The Current Financial Report Designer ships with pre-defined Row Reporting Groups. These Reporting Groups are defined as per your ERP or Accounting software Financial Categories. The default or standard Report Designer layouts within the Report require these pre-defined Mappings to be setup, as they are specific to the General Ledger. The below screen illustrates an example of some pre-defined Row Reporting Groups.
Location:
- Run Report Designer Report
- Select ‘Mapping Tool’ from BI Tools ribbon tab after running out a Report Designer Report
Custom Report Designer Mapping
The Report Designer’s mapping tool has been enhanced to include the ability for users to natively add and delete Row Reporting Groups from within the Mapping Tool’s interface. The Mappings and Groupings performed here are saved within a grouping field in Sheet 1 of your Excel Workbook called Primary Grouping. *** At time of publication the Custom Report Designer Mapping is not available to Sage 50 U.S. Edition Business Intelligence ***
The ability to Add and Delete Row Reporting Groups give users more control of the Row Reporting Groups that the Report Designer uses to group GL accounts on a layout, users can now take full control of their reporting layouts by adding their own new Reporting Groups and deleting existing Reporting Groups.
‘Group from’ and ‘Group into’ Fields Now Visible
The Mapping Tool now shows very clearly that it has been configured to use predetermined fields from within your Report Designer report to perform mappings. The objective of showing the Group from and Group into fields is to ensure users understand that the ‘Available Categories’ field list can be sourced from different fields within your report, like Account Number for example (allowing you to map account numbers to Row Reporting Groups instead of mapping standard grouping categories to Row Reporting Groups).
Improved Performance
The Mapping Tool now maps categories to Row Reporting Groups faster than ever, as we opened up the ability for users to add their own Row Reporting Groups we also made sure the Mapping Tool’s internal logic was reviewed to handle larger amounts of Row Reporting Groups even quicker.
Customizing Reporting Groups
Preparing the Mapping
This example uses the Sage 100 ERP Intelligence Financial Report Designer, however the same principles apply when adding your own Mapping & Reporting Groups to other Sage Intelligence applications that utilize the Sage Intelligence 7.2 Core software.
The standard Financial Report Designer uses AccountGroupDesc as the Primary Grouping in Sage 100 ERP Intelligence. To check what Primary Grouping is being utilized on the standard Financial Report Designer:
- Select the Financial Report Designer Report in the Report Manager
- Check the Show Advanced Options
- Select the ellipsis on the Run Add-ins option and Select the Add-In function ExcelGenieAlchemexAddin.CustomMap
4. This will open the Properties of the Add-In function which will display the field that is currently being used for the Mapping and Primary Grouping.
5. To apply the new Mapping you need to change the “A column in Sheet 1 to Group from”. Before you change the field ensure that the field exists as field in the Columns Tab of the Report.
6. For Example to change the Mapping to MainAccountDesc change the field from AccountGroupDesc to MainAccountDesc
7. Select ok once you have made the changes.
8. The Add-In Function has now been changed. Apply these changes to the Report by selecting the Apply button.
Running the Report
1. Now that you have changed the Mapping.
2. Run the Report Designer with the New Mapping.
3. Add required Parameters
4. The Mapping Tool will automatically open picking up the UnMapped Categories.
5. The Categories are now UnMapped as the Mapping changed.
6. The UnMapped Categories will be the MainAccountDesc
Adding your own Row Reporting Groupings
- Now that you have New Mappings level you may want to add your own Row Reporting Groups.
- Select the Add button on the right hand side of the Mapping Tool, you will be able to Name and add your own Row Reporting Groups.
3. Add all your new Row Reporting Groups. Once you have set up all the customized Row Reporting Groups, you can now allocate/add the UnMapped Categories to the appropriate Row Reporting Group.
4. To do this, select the Row Reporting Group you want to add the UnMapped Category to. Then select the UnMapped Category and select the Add button with an arrow.
5. Select OK once you have completed the Mapping. The Report will Generate into Excel.
6. Go to the BI Tools Tab, Select Launch and Load Layout.
7. On the Load Layout Screen select one the standard Layout and make a Copy.
8. The New Layout will now open.
9. Clear all the fields from the Row area.
10. Select the Rows Tab, note the new “Groups” you set up in the Mapping Tool are now available. For Revenue Groups you will need to right click on the button and select Toggle Switch to ensure the value is correctly displayed in Excel.
11. Generate the Layout to view the Report Layout in Microsoft Excel. Your New Mapping and Row Grouping will be displayed.
12. Create & Link the Template to Save your Mapping.
