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.
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.
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:
Sub RefreshAllPivots()
Dim PC As PivotCache
For Each PC In ActiveWorkbook.PivotCaches
PC.Refresh
Next PC
End Sub
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.
Contact KTL Solutions for this financial dashboard or to help kick starting your organization into becoming data driven organization by exploring your Business Intelligence, BI360, Power BI, Microsoft Dynamics GP and CRM needs.
BARRY CROWELL, MBA |BI/EDW Solution Architect
Barry uses his expertise to lead and teach a team of GP consultants to assure our solution scoping, architecting, and delivery meet and exceed the customer’s expectations. He helps clients analyze their current business processes and provides them with process improvements. He has performed implementations as the lead consultant and end-user project manager. Barry has over 20 years of experience working in accounting and the Microsoft Dynamics GP industry. He also possesses a bachelor’s degree in accounting and business administration from Black Hills State University and a master’s in business administration from La Salle University.
