Good morning!

Working on an SSRS report in Microsoft Report Builder and having trouble with a one-dimensional array. This all stems from using a configurator in our quoting process. We have data we want to send to the report, but the configurator sends it as an XML file, which limits us to 50 characters. To get around this, we’ve started writing the data to a UD field separated by “~”. In a calculated field within the report, I use the Split() function to create a one-dimensional array with all of the values (currently includes blanks, but can get around to that later).

The issue I’m finding is that when I try and use grouping to loop over the array, it just prints out a blank value. I can grab individual values just fine if I use Split(Fields!BigString.Value, "~").GetValue(x) but each UD field could be made up of 20 different values (will be different for various configurators) so that’s not a very feasible option. I was expecting to just be able to declare that calculated field and then type that into the expression, but are there more steps I am missing? I am showing the big string above so I know there are values, but below that is where I’m trying to display each of those values in a bulleted list.

Any suggestions are greatly appreciated!

when I try and use grouping to loop over the array, it just prints out a blank value. I can grab individual values just fine if I use Split(Fields!BigString.Value, "~").GetValue(x) but each UD field could be made up of 20 different values (will be different for various configurators)

So are you saying that Fields!BigString.Value has a value like this, “item1~item2~item3~item4”?

These are from bing chat… so use with caution.

But the idea is that you leverage SQL here to do that for you, not try and write it in VB or in an SSRS expression… You see what I am saying?

you can start off by creating a new dataset in your report. Is this a BAQ report, direct SQL report, reportdata definition driven, out of the box report?

SELECT
mytable.id,
value
mytable
CROSS APPLY
STRING_SPLIT(mytable.myfield, ‘~’);

There was something from insights 2022 that I remember and dug my old book out for (Page 8) if you were there and have it.

Basically it passes the custaddress that way and separate it by ‘~’.

You will have to adjust this accordingly since this isnt an exactly similar issue but maybe the replace to vbcrlf would work? Basically it went as followed.

=Replace(Replace(Replace(Replace(Replace(Fields!Calculated_Calc_CustAddr.Value, "~~","~") , "~~", "~") , "~~", "~") , "~~","~" , "~", vbcrlf)

Doing it that way will keep it all under the same bullet point.

I was hoping to keep it separated out a bit more to make it a little easier to read like this:

But if using another dataset won’t work, this solution is better than what I had in mind so thank you!

We are on Cloud. I tried to write up a query for the dataset, but am getting an error. Here’s what I have so far

SELECT
  Dtl.QuoteNum,
  Dtl.QuoteLine,
  Dtl.Character01 as Inc
  QuoteDtl_" + Parameters!TableGuid.Value + " Dtl
CROSS APPLY
STRING_SPLIT(Dtl.Character01, "~")
All it’s telling me is there is incorrect syntax near “Dtl”. Tried to mirror the structure of the query that came from the RDD, but not sure if I have to go about this one differently?
              
Eventually you are going to be trying so hard to make the formatting be the way you want where you’re going to make the query complex.
The alternative is to do what others were saying and just do a string split with an SSRS expression or VB expression like you were originally trying to do and then write it to a variable with character returns included and then print it accordingly in a text field. The bullet points won’t show.
  QuoteDtl_" + Parameters!TableGuid.Value + " Dtl 
CROSS APPLY 
STRING_SPLIT(Dtl.Character01, '~')"
Side question: what does putting “value” in as a field do?
              
the hard part about this Matthew, and somethign I don’t understand fully, is you can’t just copy and paste sql expressions into their text query editor. You’ll get these errors.
Try to make this thing all one line, with each word in your select statement only separated by a space.
              
You see this? We can tell this is going to work, because we can do it in a designer and it’s working fine.
You can see that I made up some random field like yours and then ran that same sql logic with the cross apply and you see I get  a row for each item like you wanted.
image1140×680 19.1 KB
              
Putting it in as one line seems to have gotten the query to work! Although when I print it out, it just repeats the first entry
But that example you show is exactly what I’m looking for!
              
I often use the “Visibility” expression on an RDL/SSRS. Using something like (if this cell = previous cell, then hide). I am not sure how to implement it with the restrictions you have, but if your 1d Array breaks up onto rows, then the visibility options might help hide some of the ones you don’t want (like the blanks).
              
I would recommend doing what @utaylor has said, but do it as a CTE instead.
So take your main table query in the report and add the version that works to the beginning like this:
="; With SplitQ (field1, field2, field3)
**Your working query here**
**Rest of the SSRS Query already there**
Now you will be able to add your SplitQ results into the main results. It is a little confusing if you have never done it before. If you need help, post your qorking query that splits out your field and the main SSRS query and I can put it together for you. Or fire away with questions if you want to try and do it yourself.