Conditional Default Values in SSRS

I’d like to share with you a laziness efficiency method I use when developing reports in SQL Server Reporting Services. By using conditional default values in SSRS, I end up saving a significant amount of time during development and testing my eliminating the time required to manually enter parameters.

Conditional Default Values in SSRS

I’ve found that it’s quite common to work on reports wherein the requirements dictate the following:

• There are numerous parameters, and
• The parameters will not use default values

Both are valid business requirements, to be sure.  However, from the report developer’s perspective, the above requirements can translate into a time sink.  When developing a report, a common method is to test the report very frequently as changes are made, which could eventually add up to hundreds or perhaps thousands of clicks just to set the default parameters during the various testing steps.  Over the development life cycle of a single report, all that clicking around adds up.

Ideally, it’s best to eliminate this redundant selection of parameters for each test iteration.  Certainly one could specify default parameters during the development cycle and simply remove the defaults, but this presents the issue of a) remembering to remove the default values from the report before deployment, and b) adding the defaults back to the parameters, should the report require additional work after deployment.

However, I like to use a method for using conditional default values in SSRS that does not require predeployment changes.  By leveraging a little VBScript in your parameter defaults, you can easily eliminate the need to manually set parameter values on each test execution of the report.

The method I’ll describe uses the following methodology:

• Set up a Boolean control variable defining whether default values should be used
• Set up default values for each parameter, and make the use of those values dependent on the control variable

The control variable essentially gives permission to each of the rest of the parameters to use the specified default values.  If the control variable is not set to True, then no defaults will be used.

Example

To illustrate this method of using conditional default values in SSRS, I’m going to create a report using a modified version of the AdventureWorks database.  My test report is configured with 7 different parameters, all but one of which is required to have a value selected:

If I click really quickly (and the server is responsive), I can set all of the parameters to the values I want to use for testing in about 25 seconds.  It doesn’t sound like much until you consider that, if the report logic or layout is complex, I might test the report in the SSRS preview window 50 times or more in a day as I make incremental changes.  That’s about 20 minutes worth of clicking in a single day.  Personally, I’d rather use that 20 minutes for something more productive!

Configure the Control Parameter

To save time (and potentially a carpal tunnel procedure), we’re going to add an additional parameter to the report.  I’m going to call this parameter pUseDefaultValues, and configure it as a Boolean value:

You’ll notice that I’ve set the visibility to Internal, meaning that this new variable cannot be set externally.  You might also consider setting this to Hidden if you choose to pass the value of the control parameter to the report (such as through a URL parameter), but for demonstration purposes we’re going to leave it as Internal and configure the logic to set the value at runtime.

To set the value of this variable, I’m going to use its Default Values tab.  I’ll create a new default value for this control variable, in which I will set the value to True if a certain control condition is met.  In this case, the most logical condition is to test the user ID of the account executing the report, which I can do by interrogating the SSRS built-in field value User!UserID. For demonstration purposes, I’ll keep it simple and just check this value to see if my own account is the one executing the report.  If so, the variable pUseDefaultValues will be set to True.

As shown, you can use the value expression to test whether the user ID executing the report matches a specific string.  The resulting True or False value will subsequently be consumed by downstream parameters as a flag to either use or discard the default values we’ll provide in the next step.

Remember that you can use other criteria for setting the control parameter value – you could access other built-in fields (User!ReportName, User!ReportFolder, and User!ExecutionTime, among others), use the same logic to create a larger list of users who would see the default values, or go wild and create your own custom control variable logic using VB code.

One last point about the control parameter: Make sure to push this new variable to the top of the variables list (I wrote about the importance of parameter order here) since other parameters will be dependent upon the value of this one.

Configure the dependent variables

With the control parameter logic in place, let’s look look to the downstream parameter values, where we will conditionally set default values based on whether the former is set to True.

For each parameter we wish to conditionally set, assign a value in the Default Values tab.  We’ll use a value expression to test the control parameter value and, if it is True, we’ll assign a specified default value:

As shown, we’ll set a default value of “US” to the Country parameter if the control param is True; otherwise, a null (or Nothing in VB) is assigned.  You’ll apply the same pattern to each dependent variable, specifying the conditional value to set for each variable.  Note that this still works if you need to assign multiple values (assuming the downstream parameter in question is configured to allow multiple values) by creating an entry for each one and applying the same type of IIF() statement to each one.

Conclusion

In this post I demonstrated how to use conditional default values in SSRS to improve the efficiency of the development and testing cycle.