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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt  gt     Order by Phonenumber

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Select All as default value for Multivalue parameter

Examples related to sql-server

Examples related to visual-studio-2008

Examples related to reporting-services

Examples related to parameters