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].&[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
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].&[USA],[Demographics].[H10].[Citizenship].&[U.S.A],[Demographics].[H10].[Citizenship].&[US]})"
Thanks Peter for your help %OR will help
Expression="%OR({[Demographics].[H10].[Citizenship].&[USA],[Demographics].[H10].[Citizenship].&[U.S.A],[Demographics].[H10].[Citizenship].&[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)