How do I combine data types in a SSRS Parameter?

Question

Background -- I’m trying to create a report in SSRS using Report Builder 3.0 that contains multiple parameters. The request is when the report is opened, display all rows of data. The end user can then select one or more of the parameters to reduce the results displayed. I can get it to work using something like the following.

Data sets for Parameters where available values are from a query,(dataset type is text)

For fields that are numbers:

SELECT 0 'FACG'  UNION ALL  SELECT DISTINCT STR(ACG) FROM  STG_CSI_SA_CONFIRM

For columns containing text:

SELECT 'Any' 'FunderName'  Union All  Select DISTINCT NAME FROM  STG_CSI_SA_CONFIRM

In the dataset for the report I have this:

WHERE  ('Any' IN (@FrName) or Fname.name in (@FrName)) AND ( 'Any' IN (@FACG) OR FLIST.FACG IN (@FACG))

The results are two Parameter boxes, one says 0, one says Any, and the report runs when first opened. End user can then select one or more numbers from FACG or one or more names from FRName.

My question is, how can I adjust the first parameter to show ‘Any’ instead of a zero as the default value? All the values returned would be integers of 7 digits long. When I simply change the 0 to Any I get an error message about converting data types. If I use STR() I can get a list out of SQL server I get a list as I expect. ( Any, 1234567, 1234568 etc.) But I cant get the list to work in SSRS.

Thanks

R

Alan Schofield Alan Schofield · Accepted Answer · 2017-06-30T13:43:17

Two way to do this. 1. Make sure the dataset that provides values for your parameters has two columns, an ID column that you will use to actually do the filtering/joins in your dataset and the other column is the label which will be visible to the user. Something like

ID         Label
'Any'      0
'1234567'  1234567
'1234568'  1234568

If you main data dataset is a query (not a Stored proc) then you can simply have a parameter dataset as above but without the 'Any' row. Then set you available value to the parameter dataset and the default values to the same parameter dataset. By default all parameters will then be selected.

In your main data dataset the query would be along the lines of

SELECT * FROM myTable WHERE myColumn IN (@myParameterName)

SSRS will turn the selected values from the parameter into a list of comma separated values and inject them into the query for you.

How do I combine data types in a SSRS Parameter?

1 Answers