how to set default numeric all value in ssrs report parameter

Question

I want to display all values of a numeric column as a default value in a numeric parameter. With non-numeric/text column, I have used select 'All' and it is selecting all values of that column. But select 'all' or select '%' or select '%[0-9]%' is giving me error in numeric column. I don't want to use "allow multiple values' option Can anyone help me.

I expect parameter to populate all numeric values by itself.

Answer 1

one way to do this for numeric parameters without multi values is to do a union all your parameter value with a -1 and call it select all.

So if your parameter (@numcolumn) values were like this

1

2

3

then you simply append a -1 using the union all

like this. (varchar(5) is just a choice to show example.. choose something that suits your needs)

   select -1 as value
,'Select All' as value_name
    union all
     select mycolumn as value
,cast(mycolumn as varchar(5)) as value_name
 from mytable

so now your @numcolumn parameter list will be like this

value   value_name
 -1     select All

 1       1

 2       2

 3       3

Then in your where clause for your dataset, you set your parameter value like this.

where ( @numcolumn = -1 or sometable.somecolumn = @numcolumn)

so if you select -1 as your parameter value, then the query will return everything.. else it will filter by the selected value from the parameter.

To make the parameter selection look tidy.. set the @numcolumn parameter value to value and parameter label to value_name

Hope that made sense!