Change the context with Subselect MDX from Excel client

Question

I have two time dimensions, production period and accounting period, and a measure that I want to aggregate with either dimension but not with both when users query the cube from excel. To do that I created a flag measure to check if both dimensions are being used

CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS  
    IIF (
       [DIM Accounting Period].[Accounting Period Hierarchy].CURRENTMEMBER.level.ordinal <> 0 and 
          [DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal = 0 , 
      1, 
 IIF ( [DIM Production Period].[Production Month Hierarchy].currentmember.level.ordinal <> 0 and 
       [DIM Accounting Period].[Accounting Period Hierarchy].currentmember.level.ordinal = 0 , 
      2, 
      3
     )
), VISIBLE =0;

Then I use this flag to create my measure as such

CREATE MEMBER CURRENTCUBE.[Measures].[Sales/day] AS 

  IIF([Measures].[AcctProdFlag] = 1 , 
      ([Measures].[Sales] / [Measures].[Accounting Period Day Count]),
   IIF([Measures].[AcctProdFlag] = 2, 
       ([Measures].[Sales] / [Measures].[Production Period Day Count]), 
        "NA")),
VISIBLE = 1, DISPLAY_FOLDER = 'Sales\Daily' , FORMAT_STRING = "#,###";

When I use this query from management studio, it works and returns "NA" as expected because both dimensions are being used

SELECT {[Measures].[Sales/day]} ON COLUMNS , 
       [DIM Production Period].[Production Month].MEMBERS ON ROWS  
 FROM [My Cube] 
 WHERE {[DIM Accounting Period].[Accounting Year].[2014], 
         [DIM Accounting Period].[Accounting Year].[2015]};

But when I add accounting year to filter in excel and add production period in rows, the measure shows values (it shouldn't as per the logic) when I select multiple accounting years but shows "NA" (as expected) when I select one accounting year. Turns out that when I select multiple years, excel send the following query to SSAS causing it to loose the context

 SELECT {[Measures].[Sales/day]} ON COLUMNS , 
         [DIM Production Period].[Production Month].MEMBERS ON ROWS 
FROM (SELECT (
              {[DIM Accounting Period].[Accounting Year].[2014],[DIM Accounting Period].[Accounting Year].[2015]}
              ) ON COLUMNS
       FROM [My Cube]
     ) 

Is there anything I can do to fix this?. I am using SSAS for SQL Server 2008 R2 and Excel 2013.

Answer 1

One way to do it is a dynamic named set as described in this post in the "Using dynamic sets to detect subselects" section.

The other way would be to build a measure group on top of DimAccountingPeriod connected only to that one dimension and build a count measure. Then to test if there are no filters on Dim Accounting Period:

IIf(
 [Measures].[Accounting Period Count] = ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All])
 ,1
 ,0
)

To expand further on how you would use that:

CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS  
    IIF (
       [Measures].[Accounting Period Count] < ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All]) and 
          [Measures].[Production Period Count] = ([Measures].[Production Period Count], [DIM Production Period].[Production Month Hierarchy].[All]) , 
      1, 
 IIF ( [Measures].[Production Period Count] < ([Measures].[Production Period Count], [DIM Production Period].[Production Month Hierarchy].[All]) and 
       [Measures].[Accounting Period Count] = ([Measures].[Accounting Period Count], [DIM Accounting Period].[Accounting Period Hierarchy].[All]) , 
      2, 
      3
     )
), VISIBLE =0;

You can try the dynamic set approach, but the measures approach perform best.

Answer 2

So I ended up doing the following to resolve the issue. I created two measures as NULL in the data source view and called them; [Measures].[Prod Months Used] and [Measures].[Acct Months Used] I also created calculated members to count all production months and all accounting months. Those worked as such -- This measure is created to count the number of production months in the cube

CREATE MEMBER CURRENTCUBE.[Measures].[AllProdMonths] AS 

    SUM(
      DESCENDANTS(
                   [DIM Production Period].[Production Month Hierarchy].[All],
                    [DIM Production Period].[Production Month Hierarchy].[Production Month], SELF
                   )
       , 1
       ), VISIBLE=0 ; 

-- This measure is created to count the number of accounting months in the cube
CREATE MEMBER CURRENTCUBE.[Measures].[AllAcctMonths] AS 

    SUM(
      DESCENDANTS(
                   [DIM Accounting Period].[Accounting Period Hierarchy].[All],
                    [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month], SELF
                   )
       , 1
       ), VISIBLE =0; 

Finally to count the number of used accounting months and production months I did the following:

-- This measure is scoped to count the number of used Production months in excel (including filters) 
-- It does that by overwritting all values except the All member. 
SCOPE (
          ([Measures].[Prod Months Used],
          [DIM Production Period].[Production Month Hierarchy].[Production Month].MEMBERS)
       ); 
 SCOPE DESCENDANTS(
                   [DIM Production Period].[Production Month Hierarchy].[All],, AFTER
                  );

      THIS =   1; 
          END SCOPE; 
 END SCOPE; 



-- This measure is scoped to count the number of used Accounting months in excel (including filters) 
-- It does that by overwritting all values except the All member. 
SCOPE (
          ([Measures].[Acct Months Used],
          [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Month].MEMBERS)
       ); 
 SCOPE DESCENDANTS(
                   [DIM Accounting Period].[Accounting Period Hierarchy].[All],, AFTER
                  );

      THIS =   1; 
          END SCOPE; 
 END SCOPE; 

and my flag would be evaluated according to the following expression

CREATE MEMBER CURRENTCUBE.[Measures].[AcctProdFlag] AS  

    IIF (
            [Measures].[Acct Months Used] < [Measures].[AllAcctMonths] AND 
            [Measures].[Prod Months Used] = [Measures].[AllProdMonths], 
      1, 
 IIF ( [Measures].[Acct Months Used] = [Measures].[AllAcctMonths] AND 
            [Measures].[Prod Months Used] < [Measures].[AllProdMonths] , 
      2, 
      3
     )
), VISIBLE =0;

This solution is working perfectly and performance is really good.