Select All as Default for Multivalue

Question

I create a report in Visual Studio 2008 with a lot of multi-valued parameters , and it works fine, but I would like to have the "(Select All)" parameter as the default value when opening the report.

Is there some kind of expression or SQL code that I can use to make this happen? Or do I need to select "(Select All)" every time in each parameter every time I want to run a report?

Answer 1

Try setting the default value to use the same query as the available values. In fact, it provides each "available value" as a "default value", and the "Select All" option is automatically selected.

Answer 2

Using a dataset with default values ​​is one way, but you should use the query for available values ​​and default values, if the values ​​are hardcoded on the Available Values ​​tab, then you must define the default values ​​as expressions. Pictures should explain everything

Create parameter (if not created automatically)

Define Values ​​- An Example of the Wrong Way

Defining Values ​​- An Example of the Right Way

Set default values ​​- you must define all default values ​​reflecting the available values ​​in order to make "Select All" the default, if you do not define all, only those that are defined will be selected by default.

Result

Single image for data type: Int

Answer 3

Doesn't work if you have zeros.

You can get around this by modifying your select statement to spit something into nulls:

phonenumber = CASE WHEN (isnull(phonenumber, '')='') THEN '(blank)' ELSE phonenumber END 

Answer 4

The accepted answer is correct but not complete . In order for Select All to be the default parameter, the data set of available values ​​must contain at least 2 columns: value and label. They can return the same data, but their names must be different. Then, the default value dataset will use the value column, and then the Select All value will be used by default. If the data set returns only 1 column, only the last record value will be selected from the parameter drop-down list.

Answer 5

Addendum to the answer from E_8.
This does not work if you have blank lines.

You can work around this by changing the select statement in SQL or by changing your query in the SSRS dataset.

  Select distinct phonenumber from YourTable where phonenumber <> '' Order by Phonenumber 

Answer 6

He works better

 CREATE TABLE [dbo].[T_Status]( [Status] [nvarchar](20) NULL ) ON [PRIMARY] GO INSERT [dbo].[T_Status] ([Status]) VALUES (N'Active') GO INSERT [dbo].[T_Status] ([Status]) VALUES (N'notActive') GO INSERT [dbo].[T_Status] ([Status]) VALUES (N'Active') GO DECLARE @GetStatus nvarchar(20) = null --DECLARE @GetStatus nvarchar(20) = 'Active' SELECT [Status] FROM [T_Status] WHERE [Status] = CASE WHEN (isnull(@GetStatus, '')='') THEN [Status] ELSE @GetStatus END 

Answer 7

This is fairly easy to do by creating a dataset with a text query like this:

 SELECT 'Item1' UNION SELECT 'Item2' UNION SELECT 'Item3' UNION SELECT 'Item4' UNION SELECT 'ItemN' 

The query should return all items that can be selected.