SSAS Drillthrough in Excel

Question

I have an issue when trying to drill through in excel. I have a fact table of employee terminations set up like the following. Each row is a termination.

Example Screenshot

For the drillthrough function in SSAS I am using ASSP.GetCustomDrilthroughMDX. Basically created a new action in the cube with the following action expression.

'call ASSP.ExecuteDrillthroughAndFixColumns("' 
+ ASSP.GetCustomDrillthroughMDX("
[$Employee].[Employee ID]
, [$Employee].[Company Name]
, [$Employee].[Last Name]
, [$Employee].[First Name]
, [$Employee].[Start Date]
, [$Employee].[Last Day Worked]
, [$Employee].[HR Title]
, [$Employee].[Working Title]
, [$Employee].[HR Status]
, [$Employee].[Location ID]
, [$Department].[Department Number]
, [$Department].[Department Name]
, [$Employee].[Supervisor]
, [$Division].[Division Name]
")
+ '")'

The Condition is

[Measures].CurrentMember IS [Measures].[Involuntary] or
[Measures].CurrentMember IS [Measures].[Voluntary]

When I bring the Desired, Undesired, Voluntary, Involuntary and Other measures into a pivot table and try drill into say the Voluntary, I get the detail of the total amount of employees terminated, not just the Voluntary. For example if 6 employees were Voluntary, and 4 were Involuntary, and I drill into the voluntary I don't get the 6 employees, I get all 10 detail rows. I'm hoping I don't have to create a separate measure group for each type of termination. Any ideas?

Please write your example table within the question, not linking an image. And give us some code too. — SBH
Ok, I have updated with a screenshot of my fact table and my action expression and condition used in SSAS Action. — Kyle Reierson

Kyle Reierson Kyle Reierson · Accepted Answer · 2014-11-10T20:45:57

So I figured this out. In the Cube Design, I clicked on each measure in the Termination measure group and went to properties. Under properties expand Source. For NullProcessing property, set it to Preserve. This will preserve the null values in the underlying fact table. By default SSAS treats nulls as zero. Hope this helps someone else.

SSAS Drillthrough in Excel

1 Answers