I would like to extract information, or the query axis element from a given MDX query. Suppose I have this query :
SELECT NON EMPTY {
Hierarchize({[Product].[Product Family].Members})
} ON COLUMNS,
NON EMPTY Hierarchize (
Union (
CrossJoin ( {[Time].[1997].[Q1]},
CrossJoin ([Store].[Store Name].Members,
[Store Type].[Store Type].Members
)
),
CrossJoin({[Time].[1997].[Q2]},
CrossJoin([Store].[Store Name].Members,
[Store Type].[Store Type].Members
)
)
)
) ON ROWS FROM [Sales]
WHERE {
Hierarchize({[Measures].[Unit Sales]})
}
The substring I would like to extract has pattern like this : [...](.[...]) or [...].[...].Members
Note that, ... means any alphanumeric, and what inside bracket means can be recurred. Thus, the results I expect are :
[Product].[Product Family].Members; [Time].[1997].[Q1]; [Time].[1997].[Q2]; [Store].[Store Name].Members; [Store Type].[Store Type].Members; [Measure].[Unit Sales]
I tried my best and finally figured this regex :
\[.*?[A-Za-z\s]\](.*?(\.\[.*?[A-Za-z\s]\])|(\.Members))
But the results are :
[Product].[Product Family]; [Time].[1997].[Q1]}, CrossJoin ([Store]; [Store Name].Members, [Store Type].[Store Type]; [Time].[1997].[Q2]}, CrossJoin([Store]; [Store Name].Members, [Store Type].[Store Type]; [Sales] WHERE {Hierarchize({[Measures].[Unit Sales]
Can someone give any correction with my regex? Any help would be appreciated.
Hmm, could you try this:
(\[[\w ]+\]\.\[[\w ]+\](?:\.(?:Members|\[Q\d\]))?)
This seems to work for me. I tried it on Rubular.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With