MDX Syntax Help

Question

Question

Ahmad Bukhtiar · Oct 6, 2022

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

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

Discussion (3)3

Log in or sign up to continue

Peter Steiwer · Oct 7, 2022

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]})"

2 0

score 0 · Answer 1 · 2022-10-07T13:37:57-04:00

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?

score 0 · Answer 2 · 2022-10-11T11:38:38-04:00

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)