SQL Script to Report against multiple GP Companies
June 09 |
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.
