Is it possible to use MATCHES in a CASE statement?

Hi,
I calculate the SLA times using the difference in days between transitions and that is OK, only that I would like to calculate that on different SLA Levels such as “Gold” and “Silver” and I was thinking to use CASE statement.
I have multiple SLA for GOLD and for SILVER, according to the PRODUCT and type (HW or SW) so that I would like to avoid creating as many WHEN sections as LEVELS and wanted to create only 2, one for GOLD and one for SILVER.
To do so I was thinking to use MATCHES, e.g.

CASE [Issue].CurrentHierarchyMember.Get('SLA')
      WHEN MATCHES "GOLD" THEN ...

I notice this is not meant to work so the question I have is if there is any way to do what I need to do.

Or shall I create as many “WHEN” sections as SLA levels?

thanks
alexandre

Hi Alexandre,

Please, check the documentation about the two types of the CASE statement:
https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference/case-statement

You can use the second type of syntax with the condition like this:

CASE 
WHEN
CoalesceEmpty([Issue].CurrentMember.GetString('SLA'),"") MATCHES ".*GOLD.*" THEN ...
WHEN
CoalesceEmpty([Issue].CurrentMember.GetString('SLA'),"") MATCHES ".*SILVER.*" THEN ...

ELSE ...
END

Note the following:

  • Matches function is sensitive to empty value, so I protected it with the Coalesce empty
  • Matches function requires a regular expression, otherwise the condition will be true only in the cases of exact match

Kindly,
Janis, eazyBI support

Thanks Janis,
much appreciated!
It works well
Alexandre