SSRS - How to get a part of a string

Question

I have a parameter called Analyst group in this format : [Dimension].[Analyst Group].&[Nl.Workplace.Foundation]

I want to pass this parameter to another report, to filter data. Its a multi value parameter. But the other report only accepts it in this format : [KanBan].[Analyst Group].&[Nl.Workplace.Foundation]

So im trying to isolate the "Nl.Workplace.Foundation", so i can do the following thing in the Go To Report parameter expression :="[KanBan].[Analyst Group].&["& --Isolated analyst group-- &"]" to create the desired format.

So what i need is to extract the part between .&[ and ]

But i really have no idea how to isolate that part of the string.

Answer 1

Found a solution! If i just use the Parameter.label instead of Parameter.value it automatically does what i want!

Answer 2

A different solution has been found, but I will still answer the initial question. It could help.

So what i need is to extract the part between .&[ and ]

You could use a regex. This may not be the fastest way but it can handle most of the situations.

So let's assume you have a string containing:

[Dimension].[Analyst Group].&[Nl.Workplace.Foundation]

And you want to get the following string:

Nl.Workplace.Foundation

Just use the following expression:

=System.Text.RegularExpressions.Regex.Match("[Dimension].[Analyst Group].&[Nl.Workplace.Foundation]", "\.&\[(?<NWF>[^]]+)\]").Groups("NWF").Value

In the expression, replace the input string with your dynamic values, like for example:

=System.Text.RegularExpressions.Regex.Match(Fields!Dimension.Value & "." & Fields!AnalystGroup.Value, "\.&\[(?<NWF>[^]]+)\]").Groups("NWF").Value

I'm keeping the formula as simple as possible so that you can easily adapt it, with, say, handling the case where an input string will not have a match (with the above query it will return #Error).

You could do this by adding an IIF() or better, use a custom function that you can reuse in several places and will reduce the length of your expression.