There are several powerful reporting tools available for Microsoft Dynamics GP. Microsoft Excel Report Builder is one that’s particularly useful.
Associated with the SmartList Builder application, it enables the user to create custom Report Definitions in the form of List and Pivot Tables that may be published as an Excel Report for users to access current Microsoft Dynamics GP data from your system.
Once the report is published in Excel, the users who have access to the report can refresh the data to update the report with current information.
In Part I of this two-part series, we took a look at how to access Microsoft Excel Report to create, modify, and publish custom reports.
In Part II, we’ll discuss the more advanced options in the Microsoft Excel Report Builder to help make your reports more powerful than ever before.
To access Microsoft Excel Report Builder for creating custom reports:
- Select Microsoft Dynamics GP – Tools – SmartList Builder – Excel Report Builder – Excel Report Builder
Restrictions:
Restrictions allow the user to filter out data from the tables to place onto the report.
To add Restrictions on what data is pulled from your tables:
- Select the Restrictions button from the Toolbar
- Select the + Next to Restrictions
- Select the Table from the report
- Select the Field from the table
- Select the Restriction to use from the dropdown list
- Enter the value for the Restriction under the Value field
To Remove a Restriction:
- Select the Restrictions button from the Toolbar
- Select the – Next to Restrictions
To Modify a Restriction:
- Select the Restrictions button from the Toolbar
- Select the Notepad Next to the Restrictions
Calculations:
Calculations are used to add calculations to your report.
To add Calculations:
- Select the Calculations button on the Toolbar
- Select Add
- Enter Calculation Name
- Enter Field Type for format of result
- Enter your calculation in the Calculation box
- To add fields to the calculation:
- Expand the Table on the right by clicking the + next to the table
- Select the field
- Select the Validate button to test the logic of the Calculation
- Select the Save button to Save the Calculation
To Remove a Calculation
- Select the Calculations button on the Toolbar
- Select the Calculation
- Select Remove
To Modify a Calculation
- Select the Calculations button on the Toolbar
- Select the Calculation
- Select Edit
- Modify the Calculation
- Select the Validate button to test the logic of the Calculation
- Select the Save button to Save the Calculation
Drill Down:
Use the Drill Down function to enable Drill Down capabilities within your published report to view GP data about the selected field.
To add a Drill Down:
- Select the Drill Downs button on the Toolbar
- Select Add
- Enter column for Drill Down
- Enter The Transaction type to Drill Down into
- Map the required parameters to the Drill Down key Parameter
- Select the field to map
- Select the Edit button
- Select the Source table to map to the key
- Select the Source field to map to the key
- Select the Save button to Save the Drill Down
To Remove a Drill Down
- Select the Drill Downs button on the Toolbar
- Select the Drill Down
- Select Remove
To Modify a Drill Down
- Select the Drill Downs button on the Toolbar
- Select the Drill Down
- Select Edit
- Modify the Drill Down
- Select the Save button to Save the Drill Down
Options:
Options are used for various modifications for your report. To access the options, select the Options button from the Toolbar.
- Summary – To add totals to data items for your report
- Duplicate – To create Copy of Report with a new name
- Display SQL – To Display SQL script pulling the report data. This enable to the user to view the script and show Fields, Calculated Fields, and Restrictions in the Script.
- Preview Data – To View contents of the selected table
- Edit Field Settings – Allows the formatting of the data types for particular fields
