Today’s tip is to remind everyone that Excel Report Builder (ERB) from Microsoft is awesome. I mean really really cool.

For the uninitiated, ERB is one of the tools wrapped up inside Smartlist Builder. If you own SmartList Builder you own Excel Report Builder. ERB lets you build data connections for Excel into GP data and publish the data connection along with the creation of an Excel Report. The result is an Excel Report that is updated with live data every time you open it.

Today I was working on some internal reporting requirements and as always there are a massive number of tools I could turn to. (SmartView, SRS, Crystal, SmartLists, Cubes etc). My Requirements were pretty simple and included:
1. To see sales Volume by month
2. To see Sales Volume by Quarter
3. To be able to share this report between the USA and Australian Office.
4. Have a single report so Abbey and I are always looking at the same numbers.
5. I had to be able to build it – everyone else was busy. (For those that do not know me I could be described as a mildly technical GP user, who is really not too clever).

I won’t run you through the detail steps in creating a report but I will highlight a couple of the tricks:

1. I had to first select the tables I wanted to use in the report, which was focused around SOP. The first time I ran the report I fell for the trap of not seeing both the SOP History and SOP Work tables together. (I needed work tables for my current month sales/forecast version of the report). So I used the MATCH TABLE function in ERB to join the two sets of tables together. If you have never used Match tables it is perfect for this scenario and let me join two tables at the click of a button.
2. I placed restrictions on the report right there in ERB to limit the data. For example I only wanted invoices, and had to exclude all quote documents. I also restricted out all $0 lines to keep things clean as I only needed things we sold for real $’s. It was better to keep this data out of the report altogether rather than trying to filter it in Excel later.

3. I then published this report to a shared network location that is accessible to both the US and Australian offices.
4. I needed more than just a list of data, so built a pivot table on the spreadsheet based upon this data and added some color etc.
5. Then I remembered that there was some data missing. Now here was a problem – I did not want to lose all my formatting of the spreadsheet but I need to publish the excel report again from ERB. So my biggest tip is – when publishing there are two options: Firstly to create the data connection and secondly to create a spreadsheet. Given I already had a spreadsheet and just needed a few columns added to the data connection – I simply chose to rebuild the data connection.

The result is I have a ‘Live’ pivot table in excel that is build of roughly 9,000 lines of data. I can share it with others in the office, it is rocket quick and completely flexible as all pivot tables are. ERB solved all my problems and literally from start to finish took about 25 minutes to build, format and publish. Go ahead and find me an ERP system that is better than Microsoft Dynamics GP.