CODEWITHSUNDEEP
sqlsql-serverreporting-servicessql-server-2016
bmsqldev
bmsqldev

Reputation: 2735

Change the default value in SSRS parameter dyanamically based on other parameter value.

I have a report with 3 cascading parameters. User-type, Region, District and Office. I have below requirement. 1. When the user-type is Manager, the user has to select the Region, District and office drop-down. 2. When the user-type is District Manager, the Region and district Drop-down need to filled with a default value [first value from data-set] and user has to select office drop-down.

So, the default values of the other parameters needs to be changed based on user-type parameter value. I have tried below in the default properties of Region parameter.

=iif(parameters!usertype.value = 'DM', first(Fields!RegionName.Value ,"RegionDataset"),nothing)

its gives following error..

fields cannot be used in report parameter expressions.

Do we have any workaround in SSRS to set dynamic default values based on another parameter values?

Upvotes: 0

Views: 2804

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

The easiest way to do this is to build a couple of datasets specifically for your default values, one for Region and 1 for District.

for example, let create a dataset called dsRegionDefaults and the dataset query might look something like this. (This is a little verbose but as I don;t know what your data looks like it's the best way to illustrate)

DECLARE @Regions TABLE (RegionID int, RegionName varchar(100))

IF @userType = 'Manager'
    BEGIN
        INSERT INTO @Regions
        SELECT RegionID, RegionName FROM myTable
    END
ELSE
    BEGIN
        INSERT INTO @Regions
        SELECT RegionID, RegionName FROM myTable
            WHERE something = somethingelse
    END
SELECT * FROM @Regions

I know it seems like a lot more work but it's pretty quick to setup and easy to maintain, you also don't have to rely on the order of records in the dataset which could be asking for trouble in the future.

Upvotes: 1

Related Questions