How to drill-through from matrix

[Anonymous]

I have a matrix configured with Company on Rows, Order Type on columns, and three measures in Values: Orders Logged, Orders Closed, Orders Outstanding. The 3 measures display on rows (I have Show on Rows selected).

The measure for Orders Logged is a count of the orders with an OrderLoggedDate in the month selected in the filter.

The measure for Orders Closed is a count of the orders with a ClosedDate in the month selected in the filter.

The measure for Orders Outstanding is a count of orders with a blank ClosedDate regardless of the filter month.

I have a drill-through set up to a tab that displays the Order Details. 
I want users to be able to right-click an Orders Logged cell and have the Order Details report show the Order rows for the Orders that make up that cell value.

For example, Orders Logged of Order Type "Type A" shows as 18, and of "Type B" as 7. I want users to be able to right-click on the cell that contains 7, and have the drill-through report show the Order Details for those 7 Orders.

For example, the Orders Logged Total row shows 25 (18+7). I want users to be able to right-click on the cell that contains 25, and have the drill-through report show the Order Details for those 25 Orders.

How can this be achieved?

[amitchandak]

@Anonymous , order opened, order closed and order outstanding have logic (join/userelationship or date logic) that give the number. So that measure should part of the drill report to match.

[Anonymous]

Not sure what you mean by "that measure should part of the drill report to match." Do you mean I need to create three separate drill-through reports, one for each of Orders Logged, Orders Closed and Orders Outstanding?

[amitchandak]

@Anonymous I am assuming Orders Closed is based on the join of closed date and date table. and this might be using use relationship(inactive join) . 

Now if this measure is not present in the drilled report then the filter will not happen on the closed date. It will happen on OrderLoggedDate , which is default active join (assumed)

You might need to three reports, depends

[Anonymous]

@amitchandakI considered creating three separate drill-through reports, but this would mean the user would see those three reports listed when they right-clicked. So for example, even if the user right-clicked on the Orders Closed cell, they would see the drill-through options for Orders Logged and Orders Outstanding as well, which is not a great user experience.

Instead I simply added the three measures Orders Logged, Orders Closed, Orders Outstanding as the first three columns on the drill-through Order Details report. The report shows all orders relevant to the row that the user right-clicked and the measures effectively are flags to indicate if each order is Logged, Closed, or Outstanding (note: each order can be in more than one of these). The user can sort by the desired measure column to see just those orders they are interested in.

While it is still not an ideal user experience, it solves two problems: having just one drill-through report; and having just one drill-through report option available when the user right-clicks the main report.