Our Top 3 Tips for Sage 100 ERP Business Intelligence

Admin June 15, 2012Sage 100

On June 13th, we had a class that highlighted 15 Tips for Sage 100 ERP Business Intelligence (formerly known as SMI). We pulled out the 3 that we think are the most important for you to know. If you would like to see all 15, go to https://martinandassoc.com/sites/default/files/15%20Tips%20for%20Sage%20100%20ERP%20Business%20Intelligence2.pdf

Martin & Associates will also be holding classes for Business Intelligence Financials Workshop & Financial Report Design July 10th & 11th. Check our Training Schedule on our website for more details.

1. Cell References for your Distribution Lists (Tip #6)
To help you better set up your list of who is distributed the report, you can create lists in an Excel spreadsheet with people’s emails. Instead of typing each one into the instruction boxes, you can reference the cells in the spreadsheet. Right click inside the address field box, then select “Insert” and follow the Excel prompt.

2. Hidden Excel sheets and how to unlock them (Tip #12)
There are sheets in Excel that show you all the data that was used to compile your report, but these sheets are automatically hidden. Sometimes you may need to troubleshoot a specific set of data or just want to see all the data, good thing we know how to find them!
Go to your report in Excel. Right click on the other sheet names at the bottom of the screen, select “Unhide.” Two sheets should come up next to the other sheets in your report.

• Sheet 1 – This sheet contains all the information that was pulled for the accounts and the number of activities
• Sheet 2 – This sheet contains information about your report, such as report ID, report location, report codes, etc.
• Your report – The used macros to pull information from the other two sheets, and you can collapse/uncollapse the categories on the left side of your report to see these macros and specific account numbers.

This helps you track certain accounts, what the revenue is, what budget numbers you used, etc. Pretty useful when you need to find a specific set of information and don’t want to sort through everything outside of the report.

3. Add a calculation (Tip #13)
In Excel, you can open the Report Designer (which is not included in the Business Intelligence package and can usually be purchased for around $1,100, but well worth it) to add in a calculation. On your Excel top toolbar, go to BI Tools -> Quick Edit -> My Report and the Report designer window will open.
This shows you how your report was designed/set up. You can move information between rows and columns, if you so wish. On the far right box, you will see other little boxes with names of calculations used in your report. If you would like to add another one, right click in that area and click add your own formula.