Composing an "Aging" Report in SQL Reporting Services

Hello:

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?
LVL 1
apitechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

planoczCommented:
We use a lot of Left joins in our SP and yes thats how you do it. Have a Var Parameter in the SP and pass it to the RDL report services and in the reports you have a parameter that the user can choose which company they want to look at. The Period days has to be set in a company table where you have a set of standard days set for that company. or you going to have to have the user add them as parameters each time they run the report then these table are joined in a SP to product your reports.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.