Posted by: Karlo Bartels | 2010/07/21

Passing NULL in a Report Parameter

While creating a dashboard report in SSRS, I ran into the issue of passing a NULL value for a specific report parameter. This nullable parameter operates as follows: either filter the report dataset on the supplied values or do not filter at all. Much like the Error List window in Visual Studio, which features Errors, Warnings and/or Informational messages. Clicking a category removes the filter for that category or adds it. No selection means: “Show me all messages”.

The same goes for the report parameter I added, in this case a Boolean field which allows a filter for the underlying dataset. The report user either selects True, False or <Do not filter>, which comes down to a NULL value.

The dashboard report itself features a collection of textboxes with the names of the target reports. Their Action properties are set to Go to URL. Instead of a hard-coded URL, I used the ReportServerUrl and ReportFolder properties of the Globals object to construct the URL:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard"

This flexible approach allows you to publish the dashboard and its underlying reports to other servers without having to update the URLs in the textboxes.

If the target report contains parameters you will have to add those to the URL directly. You can use the report’s Parameters collection to pass the value of a report parameter (e.g. Period) to the underlying report as follows:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard&Period=" & Parameters!Period.Value

But what if the report parameter is NULLable? Simply leaving out the value will not do the trick since the report engine will interpret the empty parameter as an empty string. Omitting the parameter altogether is the answer. Continuing with the example above, this is what it would look like for a NULLable parameter called User:

=Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/MyDashboard&Period=" & Parameters!Period.Value & IIF(IsNothing(Parameters!User.Value), "", "&User=" & Parameters!User.Value)

Advertisements

Responses

  1. Really informative blog post here my friend. I just wanted to comment & say keep up the quality work. I’ve bookmarked your blog just now and I’ll be back to read more in the future my friend! Also well-chosen colors on the theme it goes well with the blog in my modest opinion 🙂


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: