Search code examples
sqlssasmdxcubeolap-cube

A small error in my mdx query

    SCOPE([Trans Tasks FFMS Farah GVV].[Task Status].&[1].MEMBERS, [Trans Tasks FFMS Farah     GVV].[Task Sub Status].&[1].MEMBERS);
    THIS =[Measures].[Maximum Insertion DT];
    END SCOPE;

The above query generates an error ("the syntax for scope is incorrect")

What im trying to do: I want to display the max insertionid only for the pair of values of status=1 and substatus=1. In the cube all the status and substatus values show but i want the calculation to be only for those (1,1 as mentioned). Is that the right way to go about it? Maximum Insertion ID, is a measure i created to return the max insertionid.

Solution

  • For others to benefit: the scope keyword cannot be used in the expression for a calculated measure. Instead I used and Case statement (could have used IIF but i caused problems for me in some cases). The case statement would be like case when status and substatus =1 then do call the max measure.