Take the 2-minute tour ×

I have a dataset that loads from a stored procedure. It has the fields.

DisplayID INT,
Display VARCHAR(45),
Default BIT

I have a paramater called DisplayFields that is a multiple value parameter. For available values I have the ValueField set to the DisplayID and the Label field set to the Display field.

I would like the default selections to be every record in the dataset where Default = 1. Does anyone know how to do this?


I have a stored procedure. that I use to populate the dataset. I added a second dataset with a filter based on the Default field. I don't like this solution because I believe I am calling that stored procedure twice.

share|improve this question
So you call stored procedure first time when your available values for parameter are generated, namely filter based on "Default" field. When exactly do you call stored procedure second time? –  Pavel Nefyodov Dec 16 '12 at 14:19
Both data sets have the same stored procedure used to fill them. The second one has a filter on it. Both uses of the stored procedures are a unique use of it in my mind. I don't know if SSRS is smart enough to not do a data pull its already done. –  Matt Akers Dec 17 '12 at 15:30
add comment

1 Answer

If you always have at least one default value than you can implement such functionality as:

WITH TestData AS
    SELECT 0 AS ID, 'always default' AS Name, 1 AS IsDefaultValue
    SELECT 1 AS ID, 'test1' AS Name, 0 AS IsDefaultValue
    SELECT 2 AS ID, 'test2' AS Name, 1 AS IsDefaultValue
    SELECT 3 AS ID, 'test3' AS Name, 0 AS IsDefaultValue
    SELECT 4 AS ID, 'test4' AS Name, 1 AS IsDefaultValue
            WHEN IsDefaultValue = 0
            THEN ID
            -- return id of always default value
            ELSE 0
       END AS DefaultID        
FROM TestData

Then in SSRS side you should add available value as ID and default as DefaultID. But we can't have in set of default values some value which isn't presented in set of available values (that's why we use "always default value"), in this case default selection won't work and you should call SP twice.

share|improve this answer
add comment

Your Answer


