0

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|Prior Year Sales |

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

VAI Jason
  • 534
  • 4
  • 14

1 Answers1

1

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?

Daniel Wagemann
  • 741
  • 4
  • 6
  • Interesting. I think just in general, even if it wasn't DMR. Even if it was just relational. A list can have different drill through per column (like Sales 2019 and Sales 2018). In a crosstab with more than one metric, is there a way to have a different drill through for each metric? – VAI Jason Oct 23 '19 at 14:08
  • 1
    Ah OK, so you are looking to override the Cell Value(where it selects all measure values). You can do this by selecting the cell under the column and using the Define contents to Yes, then drag the item into the empty cell – Daniel Wagemann Oct 23 '19 at 17:48
  • Define content worked! Thank you. As a side note, I had to make another layer in the DMR for year - 1 in order to get the prior year to pass in the drill through (couldn't figure out how to get year - 1 to work in the query, tried as a data item, a calc, etc) – VAI Jason Oct 23 '19 at 22:15