none
Currency conversion MDX - change scope of accounts dimension (exclude statistical accounts) RRS feed

  • Question

  • Hi all

     

    I used the currency conversion wizard to generate the MDX of the currency conversion calculation. The calculation works fine but I have now to change the scope of the accounts dimension on which the calculation is performed since statistical accounts are not concerned by the currency rate conversion.

     

    My Accounts dimension is a parent-child dimension. The leaves are not always on the same level.

     

    The following MDX has been generated by the wizard:

     

    // <Currency conversion>

    // Currency conversion wizard generated script.

    // Currency conversion generated on: Donnerstag, 23. Oktober 2008 15:18:17

    // by user: adminbiz

    // Currency conversion type: ManyToMany

    // Selected members to be converted: Amount

    // Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again.

    // This is the Many to One section

    // All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension and for each entities' currency

    Scope ( { Measures.[Amount]} );

    Scope( Leaves([Date]),

    [Reporting Currency].[US Dollar],

    Leaves([Currency]));

    // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate - ERP]

    Scope( { Measures.[Amount]} );

    This = [Reporting Currency].[Currency].[Local] * (Measures.[Rate - ERP], StrToMember("[Currency ERP].[Currency].["+[Currency].[Currency].CurrentMember.Name+"]"));

    End Scope;

    End Scope; // Leaves of time, Entity currencies and pivot currency

    // This is the One to Many section

    // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension

    Scope( Leaves([Date]) ,

    Except([Reporting Currency].[Currency].[Currency].Members,

    {[Reporting Currency].[Currency].[Currency].[US Dollar],

    [Reporting Currency].[Currency].[Currency].[Local]}));

    // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate - ERP]…

    // LinkMember is used to reference the currency from the source currency dimension in the rate cube.

    Scope( { Measures.[Amount]} );

    This = [Reporting Currency].[Currency].[US Dollar] / (Measures.[Rate - ERP], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency ERP].[Currency]));

    End Scope;

    End Scope; // Leaves of time, all reporting currencies but local and pivot currency

    End Scope; // Measures

    // End of the currency conversion wizard generated script

    // </Currency conversion>

     

    I tried to add an additional scope using:

     

    except(

    [Account Hierarchies].[AccountHierarchies Parent].members,

    descendants([Account Hierarchies].[AccountHierarchies Parent].[STATISTICAL ACCOUNTS],,SELF_AND_AFTER))

     

    but I receive the error message:

     

    Leaves function can't be used with mixed attributes in distinct measure groups ...

     

    Thanks a lot for your help


    .
    Friday, October 24, 2008 7:36 AM