Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Reporting Services condition based drill through reports

MSSQLTips author Rob Fisch By:   |   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
MSSQLTips author Rob Fisch
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



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.


Sunday, June 02, 2013 - 10:36:30 PM - Ned Bakelman Read The Tip

 

This is awesome. Thank you very much.

 

Ned


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?




 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.