Regular expressions
Regular expression is a powerful way to filter data in reports or calculated measures and members using name patterns.
Regular expressions to filter report rows
In the report, when you click on a report column with text data (e.g., company or person name, list or tags or labels, etc.) and select Filter rows→ matches and not matches, then you can enter a regular expression that will be used to filter report rows.
Simple match
A simple regular expression is a fragment of a text that you want to match.
Regular expression John
will match John Smith
or Peter Johnson
.
Beginning or end of string
Use ^
to indicate beginning of the string or $
to indicate end of the string.
Regular expression ^John
will match John Smith
but will not match Peter Johnson
.
Regular expression Smith$
will match John Smith
but will not match Smith John
.
Alternatives
Use |
to separate alternate matching options.
Regular expression John|Peter
will match both John Smith
and Peter Jackson
.
Grouping
Use parenthesis (
and )
to specify subexpression.
Regular expression gr(a|e)y
is the same as gray|grey
and will match both gray
and grey
.
Repeating elements
?
indicates there is zero or one of the preceding element. For example, colou?r
matches both color
and colour
.
*
indicates there is zero or more of the preceding element. For example, ab*c
matches ac
, abc
, abbc
, abbbc
, and so on.
+
indicates there is one or more of the preceding element. For example, ab+c
matches abc
, abbc
, abbbc
, and so on, but not ac
.
.
indicates any character. For example, not matches .
will match all empty, while matches .
will match all non-empty strings.
Case insensitive matching
Use (?i)
to specify case insensitive matching.
For example, (?i)john
will match John
, JOHN
, and john
.
Regular expressions in calculated members
Regular expressions may also be used when creating calculated members with Filter function and MATCHES operand. In this situation, the complete expression should be more precise: including the beginning and the end of string regular expressions are mandatory.
For example, filter fix version by name:
- To return all versions which ends with "hotfix" syntax for expression
'.*hotfix$'
- To return all versions which starts with "hotfix" syntax for expression
'^hotfix.*'
- To return all versions which contains "hotfix" syntax for expression
'.*hotfix.*'
Read more about available regular expression elements usable for MDX calculations in Java regular expressions.