Select All parameter doesn't work in SSRS

ssrs multi value parameter determine if all selected
ssrs parameter default select all
ssrs select all not working
ssrs set default value for multi value parameter
how to set default value in ssrs report parameter
ssrs cascading parameters default select all
ssrs select all parameter url
report builder select all parameter

I have a report which creates a list of Events for a specified date range and event type.

The date range and event type are parameters defined in the report. The date parameters (@DateFrom and @DateTo) work as they should.

The @EventType parameter however, which is defined as a list of values provided by a DataSet (with 'Allow Multiple values' checked), does not provide the expected behaviour when using the {Select All} check box. If I select one or more Event Types by checking several boxes on the list, the report will show the Events which match the specified Event Types correctly.

However, if I click the {Select All} box (which then highlights all of the other possible values), the report does not show the Events for all of these Event Type values. It seems to miss out several of the values which are selected by the {Select All} box. If I run the report specifically for those missing values, the report returns events matching those types. This indicates to me that there is not a lack of data for these types.

And for that reason, it looks to me like the {Select All} is bugged...or perhaps cached somewhere? I've tried deleting the report/parameter dataset and redeploying to no avail. It's worth noting that this behaviour happens locally before deploying it, too.

Has anyone seen this before, or does anyone have any suggestions?

EDIT - I should also mention that the parameter in question (@EventType) has no default value assigned.

How are you declaring your predicate for the variable? It should be be like:

where thing in (@Variable)

Where @Variable is a multi value parameter.

You could attempt to see if the values of the multi valued parameters are junked up somewhere as well by defining them. Generally the collection method of multi valued parameters can cause issues if there data types are different.

Also you may try to design your data set at runtime to build instead of being a static query. EG: Set up an expression for your dataset like:

="Select * from table where thing in (" & Parameters!Variable.Value & ")"

This would cause the parameter to build as part of a string and then evaluate at run time instead of from a traditional query.

Set Select All as Default for Multi-Value Report Parameters in SQL , Setting "Select All" as the default parameter value is really helpful when users want to This dataset doesn't return any NULL value rows. SQL Query to Select All If Parameter is Empty or NULL. In this example we are using IIF Function along with ISNULL. First, ISNULL function will check whether the parameter value is NULL or not. If True, it will replace the value with Empty string or Blank. Next, IIF will check whether the parameter is Blank or not.

Can't quite believe that this was the case, but the parameter which was passed to the SQL Server procedure was too small. It was defined as a VARCHAR(500) and needed to be bigger to deal with a large list of comma separated values. I changed it to VARCHAR(4000) and it's now functioning as expected.

Thanks to Djangojazz for pointing me to the direction of the parameter.

Using multi-value parameters in SSRS, How do you default select all in parameter of SSRS? Setting "Select All" as the default parameter value is really helpful when users want to preview the report for all parameter values. Users can preview the report without manually selecting the "Select All" parameter value for each parameter.

If the dataset underneath your parameter that multi-select checked and the data source is set to a SQL query contains a blank or a NULL, the SSRS parameter drop-down will get confused and not auto-select "ALL", NULL seems to confuse it.

Killing the null/blank in my dataset solved it for me (column should not had had a NULL allowed in the first place)

How to Default to ALL in an SSRS Multi-select Parameter – The , How to Default to ALL in an SSRS Multi-select Parameter The first thing to do is add the value 'ALL' to your parameter query. hang up since there is a parameter in the dataset but it doesn't, it selects everything in the list. When I select "allow multiple values" properties, parameter dropdown show me "select all" but when I choose "select all" report result doesn't correctly. And I created for my report all selection. when I select "all" my report result show correctly. Do you know Could I remove "select all" when I use "allow multiple values"?

Workaround: When a user clicks "Select All", the report should pass , Unfortunately, you cannot pass a blank value/null value by default. After selecting the Parameter, under default values, select "From Query" and select  In the Parameter Properties of the Parameter you want set to (Select All) go to the Default Values tab. Select “Get Values from a query” Set Dataset to the same dataset your values will be populated from. Set Value Field to the same value field your values will be populated from. It will now select all by default.

parameters list: Allow multiple values not working – SQLServerCentral, This article mentions usage of the multi-value parameter in the SSRS Also, it offers a “Select All” option that helps to select all parameter values Your browser does not currently recognize any of the video formats available  So the active will be a dropdown of Select All, Active,Inactive. Active and Inactive works but not select all. I would like to have an option of select all where it displays all active and inactive. You could try this. 1.) Setting the "Allow Null Value" checkbox to TRUE. 2.) Setting NULL as the default value for the parameter.

SSRS Tutorial 40, To activate the 'Select All' option I went to the parameter's General tab, of FilterExpression for the dataset 'PPCQuery' cannot be performed. Multi-select parameters give your users control over their reports while reducing the number of reports they have to work with. In this example, I will demonstrate how to create a multi-select parameter list and pass the values to a stored procedure that will then populate the report.

Comments
  • Thank you for your help, I've posted the answer below.
  • Add more description and formatting would make it looks awesome
  • Updated My Solution