Dynamics GP can deploy some great Excel dashboard’s in GP2015 and Jared Hall created a awesome Financial Dashboard that you can download here. The problem with all of these solutions is that most Dynamics GP environments are multi-company setups making these dashboards not very functional without navigating and opening multiple Excel spreadsheets to view each company’s dashboard. So I took it upon myself to modify the one provided by Jared Hall to work in a multi-company Dynamics GP setup.
Changing the Data Connections
The first changes made where to the Dashboard tab to add a form control and add a new tab with a data connection to the Dynamics SY01500 table for the list of active GP databases and then assign that list to the form control (figure 1).
I then added stored procedures in the Dynamics database with three input parameters. Two from the original parameters with Jared Hall’s Financial Dashboard of @UserDate and @TimeUnit and additional parameter of @Database. The @Database called the original stored procedure in the company’s database provided by Microsoft and passed the @UserDate and @TimeUnit into it to produce the intended results. This solved the KPI queries within the original Financial Dashboard. There were several OLE DB Queries in the Financial Dashboard for Budget, Cash Balances, Current Financial Data and Prior Financial Data. I used the original code from the queries and created individual stored procedures in the Dynamics database with one parameter of @Database. Once the stored procedures had been created, I changed the connections in the Current Financial, Prior Financial, Budget and Cash Flows tabs to user the Database connection calling the stored procedure with the @Database parameter mapped to the form control’s output (figure 2).
Refreshing the Pivot Table Cache
That left me only one obstacle that I discovered after all of this hard work. My Key Performance Indicators section was updating when I selected a new company from the drop down list. What I discovered was while the Financial data was updating in the Current Financial, Prior Financial, Cash Flows and Budget tabs the pivot tables Caches where not updating. I solved this by adding a little more VBA to the Financial Dashboard to automatically update all pivot tables caches when data source data changed. To do this created a VBA module and added the following code:
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
I then called the sub from the Financial data sheets whenever the worksheet changed.
Fabulous! A Multi-company Financial Dashboard
What we end up with is a multi-company Financial Dashboard with no need to navigate to different spreadsheet to get a view of each company’s current financial outlook.
Hopefully this helps you start your journey down Excel Dashboards in Dynamics GP.