Sage MAS Intelligence Tips And Tricks

By: | Category:

Sage MAS Intelligence (SMI) is a powerful new tool in Sage ERP MAS that was primarily adopted to replace Microsoft FRx, which is being retired by Microsoft. Naturally there is a learning curve associated with getting the most out of any new tool. Here we provide some tips on getting started with creating custom financial reports in the SMI Report Designer.

Excel Friendly

The SMI Report Designer sensibly starts with MicrosoftÆ ExcelÆ, a tool with which most accountants are very familiar, and adds functionality. The Report Designer requires Microsoft Excel 2007 or above, and gives you flexible report design capabilities from raw data in Excel.

Report Templates

Currently, the Report Designer functionality is available for two reports, one for single-company financial reports and one for consolidated financial reports for multiple companies. You can start creating your custom report by copying these reports to use as templates and then modifying them. After you design a new report format, remember to create and link the template back to the copy of the report you made.

Getting Started

To access the Report Designer, run the desired report. When Excel opens, access the Report Designer using the BI Tools tab. Once Intelligence is installed, the Add-Ins and BI Tools tabs will always show in your Excel workbooks.

Mapping Accounts

You may need to map the Report Designer reporting groups to your system accounts. This is done within the individual report. We recommend that you copy the Financial Reports Designer report and rename it to Mapped Designer Template. Map the accounts as follows:

  1. Select the report.
  2. On the Parameters tab, set the Fiscal Year default to 2010 and the Budget Code default to Original. To do this, right click on the field and select Properties. After selecting the default, click OK.
  3. Select the Mapped ABX Designer Template and run the report. Select OK.
  4. Map the UnMapped System Categories into Reporting Groups. First select a reporting group in the right most column and then select the unmapped category in the left most column. Select Add. Select OK when all categories have been mapped.
  5. Click OK to the message about successfully mapping your categories.
  6. Minimize Excel and right-click on the Mapped ABX Designer, select to create and link the template.
  7. Click OK to select the Mapped ABX Designer Template Workbook.
  8. Click Yes to clear the contents of worksheet 2.
  9. Click OK to accept the template name. By default the template name is the same as the report name. It is recommended that you keep the template name and the report name the same.
  10. Select Yes to replace the existing template.
  11. Select OK to accept that the template was created successfully.

Working With Report Designer

Here is an example of creating a rolling three month report for January (assuming a calendar fiscal year) using the Mapped Designer Template. We will remove the budget columns and add in the appropriate period columns.

  1. Copy the Mapped ABX Designer template and paste it into the Financials folder. Right click on the copied report to rename it to Three Month Report. Click OK to assign the new template name.
  2. Select the report and click the Parameters tab. Select the Budget parameter and click Remove. Click Yes.
  3. Run the report using the default parameters.
  4. From the BI Tools tab, click Launch.
  5. Click Load Layout. All of the available layouts for the report are displayed. Click Copy for the row IS – Actual vs Prior.
  6. Enter January Report ñ 3 month as the new layout name and click Next.
  7. In the report designer, note the columns area in the top right section. Click Clear All to remove the existing column definitions, then select the columns you need from the green Columns Tab in the center of your screen. Select columns for ActivityPeriod01, PYActivityPeriod12, and PYActivityPeriod11 in that order. Click Add Spacer to make spaces between the columns.
  8. Click Generate Layout and click OK when the layout has generated successfully.
  9. Change the headings from Periods 01, 12, and 11 to January, December, November.
  10. Minimize Excel and create and link the template to the Three-Month Report report.

Give us a call with any questions or for assistance with the SMI Report Designer.