How to create context-sensitive drill through in Tabular Model?

Jun 26, 2015 at 5:59 PM
Hello!

We have an SSAS Tabular solution that is replacing an existing multidimensional cube and we're struggling with the drill through functionality. We're using the BIDS helper actions editor (thanks so much for that!) and what we need is the ability to be a little more flexible and still retain the context.
What we seem to have found so far is that using the drill-through action we do get the context right (data is drilled through per the filters applied) but when using the rowset option we're getting all the data - even when trying to put a hard-coded condition in; I'm pretty sure we're missing something and we don't know enough about the different options to implement.
Here's what we're trying to do:
  1. Have a direct drill through, with context filters, preferably with the ability to rename columns to a nicer display name without the table name that comes as a default
  2. Create a drill through that manipulates the context - the scenario is this: the client looks at financial data on the pivot table, let's say he filters Q1 2014. He has the ability to right-click and drill through to the current context; that will return data for Q1 2014. He will have an additional action called "Drill through for entire year" - when choosing that, he will get the drill through for the entire year of 2014 (so we're looking at the filter, and just filtering by the year member of that dimension). They have this functionality in their old multidimensional solution using custom actions; they need it now too.
  3. Create an additional drill through that will perform the same logic, but ignore time dimension all together (an "all time" filter).
Both drill throughs should support renaming the columns, controlling the order and adding additional measures from the same measure group.
Oct 11, 2015 at 9:01 PM
Did you ever find an answer to this as I have a similar need. I want to pass a context sensitive date parameter (actually start date and end date) from a tabular cube to an SSRS report running on top of a sqlserver relational db.
Coordinator
Oct 12, 2015 at 12:58 AM
Start and end date you can do:

Start date:
{Existing [Date].[Date].[Date].Members}.Item(0).Item(0).Name

End date:
Tail({Existing [Date].[Date].[Date].Members}, 1).Item(0).Item(0).Name

Sorry Giri nobody replied. That doesn't happen often. Let us know if you still need help.
Oct 12, 2015 at 4:55 AM
furmangg thank you for the quick reply, I appreciate the help. I am new to this do not completely follow you and have a couple follow up questions.

I have a tabular cube and when I right click on the service account id (using an excel as the front end) I want to be able to drill to a service summary report. If I default the start and end dates in the SSRS report I can pass in the service account id and the report runs fine, but if I try to add dates (based on what I filtered with in the cube) it seems not to pick up the context (it tries to pass "all"). I am sure I am doing something wrong. Here is a screen shot in bids helper (screen shot of tabular actions editor) where I am trying to get the StartDate parameter filled in correctly

My date dimension is called [Date Dimension], my lowest level date field is called [Start Date]

Any thoughts or additional questions?
Coordinator
Oct 12, 2015 at 5:38 AM
You are missing a reference to the [Start Date] level in your parameter.

When you reference [Date Dimension].[Start Date].members you are referencing the entire hierarchy which includes the [All] member. The [All] member "exists" in relationship to all of it's children. (when you call "EXISTING" on a hierarchy you get the current member plus all of it's parents)

Where as when you reference [Date Dimension].[Start Date].[Start Date].members you are just referencing the list of start dates (excluding the All member).

So your parameter value expression should look like the one below:

UrlEscapeFragment( { existing [Date Dimension].[Start Date]__.[Start Date]__.members }.item(0).Item(0).Name )
Oct 12, 2015 at 11:34 PM
dgosbell thank you for the help.

I am getting closer. I had to modify what dgosbell gave me in the post above to eliminate syntax errors (Latest screen shot of the tabular actions editor but at least this gives me a date. The issue is it always selects the minimum value in the whole dimension not the minimum value of what the user selected/filtered on in excel.

Is there a way to do that?
Coordinator
Oct 13, 2015 at 12:06 AM
So this probably comes down to what values are being selected in Excel and how your hierarchies and attribute relationships are configured. The Existing operator looks along the attribute relationship chains. You may need to alter your attribute relationships. Can you post an image of those and maybe the attributes & hierarchies?
Coordinator
Oct 13, 2015 at 12:27 AM
This is Tabular so no attribute relationships. The problem is that the target on the action is the Service Account Dimension so you right click on one of them and the action only passes context on that one dimension into the action. Change the target to Cells. Then your Date dimension filter context will be passed into the action.
Oct 13, 2015 at 5:56 PM
furmangg when you say target to pick cells I am assuming you meant target type, which I did but then it is forcing me to pick a "MeasureGroupMeasure" under target. I have tried all of them, but when I right click I don't see any options under additional actions.

latest image of Bid helper TAE

on a side note is this a good set of documentation on what the target type and target options are?

Thanks again for your help.
Coordinator
Oct 13, 2015 at 7:41 PM
The standard MSDN documentation on actions has a full list here https://msdn.microsoft.com/en-us/library/ff929231.aspx

What are you right-clicking on in Excel? If you want to pull in 2 dimensions from the current context you need to be right-clicking on a measure value and the target needs to be the measure group that contains that measure (so usually one of your fact tables). You can't right click on a service account member as that could be ambiguous in terms of what the current date member is.
Oct 21, 2015 at 9:19 PM
Edited Oct 21, 2015 at 9:20 PM
I finally had a chance to get back to this. I think I know what caused the confusion. I had filters picking multiple items which caused the additional actions right click to be blank. If I only picked one item per filter (I tested with more than one filter) I get the ability to drill through using the additional action on the measure value.

Interestingly enough if I move the filter field to a row or column (and keep the filter) it allows me to drill.