Reporting Using User Selection Dropdowns

SSAS and SSRS
1

I have a SQL query that has a variable at the beginning to receive the selection from the user. The dropdown will have '1', '2', '3', '4', '9', and 'All'.

DECLARE @UnitNum AS CHAR(30)

In the WHERE portion of the query I have:

WHERE [SystemType] <> 'Deactivated'
	  AND [UnitNumber] = @UnitNum
	  --AND [UnitNumber] IN (1,2,3,4,9)
	  AND [AnalysisDeactivated] = 0
	  AND [SystemDeactivated] = 0

If the user selects 'All', I need to have the second UnitNumber statement (commented out).

Your help is appreciated.

2

AND (@UnitNum = 'All' OR [UnitNumber] IN (1,2,3,4,9))

3

What would happen if a new unit number is added - say adding units 5, 6, 7 and 8?

If the prompt is hard-coded, it would need to be updated to include the additional values - and redeployed. If you use this code, then the code would also have to be updated and redeployed.

It isn't really necessary to check each one though - easier to just do this:

WHERE [SystemType] <> 'Deactivated'
	  AND ([UnitNumber] = @UnitNum OR @UnitNum = 'All')
	  AND [AnalysisDeactivated] = 0
	  AND [SystemDeactivated] = 0

However, if UnitNumber is a numeric type (e.g. integer) - then I would use 0 as the value for the 'All' option and declare @UnitNum as an integer.

The above code would just need to be changed to use 0 instead of 'All'.