Optional Parameters In SQL Server Reporting Services

Optional parameters In SSRS means if parameters have been selected, it would be passed back to SQL query as condition in WHERE clause, and when no parameter(s) have been selected, the WHERE condition should be not in use. Dynamic SQL is not the best solution. Following solution is much better:

SELECT Col_A
, Col_B
FROM Tab
WHERE (Col_A=@para_A OR @para_A IS NULL)
AND (Col_B=@para_B OR @para_B IS NULL)

What make life harder is when parameters are selected, but due to lack of records, the result is nil? These would be happened often when multiple parameters get involved. Depends on situations, you may wish to abandon that condition completely. So, the real issue is not if you have option to select a particular parameter, it is if you have selected that parameter but it turns out nil result and you need to abandon it after this discovery.

There is no programmatic solution so far. However, following approach may help:

1, Limit the number of parameters, because you will need to combine them in matrix. Too many parameters will increase the task load exponentially.

2, Classify parameters into hierarchy.

3, Write queries for each combination case of the parameters. Hierarchical structure of parameters will help a lot.

4, From low position in hierarchy to pick up the best available result amongst the results generated from above queries. Following is an example to do this procedure:

; SET @Stopper = 0
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_1' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
; IF 0 = @Stopper
BEGIN
SELECT @result = SUM(CASE WHEN Hierarchy = 'Level_2' AND Result_From_Set IS NOT NULL THEN Result_From_Set ELSE 0 END)
FROM Tab
IF @result <> 0 SET @Stopper = 1
END
...

What you need to do next is in SSRS to enable the optional parameters. While you have made it possible to accept the NULL value in your back-end queries, by default the report interface of SSRS cannot be flagged out the parameters. The users would have no choice but to select parameters. In this case, include NULL value record for your parameter data set:

SELECT NULL AS Id
, 'Unknown' AS Name
UNION
SELECT DISTINCT Id
, Name
FROM Tab

If you have problem on how to position this NULL value record, please refer to following article:

Special Function of ORDER BY Clause in T-SQL

Next, in Report Parameters window of SSRS, make sure the "Allow null value" option has been selected for this particular parameter.

Done.


http://bloggingabout.net/blogs/egiardina/archive/2007/06/26/sql-server-reporting-services-optional-parameters.aspx