Building an SSRS Trial Balance for Dynamics GP
In this post, I’ll show you how to create a view in SSRS that allows Dynamics GP users to create a trial balance for any time period (or multiple periods) and get the ending balance for that period(s).
I was motivated to write this post after seeing a question on the GPUG forum about it. The question was referred to Victoria Yudin, a GP SQL guru, and she has a nice stored procedure to get a 12-month trial balance for the same year.
Also, I’ve been developing some generic design patterns for financial reporting in SSRS and as part of this project we’ve been asked to create trial balances for the last 12-month period—so the topic has been on my mind.
Why Views Instead of Stored Procedures?
While Victoria’s solution is good, I want to add to it by allowing users to return trial balances for any period, not just the last 12 months. Also, I wanted to use a view instead of a stored procedure for a couple reasons:
- From a users perspective, views work just like tables. So, for example, a user can use views in the SSRS Report Builder Report Wizard where they can’t use a stored procedure.
- Stored procedures tend to multiply and/or require lots of maintenance. If you add output fields to a stored procedure, you have to either create a new procedure or fix all your existing reports. But, if you add a few new fields to a view, your existing reports don’t change. (But this is really a topic for another blog post.)
So let’s start where Victoria started—with the balance table. I’m going to use the Account Summary view as it contains both open and historical data. This query takes the period balances that are stored in GP and totals them for each period. So, we get the ending balance for each period:
And here’s the output:
It’s not quite what we’re looking for. It’s great that each period “rolls forward” to include previous balances. But GP only stores data in the table when there was some activity in the period.
List all Account/Fiscal Period Combinations
A Dynamics GP Consultant can fix this by starting with a list of all account/fiscal period combinations that exist, instead of starting with the amount data. Here’s the query:
And here’s the sample output:
This is much better.
By the way, in financial reporting we often want to see all accounts, whether or not they had activity. Or if they had budgeted activity but not actual activity. Or actual activity but not budget. The solution is to start with a list of what we want to see, rather than start with the amount information.
Now that I have a list of possible accounts and periods, I use a left outer join to check for actual activity:
When I run the above query, I get the output I want—with a value for every period:
Next, I’m going to run the above query into a view. Note that when doing this, I remove the “order by” clause as well as the check for fiscal year, which I used for testing.
With the above view, I can create an SSRS report which groups fiscal year and periods as columns and account descriptions as rows:
Filtering Time Periods
Now, if I want to choose only a few periods, I can do so by changing the parameters. But remember, I must filter the time periods in SSRS, not in the SQL query. Why? Because to calculate balances correctly, I need to select periods 1-6 in order to get period 7.
For example, let’s say I want to select the last 12 months. I tell the query to select two year’s worth of data:
I’ve purposely broken the filter process into multiple steps. I like to segment the process because multi-part formulas are harder to debug when you return to your reports months (or years) later.
In each case, I’m combining fiscal year and period into a six-digit number. For example:
- 2015 period 10 becomes 201510
- 2015 period 6 becomes 201506.
I do this three times: (1) for the fiscal year and period from the data returned from the query, (2) for the fiscal year and period from the query result and, (3) for the starting fiscal year and period, which requires a little twist in the logic.
All these fields can be created from Dataset->Properties->Fields:
Which I define as:
Now, I’m going to establish the ending period of the report, which I get directly from my parameters:
The logic for the starting period is a little more complex. Basically, I have two situations: period 12 and all other periods. For anything but 12, the logic is simple:
- Subtract one from the year
- Add one to the period.
So, 2015 period 10 becomes 2014 11.
For period 12, I want to start at period 1 for the same year.
So, the year remains the same. The period becomes 1.
Here’s how the logic looks in SSRS:
Now that I have the three date fields, I can easily create the filter.
I state that “fiscal year and period” must be between “start fiscal year and period” and “end fiscal year and period.” I also exclude period 0, which represents the balance brought forward.
Here’s the output:
A Word of Warning
I acknowledge that using filters in SSRS degrades performance. I’m only using filters here because I’m crossing years. If I simply wanted all months for the current year, I wouldn’t need a filter because I could incorporate the record selection into the query.