Reporting Services condition based drill through reports

By: Rob Fisch   |   Read Comments (3)   |   Related Tips: > Reporting Services Drill Through

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.

Last Update: 7/31/2007

About the author

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

View all my tips

We Recommend

Comments and Feedback:

Wednesday, March 18, 2009 - 3:41:58 AM - parwinder	Read The Tip
Hi I am facing a problem regarding use of expression for drill through reports. I have appended ".rdl" at the end of report name, so it is working on WSS site , but it is giving error in designer. Do you have any solution for the same?

Sunday, June 02, 2013 - 10:36:30 PM - Ned Bakelman	Read The Tip
This is awesome. Thank you very much.   Ned

Friday, June 14, 2013 - 2:44:24 AM - Naveen Khanna	Read The Tip
Hi,   I was searching for the same solution from last two weeks. Today I found it and its realy gud one.Thanks Rob.

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

Find and fix SQL Server problems before they happen - SQL diagnostic manager now with predictive analysis! SQL Developer Bundle: Cut out dull work with 12 tools for simpler, faster database development. Free trial What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant in the USA. Free SQL Server performance monitoring software! Improve performance by 65% today with IgniteFree. The SQL Server Security THREAT - It’s Closer Than You Think