Select All as default value for Multivalue parameter

Question

I'm building a report in Visual Studio 2008 with a lot of multivalue parameters and it's working great, but I would like to have have the "(Select all)" option as the default value when the report is opened.

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

Answer 1

Try setting the parameters' "default value" to use the same query as the "available values". In effect it provides every single "available value" as a "default value" and the "Select All" option is automatically checked.

Answer 2

Using dataset with default values is one way, but you must use query for Available values and for Default Values, if values are hard coded in Available values tab, then you must define default values as expressions. Pictures should explain everything

Create Parameter (if not automaticly created)

Define values - wrong way example

Define values - correct way example

Set default values - you must define all default values reflecting available values to make "Select All" by default, if you won't define all only those defined will be selected by default.

The Result

One picture for Data type: Int

Answer 3

Does not work if you have nulls.

You can get around this by modifying your select statement to plop 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 option, the Available Values dataset must contain at least 2 columns: value and label. They can return the same data, but their names have to be different. The Default Values dataset will then use value column and then Select All will be the default value. If the dataset returns only 1 column, only the last record's value will be selected in the drop down of the parameter.

Answer 5

Adding to the answer from E_8.
This does not work if you have empty strings.

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

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

Answer 6

It 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 rather easy to achieve by making 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.