Search code examples
scopessasmdxdatamart

MDX Scope returning NULL #VALUE

I have been on this issue for the past 2 weeks and am getting bored of MDX and my work.

I have a scope script to override the figure coming from the source table for the following lines. 1. Pool Charge (2075) 2. Pool Compensation (2076)

SCOPE ([Measures].[Income Amount]);  

SCOPE ([Income Caption].[Captions].&[2075],[Measures].[Income Amount]);                   //POOL CHARGE 
this = [Measures].[Charge];
END SCOPE;

SCOPE ([Income Caption].[Captions].&[2076],[Measures].[Income Amount]);                   // POOL COMPENSATION
this = [Measures].[Compensation];
END SCOPE;

Screenshot of the sample reports.

enter image description here

I want to further manipulate those 2 lines in green: I want to sum-up other Pool Contribution entries and place it in MD LINE. Please note MDLine exclusive. The same goes to Pool Charge.

MD LINE Pool compensation = 'ASSET & INVESTMENT MANAGEMENT' + 'CORPORATE BANKING' + 'GENERAL DEFAULT' + 'INVESTMENT BANKING' + 'TREASURY AND GLOBAL MARKETS' Please note only Pool compensations and Pool charges.

All efforts been returning null or #VALUE.

Please help.

Many thanks.

Solution

  • What about?

    Scope ([Income Caption].[Captions].&[2075],[Measures].[Income Amount]);                   
    This = IIF(
        [Something].[Something].CurrentMember is [Something].[Something].[MD LINE]
        ([Something].[Something].[All],[Measures].[Charge]),
        [Measures].[Charge]
    );
End Scope;

    Replace Something with the corresponding dimension and hierarchy.