Written by

Question Ahmad Bukhtiar · Oct 6, 2022

MDX Syntax Help

#HealthShare #Analytics #Cubes #MDX

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

Add reply

Comments

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=D2…

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

0 0

Ahmad Bukhtiar Oct 7, 2022 to Peter Steiwer

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?

0 0

Sam Duncan Oct 11, 2022 to Ahmad Bukhtiar

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)

0 0