Reporting Services condition based drill through reports

By: Rob Fisch   |   Updated: 2007-07-31   |   Comments (4)   |   Related: > Reporting Services Development

Problem

One great feature of Reporting Services is the ability to drill through to secondary reports.  This is pretty straightforward to do, but the built-in option in Reporting Services is an all or nothing approach.  Sometimes there is a need to drill through to secondary reports based on certain data conditions, but you do not want a link for every row only certain ones. How can you provide a link for some data records but not others?

Solution

The solution is to use if IIF() function within the report navigation expression window.

Let's say you have a report showing a summary of sales in several divisions, but you only want to drill through to details (a secondary report) with records having to do with the Eastern division. All other divisions will report at the summary level without a drill though report.

To create the drill through navigation, right-click on the Properties of the textbox and select the Navigation tab.

Instead of choosing a report from the dropdown, click on the fx button to open the navigation window and use the Reporting Services expression editor.

To conditionally present a link, use the IIF() function.

The IIF() statement has the following format:

=IIF(Expression to evaluate,
what-to-do when the expression is true,
what-to-do when the expression is false)

There are a few rules and notes to follow.

• There are three sections within the parentheses, each delimited by a comma (no comma after the 3rd part, only after the 1st two parts).
• (Unlike T-SQL statements), in the Reporting Services expression editor, text is delimited with double quotes (not single quotes).
• Always begin expression statements with the equals (=) sign.
• Keywords are not enclosed with quotes. You will know you are using a keyword because it automatically turns blue.
• Until the syntax is correct, you will see a squiggly red underline under part or all of the expression phrase. Your expression will not work until this is gone.  (That doesn't mean it WILL work, it only means it will never work while you have the wrong syntax.)

Put together, here's what to do:

1. Start with =IIF(
2. In the first part of the IIF(), create the expression you will be evaluting.
3. Next, type a comma and then the name of the report you will be drilling through to, delimited by double quotes (i.e. "NameOfReport"). NOTE: In some cases of SQL 2005, with SP2 in Sharepoint Integration mode (like mine), you will have to add ".rdl" at the end of the report name such as "NameOfReport.rdl". This has the alarming result of disabling the link in the designer, but when you publish to the Sharepoint server, it will work just fine. If you leave it without the .rdl, the designer link will work but it may not work when you publish. Experiment and see for yourself.
4. Next, type a comma and then the word Nothing (without quotes). This is a keyword.
5. Close the parentheses )

Use the parameters button as you normally would with drill through reports.

More notes:

• The keyword Nothing means just that. Do not display anything. In this case, it means, 'do need provide any link'. Had we used two double quotes (i.e. "") the link would not have gone to any report, but there would essentially be a link to nowhere. (The user would get an error when clicking on it.) Using the keyword Nothing prevents the error and creates the desired effect.
• You could modify this procedure to send the user to "Report1" if the expression is true and "Report2" if the expression is false, by replacing the Nothing keyword with the name of "Report2". (Don't forget the double quotes for "Report2" in that scenario).
• Alternatively, you could use the SWITCH() function in a similar way (but that's a topic for another tip).

Next Steps

• Reporting Services is a great feature of SQL Server that is still underutilized.  If you are not already using Reporting Services take the time to evaluate it and see how it can be used in your environment.
• In this tip you can see that you have the ability to customize how your users interact with your reports based on data content. This allows you to domically generate your reports based on your data without having to hardcode features.
• Take a look at these other Reporting Services tips.

About the author

Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips