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.

GregGalloway · Answer 1 · 2018-01-08T12:34:24.430

2

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.

edited Jan 08 '18 at 12:34

answered Nov 13 '15 at 02:37

GregGalloway

11,355
3
16
47

Thanks @GregGalloway , I am tempted to try the second solution but it doesn't seem to me that the syntax is correct. The expression you have will always evaluate to true. It is out of the context of what is been selected. Can you please elaborate more on how would the expression look like? – BICube Nov 13 '15 at 17:35
Also, I tried the dynamic set solution and it didn't work. – BICube Nov 13 '15 at 17:51
If you want to post your attempt to use dynamic named sets, I'm happy to look at it. But I would try the measures approach first. – GregGalloway Nov 13 '15 at 22:21
my point is, the left hand side of your IIF statement is not aggregating on the selected members and if I use EXISTING, I go back to the initial problem. I will try to use your approach within a context of the selected members and will keep you posted. Thanks. – BICube Nov 15 '15 at 17:29
Thanks Greg. I ended up doing the following (listed as answer). – BICube Nov 17 '15 at 15:21

score 1 · Accepted Answer · answered Nov 17 '15 at 15:21

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.

Change the context with Subselect MDX from Excel client

2 Answers2

Linked