Question
Ahmad Bukhtiar · Oct 6, 2022

MDX Syntax Help

we have this container code where we want to create more CalcMember properties

<sharedCalcMember FullName="HSAAPATIENT||DEMOGRAPHICS||CITIZENSHIP.NATIONAL.sharedCalcMember" Dimension="Demographics" Name="Citizenship.National" Expression="[Demographics].[H10].[Citizenship].&amp;[USA]" SolveOrder="0" CubeName="HSAAPATIENT" UpperCaseDimension="DEMOGRAPHICS" UpperCaseName="CITIZENSHIP.NATIONAL"></sharedCalcMember>

So we need some syntax help how to use INLIST, NOTINLIST and CASE expressions. Anyone can help what should be the syntax for Expression to handle below 3 situations. 

Syntax 1 - inlist values:
citizenship value in list ('USA','U.S.A','US','U.S', etc etc) 

Syntax 2 - NOT inlist values:
citizenship value not in list ('USA','U.S.A','US','U.S', etc etc)  
Syntax 3 - case statement :
case
when DiagnosisGroup between 'A00' and 'B99' then 'A00-B99'
when DiagnosisGroup between 'C00' and 'D49' then 'C00-D49' end DiagnosisChapter;
Product version: IRIS 2021.2
0
0 101
Discussion (3)3
Log in or sign up to continue

Hi Ahmad,

These functions do not exist in MDX, please see the MDX function documentation here: https://docs.intersystems.com/iris20221/csp/docbook/Doc.View.cls?KEY=D2R...

You likely want to use the %OR function: https://docs.intersystems.com/iris20221/csp/docbook/DocBook.UI.Page.cls?...

Using %OR would allow you to reference multiple members, for example:
Expression="%OR({[Demographics].[H10].[Citizenship].&amp;[USA],[Demographics].[H10].[Citizenship].&amp;[U.S.A],[Demographics].[H10].[Citizenship].&amp;[US]})"  

Thanks Peter for your help %OR will help

Expression="%OR({[Demographics].[H10].[Citizenship].&amp;[USA],[Demographics].[H10].[Citizenship].&amp;[U.S.A],[Demographics].[H10].[Citizenship].&amp;[US]})"   

Can you advice how to use the same expression to say if citizenship value is not one of these values something like this NOT %OR?

There are a couple of ways you might be able to do this, but the simplest one is probably to use the %NOT MDX function. You can write a filter that excludes one member by appending .%NOT to the member spec:

%FILTER [Demographics].[H10].[Citizenship].&[USA].%NOT

If you want to write a filter that excludes multiple individual members of one or more levels, you can filter by a tuple of the members that you want to exclude. Append .%NOT to each of the member specs, and do not use %OR:

%FILTER ([Demographics].[H10].[Citizenship].&[USA].%NOT,[Demographics].[H10].[Citizenship].&[U.S.A].%NOT,[Demographics].[H10].[Citizenship].&[US].%NOT)