locked
SSAS Reports: Filter based on a Measure, include Recursive Parent RRS feed

  • Question

  • I have a report based on a SQL 2008 R2 SSAS Cube. I am using a Dimension that is a Parent/Child, and that manifests itself as a Recursive Group in the report. No problem. Now I want to filter the report based on a Measure, for example: a Calculated Measure of [Prior Year Sales]. I can do this by FILTERING the Data Set. (actually the filter is based on a Calculated Member in the MDX query, which in turn is a set of nested IIF ( ) statements that 'bucket' based on the measure.)

    Here's the issue: Suppose I have data as:

    Name                      Measure    Bucket
    =====                  =====    =====
    PARENT ROW #1     $400
    Child A                    $200   BUCKET#2
    Child B                    $100  BUCKET #1
    Child C                    $100  BUCKET #1

    There is a parameter that accepts "BUCKET #2" as one of the valid values. When that is selected, I want the following rows on the report:

    PARENT ROW #1     $400
    Child A                    $200   BUCKET #2

    But when the Filter is applied to the DataSet, the PARENT ROW is filtered out because it is NOT in BUCKET #2. In fact, he is not in any bucket as the bucketing shoiuld be done ONLY at the Child level.

    If this was a traditional one-to-many hierarchy, I would have fields of:

    PARENT #1       Child A   $200     BUCKET #2
    PARENT #1       Child B...
    PARENT #1       Child C...

    and the filter would work. But with a Parent/Child slash Recursive Group, it doesn't work.

    What are my options, apart from re-designing the Dimensional Hieararchy to break out all the distinct levels?

    Any help would be appreciated.

    Thanks


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Tuesday, August 7, 2012 8:13 PM

Answers

  • Hi Todd

    Yeah that's what I'm getting at, you need to test for both levelnumber = 1 as well as the parameter.

    You could also try doing it at the level of the MDX query where you filter members that meet the Bucket parameter you pass through OR that are the parent node.


    Josh Ash

    • Marked as answer by Todd C Wednesday, August 8, 2012 2:39 PM
    Wednesday, August 8, 2012 1:10 AM

All replies

  • To avoid the parent row being filtered out, have you considered doing something as trivial as checking the level number ?

    So you could create a calculated member that is 1 when a bucket is assigned to the level, OR when the level is top level of the hierarchy, and then use that calculated member as the filter? 


    Josh Ash

    Tuesday, August 7, 2012 11:02 PM
  • Josh:

    Thanks for the suggestion, but I'm not sure I follow along your reasoning.

    I guess part of the issue is that when you create SSRS Reports using MDX statements with SSAS sources, you typically have filters on dimension members that manifest themselves as Parameter mappings in the dataset properties page. But now I'm trying to filter on a Measure, which (as far as I know) can only be done on the Filter page of the Dataset properties dialog.

    Maybe a Filter statement of:

    =IIF(Fields.DimensionMember!LevelNumber = 1, True, False) OR (Fields.Bucket_Assignment!Value = Parameters.ChooseBucket!Value)

    (Boolean Type)

    = True

    Think that would work?

    I'll try tomorrow.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Wednesday, August 8, 2012 12:19 AM
  • Hi Todd

    Yeah that's what I'm getting at, you need to test for both levelnumber = 1 as well as the parameter.

    You could also try doing it at the level of the MDX query where you filter members that meet the Bucket parameter you pass through OR that are the parent node.


    Josh Ash

    • Marked as answer by Todd C Wednesday, August 8, 2012 2:39 PM
    Wednesday, August 8, 2012 1:10 AM