MDX Calculation script : syntax error with tuple s

问题:

I'm trying to design a calculation in a cube's MDX calculation so that when a specific value of a dimension is used, the behavior/values returned by the measure is change to something else, based on other values from the same dimension attribute.

here's my code sample :

CREATE SET CURRENTCUBE.[NonBreakDownIndicators]
AS {([Indicator Label].[Indicator].MEMBERS - [Indicator Label].[Indicator].&[Indicator Daily Diff])}
; 


SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
   //SCOPE([NonBreakDownIndicators]);
        THIS = ([Indicator Label].[Indicator].&[SensiDiv],[Measures].[Daily Diff Unit]);   
        //THIS = [Measures].[Daily Diff Unit];   
    //END SCOPE;
END SCOPE;

What we can see here is that I want to create a tuple with some dimension 'Indicator' values, except one. I have 3000+ values in this dimension attribute.

Next, when a user select, among others, the attribute Indicator, and among the selected values, one is 'Indicator Daily Diff', I want to change the values returned by the measure 'Value Unit', to instead get the values from 'Daily Diff Unit'.

This last measure is tested and working.

So far I've managed to get a result with the currently uncommented code. But as you can see this will only work for 1 value of the 'Indicator' dimension, when I would need to make it work for thousands of them.

I've tried to use a sub scope as you can see in the commented code, but I get this exception :

An arbitrary shape of sets is not allowed in the current context.

I've also tried to use the Tuple set [NonBreakDownIndicators] in the THIS statement like this :

SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
        THIS = ([NonBreakDownIndicators],[Measures].[Daily Diff Unit]);   
END SCOPE; 

But then I would end up with a 'null' value in my cube (not empty or not showing, a cell is filled with null).

I'm not SSAS expert and I'm mostly doing stuff hoping to get some keywords to look up, but so far internet blogs have had little infos on my topic, and I can find anyone around me with enough knowledge on this technology.

Does anyone have an idea about how I could achieve this without having to specify every single Indicator the MDX?

PS: I can't use a measure to solve this. My client explicitely asked for this 'Daily Diff Unit' to be shown as indicator, in order to have only one measure, and as many indicator as they need.

Edit

I did this change and indeed it resolve the 'arbitrary shape of sets' error. But now I don't get any figure back when querying.

I can see other figures on other 'Indicators', but no cell is returned for the [Indicator Daily Diff] indicator. I'm using this right now :

CREATE SET CURRENTCUBE.[NonBreakDownIndicators]
AS {([Indicator Label].[Indicator].[Indicator].MEMBERS - [Indicator Label].[Indicator].&[Indicator Daily Diff])}
; 

SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
     SCOPE([NonBreakDownIndicators]);             
        THIS = ([NonBreakDownIndicators],[Measures].[Daily Diff Unit]);   
    END SCOPE;
END SCOPE; 

回答1:

Try changing the set to the following to see if that avoids the arbitrary shape error:

CREATE SET CURRENTCUBE.[NonBreakDownIndicators]
AS {([Indicator Label].[Indicator].[Indicator].MEMBERS - [Indicator Label].[Indicator].&[Indicator Daily Diff])}
; 

You can have random members of a single level and scope on that set. But you can't have random members of multiple levels and scope on that set. The change above removed the All member from the set which I believe should ensure all members in the set are on the same level.

Then I would try:

SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
        THIS = ([Measures].[Daily Diff Unit]);   
END SCOPE; 

Or possibly:

SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
        THIS = SUM([NonBreakDownIndicators],[Measures].[Daily Diff Unit]);   
END SCOPE; 

回答2:

(Posted on behalf of the question author).

Following the advice of Greg, here is the MDX code that answer my need perfectly:

CREATE DYNAMIC SET CURRENTCUBE.[NonBreakDownIndicators]
AS {(
[Indicator Label].[Indicator].[Indicator].MEMBERS 
- [Indicator Label].[Indicator].&[Indicator Daily Diff]
- [Indicator Label].[Indicator].&[Params Variation]
- [Indicator Label].[Indicator].&[Secondary Variation]
- [Indicator Label].[Indicator].&[Primary Variation]
- [Indicator Label].[Indicator].&[Expired Variation]
)};  

SCOPE([Measures].[Value Unit], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
        THIS = SUM([NonBreakDownIndicators],[Measures].[Daily Diff Unit]); 
END SCOPE;

SCOPE([Measures].[Value k¤], [Indicator Label].[Indicator].&[Indicator Daily Diff]);
        THIS = SUM([NonBreakDownIndicators],[Measures].[Daily Diff k¤]); 
END SCOPE;

It even allowed me to decline the behavior for other measure groups (simple values, counter valorised values,...). It supports the currently used Indicators and filter out the others.