SSRS Optional Parameters

Question

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.

Jeroen Jeroen · Accepted Answer · 2013-09-24T21:29:27

What you can still squeeze comfortably out of SSRS:

A multi-value company parameter based on a dataset;
A cascaded (from company) multi-value department parameter based on its own dataset;
An optional multi-value employee id parameter, based on a dataset that might filter on company/department;
An optional custom employee id parameter, plain INT input;

Your datasets would be something as follows.

For @Company:

SELECT CompanyId,       -- Param value
       CompanyName      -- Param display in SSRS
FROM   vw_AllCompanies

And for @Department:

SELECT DepartmentId,    -- Param value
       DepartmentName,  -- Param display in SSRS
FROM   vw_AllDepartments
WHERE  CompanyId = @CompanyId

And for @EmployeeId:

SELECT EmployeeId,
       FullName
FROM   vw_Employees
WHERE  (DepartmentId = @DepartmentId AND CompanyId = @CompanyId)
       OR (@DepartmentId IS NULL AND CompanyId = @CompanyId) -- Optional

Then your main dataset would do:

SELECT * -- Okay, don't use "*", but select actual columns :)
FROM   vw_AllMyData
WHERE  EmployeeId IN (@EmployeeId)            -- The cascaded param
       OR EmployeeId = @SomeCustomEmployeeId  -- The custom INT input param

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.

SSRS Optional Parameters

1 Answers