Calling a detail report using the intersection of Row and Column Sum as parameters

Question

Description: I have a report that that aggregates ATM transactions. This report has a Tablix with the following attributes: Rows; Vendor, Terminal. Columns; Month, Day (Date), Hour.

The Row groups Terminal as child of Vendor and the Column groups Hour as child of Date and Date as child of Month. The Row Group and Column Group properties specify that each child visibility is toggled by its parent and the default if each child visibility to be hidden.

The Placeholder Value property for the intersection is set to Sum() transactions. The Action is set to Go To Report and specifies a detail report that accepts; Start Date, End Date, Vendor, Terminal As parameters to the detail report. Each time the link is clicked, it calls the detail report with the dataset parameters set at runtime. So, for “North”, the detail report would reveal 41765 rows associated with the vendor “North”.

Problem: No matter what resolution of detail in the matrix, when clicking the Sum() value to invoke the detail report, the dataset parameters set at runtime are sent to the detail report. For instance, if I drill down from Month to Date, I see that for Vendor “West” the sum of transactions on 2018-07-01 are 81. If I click that link to summon the detail report, it returns the total rows for the Start Date and End Date (1577 rows), rather than for the Date (81) as expected. This behavior is replicated throughout the intersection combinations.

Request: How do I call up the detail report such that it returns only those rows specified at the resolution of the intersection in its current state? Ie: the sum at Month\Date: Vendor or the sum at Month: Vendor\Terminal or the sum at Month\Date\Hour: Vendor\Terminal….

What is the best method to accomplish the request? My research has not come up with any valid suggestions. I've attempted to use InScope() in the Placeholder Properties expression but can't seem to get it right.

Thanks for your help!

Answer 1

Your thought is correct, you have to use Inscope

Step 1:

For each parameter you use in your main report to call the subreport use an expression like

= Iif( Inscope("matrix1_Terminal"), Fields!Terminal.Value, Nothing)

For numeric parameters (like month number) set a dummy value like -1 because NULL values are not allowed

= Iif( Inscope("matrix1_Month"), Fields!Month.Value, -1)

Step2:

In the subreport change your parameters to accept NULL values (only for strings and dates)

Step3:

On the subreport adjust your query code to handle dummy parameter values

For string or dates

WHERE (terminal = @Terminal or @Terminal IS NULL)

For numeric values

WHERE (month = @Month or @Month=-1)

Important!! Don't forget to pass to the subreport other parameters needed like filters in the WHERE of the original report

Tip: In the subreport use temporary string parameters for debugging and pass the values of your Inscope expressions