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.
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.
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.
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.
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.
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.
