In Reporting Services (SSRS), I found myself in a situation where I needed to extract friendly labels from MDX member keys. I had a fiscal year parameter being passed to another report via a drill through action, and I needed to display the selected fiscal years on the drill through report. So I had to convert something like [Fiscal Period].[Fiscal Year].&[2014], [Fiscal Period].[Fiscal Year].&[2013] to “Fiscal Year: 2014, 2013”.
There are a couple of ways of doing this. If your labels always contain the same number of characters, you can use the MID function as shown later. I have multiple multi-value parameters being passed, however, so I wanted to be able to reuse one function for all of them. I figured there had to be a way to grab the text between the &[ and last ] in the MDX. There is a way, and the answer lies with REGEX and a bit of custom code.
First, here’s how the Fiscal Year parameter is being passed to the drill through report in a variance column:
|
1 |
=Split("[Fiscal Period].[Fiscal Year].&[" + Cstr(Parameters!FiscalYear.Value) + "], [Fiscal Period].[Fiscal Year].&[" + Cstr(Parameters!FiscalYear.Value - 1) + "]",", ") |
Let’s say 2014 is selected in the report. The expression above turns 2014 into [Fiscal Period].[Fiscal Year].&[2014], [Fiscal Period].[Fiscal Year].&[2013] because variance involves the currently selected and prior fiscal year. The split function turns the string into an array.
At this point you may be thinking, “He’s converting friendly fiscal year labels into an array of MDX values, and then converting it back to friendly labels? What kind of Rube Goldberg solution is this!?”. You’d be right to wonder this. It is likely more elegant to pass parameter values around as friendly values, and convert them to MDX in the Parameters section of the Dataset Properties. I may make this change eventually, but when you’re working in a rush you often go with what works and optimize it later when you have time.
OK, so now we have an array of MDX values arriving at the drill through report. Here’s the code that loops through the array and extracts the friendly values from the MDX using REGEX:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Public Function JoinMdxLabels(myArray as object) As String Dim ReturnString as string, Item as string, FirstItem as boolean = true For each Item in myArray If FirstItem = True ReturnString = System.Text.RegularExpressions.Regex.Match(item, "(?<=&[)[^]]+(?=])").Value Else ReturnString = ReturnString & ", " & System.Text.RegularExpressions.Regex.Match(item, "(?<=&[)[^]]+(?=])").Value End If FirstItem = False Next item Return ReturnString End Function |
We now have a comma separated string of friendly labels. To display these labels on the report, the following expression is used:
|
1 |
="Fiscal Year: " & Code.JoinMdxLabels(Parameters!FiscalYear.Value) |
The nice thing about the code above is that it can be reused in any case where you have an array of MDX member keys. If you only need this functionality once, and all of the members have the same number of digits (Fiscal year, fiscal period, postal code, etc.), you can use the MID function instead of REGEX:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Public Function JoinFiscalPeriod(myArray as object) As String Dim ReturnString as string, Item as string, FirstItem as boolean = true For each Item in myArray If FirstItem = True ReturnString = MID(item,34,2) Else ReturnString = ReturnString & ", " & MID(item,34,2) End If FirstItem = False Next item Return ReturnString End Function |
As I admitted earlier, this is likely not the most elegant solution. You could eliminate the custom code by passing parameters to drill through reports as friendly labels, and then converting them to MDX in the Dataset Properties menu. Nonetheless, this shows the sort of thing you can do with custom code in SSRS, and provides the REGEX to extract friendly labels from MDX keys.
1 Comment