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.
Yes! And the way it’s created, it does end up with a sequence like this “~~~~~~~” for fields that are blank, but I wouldn’t have thought that would mess things up. Or would it?
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
FROM
mytable
CROSS APPLY
STRING_SPLIT(mytable.myfield, ‘~’);
This is Report Data Definition driven. When creating a new dataset, would I just have to call that QuoteDtl table again or would I be able to look at the specific UD field that I’m trying to look at in the first dataset? Sorry, I’ve never really tied in multiple datasets to a report.
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.
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
FROM
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.
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.
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)
AS
(
**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.
