Summary:
This Article is showing how to to Add a customer SQL View within the Solver Data Warehouse Gateway for Dynamics GP. It will also show how to use Dynamic GP Account Category with User Defined Rollups at a Transaction Level.
Method:
The first thing that must be done is to Add the Account Category from Dynamics GP to the Gateway. To do this you must add the following SQL Statement. Note the name of the SQL View is a naming convention so it is easier to find with the Gateway. This view will be added in the Dynamics GP production company.
CREATE VIEW
[dbo].[BI360_AcctCat]
as select * from GL00102
GO
Now you must add the Account Category Dimension. I added two Customer Fields for Rollups.
Note this is different then the standard Account Category in the Solver Data Warehouse Product.
Next you plan the View from the Gateway into the Integration.
Once you have added the Account Category Dimension you and fill out the Rollups are you want to use in your Report.
Next you need to add the Account Category in the Module. First we will add this in the SQL View.
Go to the BI360_GL_Summary_Open and do an Alter View statement and add the a.ACCATNUM as AcctCat line. Do this in the BI360 GL_Summary_History view also.
Then go into the Module Schema and add the Account Category to the General Ledger Module
You now have the Account Category available for use.
Properties:
Solver Cloud, Integrations, Add Custom Dimension, Dynamics GP