Composing an "Aging" Report in SQL Reporting Services
Posted on 2010-04-01
I need to compose a report in SQL Reporting Services that resembles a receivables aged trial balance in Microsoft Dynamics NAV. I am stumped on a couple of important items, however.
First, have any of you ever created a SQL report where the query that you based the report on needs to be used for multiple company databases? I know how to create such a report and specifically how to create a parameter for "company", so that users can choose which company to print for when they run the report. Truthfully, though, I have only done that for Microsoft Dynamics GP and not NAV.
Anyway, someone told me that you need to place a variable in the query that pulls the company name from whatever table in SQL that holds the NAV company names. But, even if that's what you need to do in order to create this parameter, how do you link this table to the tables that you have in your query? My report is going to contain A/R data from multiple databases. So, how can a table containing company names get linked to multiple A/R tables in SQL for NAV?
For GP SSRS reports, I have always simply either created a view or a stored proc that does a “UNION ALL” to join queries for multiple companies. But, again, someone told me that this can be simplified with a variable representing any company.
The other "thing" regards the aging period that the end user needs to be able to run the report for. If the user runs the aging report for a period of 30 days, then the columns of the report need to be "Current", "31-60", "61- 90", and "Over 90". But, if the user runs the aging report for a period of 60 days, then the columns of the report need to be "Current", "91-180", "181-270", and "Over 270". SQL reporting parameters, usually, need a dataset. But, how do you configure such a dataset when this "set" would not pull from data tables but rather from whatever the user wants to run the report for? And, for that matter, how would you change the columns to conform to the revised time periods?