SSRS – Applying Parameter Default Value Using Custom Code

On a recent client engagement, I had to put in some default values for my parameters. No big deal. Normally, I just use the DEFAULT property of the Report Parameter, but this time was a little different. I needed to default the parameter to a different item depending on another selection of a report parameter. I decided the easiest way to do this was to add in some VB Script to handle the selection, so I’ll show you here. I’m using SSAS for my datasets, so this solution would be different if you are using SQL stored procedures or a table. Note that I’m using MDX for my dataset queries; this solution would be different using SQL stored procedures or a direct SQL query.

My first report parameter is called “PropertyOrAsset” and contains two hard-coded values: “Property” and “Asset”. The default is set to “Property”.

My Second report parameter is called “Currency” and it needs to have two different sets of values. When the “PropertyOrAsset” parameter is set to “Property”, it should contain three values, “USD”, “GBP”, or “EURO”, and its default should be set to “EURO”. But when, “PropertyOrAsset” is set to “Asset”, it should only contain a single value, “USD”.

So, the way I’m going to implement this is to change the default value for the “Currency” parameter based on the value of the “PropertyOrAsset” value. The “Currency” parameter is always going to have the three values, “USD”, “GBP”, and “EURO”, but when “PropertyOrAsset” is set to “Property”, the default for “Currency” will be set to “EURO,” and when “PropertyOrAsset” is set to “Asset,” the default for “Currency” will be set to “USD.”

In the Report Page Properties Code section, we can add the following code to implement our logic:

function SetCurrencyDefault(blnShowPropertyAsset as boolean) as String
‘ the value 1 is USD, used for “Asset Level”
‘ the value 8 is EURO, used for “Property Level”
if (blnShowPropertyAsset) then
SetCurrencyDefault = “1″
else
SetCurrencyDefault = “8″
end if
end function

My MDX Query for Currency has the values 1 and 8 as the keycolumns of USD and EURO.

[Currency].[Currency].&[1] is the USD
[Currency].[Currency].&[8] is the EURO

Now, in the Currency report parameter Default properties, add in the expression:

=”[Currency].[Currency].&[” + Code.SetCurrencyDefault(Parameters!PropertyOrAsset.Value) + “]“

And, that should do it. As you change the values in the PropertyOrAsset parameter, the value will trigger the vbscript and build the mdx string appropriately based on the key value.