SQL Script to Report against multiple GP Companies

By Tracey BrinkmanCompany, GP Reporting, Microsoft Dynamics GP, With 0 comments

By Ryan McBee
One of the most common needs when setting up multiple Great Plains Companies is to report against each company in a single report.  If you are looking for Financial reporting, it is fairly easy to setup a tree within Management Reporter or FRx which will meet your needs.  However, data that is in the sub-modules can be a bit more of a trick to run against each GP company in a single report.

To accommodate this requirement we have the example below which is a basic query that reports against each GP Company and displays every active checkbook, current balance and GP Company.  The first step is setting up a custom table in the Dynamics database to house our data.

USE [DYNAMICS]

GO

 

/****** Object:  Table [dbo].[Checkbooks]    Script Date: 06/08/2011 21:58:30 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[Checkbooks](

[CHEKBKID] [char](15) NOT NULL,

[CURRBLNC] [numeric](19, 5) NULL,

[COMPANY] [varchar](65) NOT NULL

) ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO
After we have our custom table to store our data, we can run the SQL Script against the Dynamics database.

DECLARE @companydb VARCHAR(5)

DECLARE @sql nvarchar(4000)

SELECT @sql = ‘ ‘

 

delete from DYNAMICS.dbo.checkbooks

 

DECLARE companyCur CURSOR FOR

SELECT interid FROM Dynamics.dbo.SY01500

–where interid <>’TWO’

OPEN companyCur

FETCH NEXT FROM companyCur INTO @companydb

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @sql = ‘Insert INTO Dynamics.dbo.Checkbooks

SELECT  a.CHEKBKID, a.CURRBLNC,

CompanyName = (SELECT CMPNYNAM FROM Dynamics.dbo.SY01500 WHERE CMPANYID = (select CMPANYID from ‘+@companydb+’.dbo.SY00600 GROUP by CMPANYID))

FROM        ‘ + @companydb+ ‘.dbo.CM00100 a where inactive = 0′

 

 

EXEC (@sql)

 

FETCH NEXT FROM companyCur INTO @companydb

END

PRINT @sql

 

CLOSE companyCur

DEALLOCATE companyCur

 

select * from Dynamics.dbo.checkbooks
In the example above, I am including data from Fabrikam, but to exclude it, you will just need to add back in the proper syntax (where interid <>’TWO’).

For more information on this GP SQL Script or for similar SQL Scripts, I can be contacted at rmcbee@isolutionsgroup.com or 317-602-1579.