People often want to create reports in SRS with data from multiple periods. For example, you may want a report with year-to-date (YTD) numbers in one column and month-to-date (MTD) numbers in another column. Or you might want budget data in one column and actuals in a second column. Dynamics GP does not store data in a usable way out of the box. Derived tables are one method to format data for such a report.
A derived table is a query similar to creating a temporary table, but it is much simpler. A derived table uses inline views to aggregate data.
In this article, we will create a report including MTD and YTD information for profit and loss accounts.
Here is a sample of the report we will create:
Notice the report has four columns. The table below will tell you the source of the tables:
If you’re not familiar with the Accounts and AccountSummary objects, they’re SQL Views that ship with Dynamics GP. Using these views eliminates a lot of expressions you’d have to manually create. For example, you will not have to write expressions to link the account segment columns in GL00101 or join GL00101 and GL00105.
As you can see, the two numeric columns are coming from the same source. Because you want different amounts in each column, something in the query creates that result. Using a derived table, we will create the SQL query to create the results as shown in the report.
In addition to the expressions needed to create the two columns, we also need to limit the data returned in the query to accounts marked profit and loss. Additionally, only accounts set up as posting accounts will be used.
The SQL objects for our select query have already been identified above. At this point we will write a query for two of the four columns.
As you can see, the Accounts SQL object provides friendly display names instead of the more cryptic physical names of objects.
Now for the fun part: Imagine if you had the MTD and YTD information in separate tables or views and you wanted to join them to your data. You would create a query similar to what is shown below:
The above syntax is what you learn in T-SQL 101 regarding how you join tables. You have no doubt used this syntax very often.
When creating a derived table, you use syntax that is very similar. The difference is that tables 2 and 3 are select expressions instead of tables.
Here is an example of the code after adding the MTD Balance:
Notice the expression replaces the table in the simple syntax example:
You should see there are two report parameters in the expression. The query matches the period ID from the AccountSummary view to the value for the @RptPeriod parameter, and the year from the AccountSummary view to the value for the @RptYear parameter.
Notice that the [Period Balance] column is renamed as MTDBalance. This name is used in the top-level select statement.
The query also includes the use of the IsNull function. Because some periods in the AccountSummary view may not have values, the query will return a NULL value. To convert the NULL values to 0, the IsNull expression is used.
The query also changes the sign of the amounts. Therefore, accounts that are normally a credit balance will be positive, while accounts that are normally debit amounts will be negative.
To add the YTD Balance, another join expression will be used similar to that used for the MTD Balance:
There are some differences between this expression and the previous one:
- “Sum([Period Balance])” is used to summarize the balances from a range of periods.
- “BETWEEN 1 and @RptPeriod” is used to create a range of periods from the first period in the year and the period you enter for the parameter.
- “GROUP BY [Account Number]” is used to group the amounts by account number so you get one row for each account number.
The completed query for the report is shown below:
Now that the query has been created, you can create the report. We’re using the Business Intelligence Development Studio (BIDS) in SQL Server 2008 R2. You could also create the same report using Report Builder 2 (SQL Server 2008) or Report Builder 3 (SQL Server 2008 R2).
Here is the report in the Design page in BIDS:
Notice a report summary row has been added with totals.
The report was published to the TWO/Financial folder on the Report Manager website. The user clicks on the report as with any other SSRS report to get the final report as shown above.
As you can see, derived tables can be a powerful and easy way to create reports containing columns with different aggregate totals. You also could add statements to the query to include budgets and other aggregates, and within the report you can create variance columns.
How would you create this report without derived tables? You could create a stored procedure to populate a temporary table. You could create multiple SQL views, each for its own purpose, and use them within a query. Derived tables can be a much simpler way to handle this type of report than either of these other two methods.
For questions about derived tables in SRS reports, please contact our Microsoft Dynamics GP support center by email at email@example.com.