Skip to content


SLB – Tip of the Month

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.

1 Comment

  1. Steve Chapman on February 14, 2011 at 10:37 am

    I keep finding new uses for SLB. Our customers who have many GP databases really like the way you can combine information from multiple companies.

    Being able to then link the GP data to "live" pivot tables and graphs is fantastic. I demo this almost every time.

    Steve Chapman

Leave a Comment


Q1 2023 All Hands Call
2023 Advanced SmartConnect Bootcamp: REST Web Services Integration
Accessing Your Historical Dynamics NAV Data in D365 Business Central - Part 1: Getting Started
2023 Migration from Dynamics GP to D365 Business Central Training
eOne Welcomes Incremental Group as a New eOne iPaaS Integration Partner


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder



Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release SalesForce SmartConnect SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.