Modifying Microsoft Dynamics GP SQL reports

SQL Reports provide a great way to access your Microsoft Dynamics GP data without needing to login to Dynamics GP. Dynamics GP comes with over a hundred reports that can be deployed from the System Setup menu. Reports such as the GL Trial Balance and Receivables/Payables Historical Aged Trial Balances are available. Another huge benefit of the SQL reports are their ability to be exported directly to Excel.

One problem with the GL Trial Balance Detail is that it does not contain the Journal Entry Reference field by default. This makes the report relatively useless for clients that process a lot of journal entries.

The steps below will walk you through adding the Reference field to the GL Trial Balance Detail report in SQL Reporting Services.

1. Go to you SQL Reports homepage in your browser (path should be something like http://servername/reports)
2. In the toolbar, click the Report Builder button
3. You may be prompted to download and install the Report Builder
4. Once Report Builder launches, choose the option to open a report
5. You will be prompted with the same folder that exists on the SQL Reports site
6. Double click a company folder
7. Double click the Financial folder
8. Select the Trial Balance Detail
9. You will now see the report layout for this report
10. There are Zoom controls in the bottom right corner to enlarge the view.
11. Expand the dataset folder on the left to reveal all the available fields that can be added to the report. You will see the Reference field highlighted in the screen shot below.
    
12. Normally, you could simply drag this field onto the report, but there is no room to place it without going wider than 11 inches.
13. Therefore, we will change the Originating Master Name field to be a calculated field.
14. When there is an Originating Master Name, that’s what will print. When Originating Master Name is blank, it will print the Reference.
    1. This works well since the reference only applies to General Journal Entries
    2. General Journal Entries do not have a value in the Originating Master Name
15. Right click on the [ORMSTRNM] field and choose Expression
16. Remove everything in the current expression and replace it with the following:
17. =IIf(Fields!ORMSTRNM.Value >= “0”, Fields!ORMSTRNM.Value,Fields!REFRENCE.Value)
18. When you’re finished, the window will look as follows:
    
19. Click OK
20. Now you can click the Run button in the upper left to test it out.
21. Clicking the Save button will commit your changes, so you can now run the report from the browser with your changes applied.

RSM offers support services for Microsoft Dynamics.  If you’d like more information on this problem or Microsoft Dynamics GP reporting, please contact our professionals at erp@rsmus.com or by phone at 855.437.7202

By:  Matt Wilson – Maryland Microsoft Dynamics GP partner

Matt Wilson