Composing an "Aging" Report in SQL Reporting Services

Posted on 2010-04-01
Medium Priority
Last Modified: 2012-05-09

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?
Question by:apitech
1 Comment
LVL 27

Accepted Solution

planocz earned 2000 total points
ID: 29453220
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.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question