How can I pass the correct year in the drill through?
When using a list - each column can have it's own drill In a crosstab, the drill through appears to be the same for all cell values (showing metrics)
In this example, the crosstab has two metrics -- current sales and prior year sales They are stitched by company and year The join for prior year sales is based on Year -1, this way the Report can show a specific Year (like 2019) and the facts adjust accordingly (i.e. Current year sales is for 2019 and prior year sales 2018, even though the crosstab shows 2019 for the year). The metrics display correctly
Because of this approach (using a crosstab), the facts share the same drill through
How can I pass the correct year in the drill through? I have the drill through using the Year and sales rep. Because of this the drill through shows the selected year, EVEN when selecting the metric for prior year
Crosstab looks like this
| Year (like 2019 |
| Month (like 9) |
Sales Rep|456 | 123 | |523 | 0 | |623 | 123 |
Drill through pass data item -- Sales Rep, Year (which works) However, when selecting prior year, the drill through is passing 2019
Reminder, in a list, each column has a separate drill through Btw: If I try to change the list to a crosstab, I lose the drill through definitions
So I can't say I fully understand how you are getting your prior year, I fear with 2019-1 and stitching joining items, you maybe getting out of MDX(DMR) functionality and in to SQL(Relational) contexts.
If you are using DMR and have the current Year projected on an edge, you could get the previous year via the lag function, more complex dynamic functions such as parallel period also exists.
Does the drill behavior change if you project the 2019 member and a calc of lag(2019) projected next to it?