Substring from start to next comma Report Builder

Question

I have the result of a data set that contains graph values, but it is contained in an odd format as follows:

[[42016,44969],[{"name":"$41,000/year goal","y":41000}],["",""]]

What I want to do is basically break up this data with something like Mid (), but instead of specifying exact characters, I want to indicate the start after the character and end before the next character.

An example is getting the value "42016" from the data above, for example:

=Mid(Fields!LinearReturnData.Value,"[[",",")

essentially says the beginning after the characters "[[" and ends in the next comma. I understand that for the first value I could use

=Mid(Fields!LinearReturnData.Value,3,5)

but this data will change and not always be a five-digit number, and I will also need the same type of thing for other values, such as breaking “44969” and extracting the value “41000” as the third data point.

thank

Answer 1

Try the following: -

For the first number: -

=mid(Fields!ID.Value,3,instr(Fields!ID.Value,",")-3)

For the second issue: -

=mid(Fields!ID.Value,instr(Fields!ID.Value,",")+1,instr(Fields!ID.Value,"]")-(instr(Fields!ID.Value,",")+1))

For the third issue: -

=mid(Fields!ID.Value,instr(Fields!ID.Value,"""y"":")+4,instr(Fields!ID.Value,"}")-instr(Fields!ID.Value,"""y"":")-4)

They work for the following data set: -

[[42016,44969],[{"name":"$41,000/year goal","y":41000}],["",""]]
[[4206,144969],[{"name":"$41,000/year goal","y":41000}],["",""]]
[[4204566,69],[{"name":"$41,560,000/year goal","y":41560000}],["",""]]