Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

setting a measure value to NULL if a particular dimension is used in excel

Tags:

ssas

mdx

I have an SSAS-2014 cube. I want to set a particular measure to NULL if a particular dimension is being used in either axis or the filter pane of a pivot table in excel. Now, the most intuitive solution, is to scope this measure to not work with the members of that dimension. Say, I don't want the measure to work with accounting period members, then I use the following MDX in the cube:

CREATE MEMBER CURRENTCUBE.[Measures].[Test Measure] AS 1;

SCOPE([Measures].[Test Measure]);
SCOPE(DESCENDANTS([DIM Accounting Period].[Accounting Period Hierarchy].[All],,AFTER));
THIS = NULL;
END SCOPE;
END SCOPE;

This seems to work if I select one year in the filter of the pivot table. enter image description here

This seems good since excel send the following MDX back to the SSAS engine

SELECT NON EMPTY Hierarchize (
 {
  DrilldownLevel (
   { [DIM Production Period].[Production Month].[All Dates] }
   ,
   ,
   , INCLUDE_CALC_MEMBERS
  )
 }
)  ON COLUMNS
FROM [CUBE - Opex Analysis]
WHERE ( [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2015],
        [Measures].[Test Measure] ) CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

Problem happens if I select two members in the filter pane, as shown in this image enter image description here

and the reason for that seems to be related to the MDX that excel sends back to the engine. It encapsulate the items inside a subcube making the engine thinks that no accounting year is being selected. This is the MDX that excel uses:

SELECT NON EMPTY Hierarchize (
 {
  DrilldownLevel (
   { [DIM Production Period].[Production Month].[All Dates] }
   ,
   ,
   , INCLUDE_CALC_MEMBERS
  )
 }
)  ON COLUMNS
FROM (
 SELECT (
 { [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2015],
   [DIM Accounting Period].[Accounting Period Hierarchy].[Accounting Year].[2016] } ) ON COLUMNS
 FROM [CUBE - Opex Analysis]
)
WHERE ( [Measures].[Test Measure] ) CELL PROPERTIES VALUE
, FORMAT_STRING
, LANGUAGE
, BACK_COLOR
, FORE_COLOR
, FONT_FLAGS

Please note, That I also tried to use EXISTING trying to capture accounting years being used but I was having the same problem. Does anyone know a way to fix this?. I had a similar problem before, but implementing the solution I used here is overkill for performance Similar Question I am using SSAS 2014 and Excel 2013

like image 289
BICube Avatar asked Jun 30 '16 17:06

BICube


1 Answers

Hm, the only solution I could envisage for subcube scenario is to create dynamic set on probed dimension as described in the article of referred question. Then for your scope add another sub scope([DIM Accounting Period].[Accounting Period Hierarchy].[All]) and count DESCENDANTS([DIM Accounting Period].[Accounting Period Hierarchy].[All],,AFTER) in this scope. Compare it with count of the dynamic set. If two do match - you are not inside subcube from subselect, otherwise you are.

like image 83
Ferdipux Avatar answered Sep 22 '22 05:09

Ferdipux