Search code examples
ssas

SSAS Cube - Excel Drill through not filtered as required

I have a SSAS DSV similar to following structure:

Id      Type    Special
1       A       1
2       B       Null
3       A       Null
4       C       1
5       C       Null

I built a dimension for this DSV including one attribute for Type.

Then I have in my cube three measures

  • Measure1: Count of rows
  • Measure2A: Sum of Special
  • Measure2B: Count of non-empty values for Special

Finally in Excel, I display data as following:

  • Rows --> Type attribute
  • Values --> Measure1 / Measure2A / Measure2B

When I look at the results, everything is correct. For instance, I get a count of 1 for measure2A and measure 2B for row = C

BUT when I attempt to drill through for related cells, instead of getting 1 row, I get 2 (the ones where type = C without considering the value of Special)

I guess I am doing something wrong in my design of the cube but cannot understand what.

Solution

  • When determining what rows to show in drillthrough SSAS only considers the dimension context not which detail rows have a non null measure value.

    You could add a new dimension on the Special column and add that dimension as a filter to your PivotTable.

    Or you could install ASSP and construct a custom rowset action that fires an MDX query which does a NON EMPTY on your measure.

    http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home