SSRS – Self Referencing Reports or Drill through to same report

When looking at a report and wanting to drill through to get more detail, we often create two reports for this. This can be accomplished by just one report by creating a self-referencing action that links to the same report. This can be done even if the report has parameters on it.

clip_image002

Above is a simplified example report. The list on the left contains the product ID’s. The table on the right shows the details for the product that the user clicks on. Also notice the different background color to show which item the user clicked. There is a parameter on the report for the product color. There is hidden parameter on the report for the product ID also.

There are two datasets on this report. The first contains the product ID’s. Here is that query.

SELECT ProductID

FROM Production.Product

Where Color = @Color

The second contains the Product details. Here is that query.

SELECT *

FROM Production.Product

Where Color = @Color and ProductId = @ProdId

Nothing special is done to the color parameter. The Product ID parameter needs a few adjustments. It needs to be set to allow blanks and nulls, and be hidden.

clip_image003

Add the two tables and use the details dataset on the details table and the product Id dataset on the product ID table.

Next you will create an action on the Product ID table. Right click on the data field and select textbox properties.

clip_image004

Click on the Action node. In here you will select “Go to Report” and select the report you are on as the drill down report. There are two parameters. The color parameter will map to itself. To do this click on the fx expression box next to the Color parameter and select the color parameter in the expression window. Select the product id from the drop down menu next to the product id parameter.

clip_image005

The last option is to highlight the background color of the product id that was clicked. The expression for that is below. You are comparing the selected product id with the product ID on the table and changing the background color if they match.

clip_image006

Now run the report and click on a product Id. Check the first image in the blog to see the results. Now you don’t have to leave the current report to see details.

About these ads
This entry was posted in SSRS, Syndication and tagged . Bookmark the permalink.

3 Responses to SSRS – Self Referencing Reports or Drill through to same report

  1. Phippsy says:

    I don’t get it. How does the drill down work? A before and after of the report would be helpful….

  2. Phippsy says:

    I don’t get it. A before and after picture of the report would be helpful, because I don’t really understand how you are linking to the same report and getting something different to display. It’s not clear why this is an advantage over a second report.

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