I have run into a couple situations where I have a single report, but the user requires two ways to run it. For example, they want to either enter an employee id and pull up a single employee record, or they want to enter the company and department, or multiple companies and departments, and return employee records for all selected departments & comapnies.
I know how to do the cascading parameter thing, so I can do either way, but I dont want to have 2 reports, I would like to have one report with optional parameters. I envision two tabs or check boxes or soemthing when they first open the report, that say, "Click to view single record" and "Click to view multiple records" then which ever one they choose, they can enter the parameter(s) and run.
I have been researching and I am leaning towards sub reports and/or using ISNULL in the parameters and marking them as 'allow null'. STill playing with it, but if someone has a link to a nifty tutorial, I would be much obliged. Thanks.
Best Answer
What you can still squeeze comfortably out of SSRS:
Your datasets would be something as follows.
For
@Company
:And for
@Department
:And for
@EmployeeId
:Then your main dataset would do:
In my experience, this is slightly clunky, but probably the best you can get out of basic SSRS. If you want more flexibility I recommend you built something in your app around it, and pass the ID as a final parameter to the report.