Build a list from a field or Group in SSRS

Is it possible to build a “list” (single string variable) of values from a single field?

For the sample data set:

Job#   Seq#   Part#    Qty#
100     10    AB-1234   10
100     20    XY-9999   1
100     30    ZZ-0022   5
200     10    AB-1234   20
200     20    XY-9999   2
200     30    ZZ-0022   9
300     10    AB-1234   5

I want a header row to include "Jobs: 100, 200, 300"

  • No repeats (I can run this at the Job# group level)
  • It’s okay if it has to be the footer row.

Thanks in Advance

What is this report Showing. If it is possible to group this by something that there would only be one group per list you can add a column that passes into code the values you want to add to the string and at the footer of the group you can call that variable which will display what you want. Example below.

New column Expression: code.AddToString(FieldToAddToString)

Footer Expression: code.RetString()

Code:
Public Dim BuiltString As String

Public Function AddToString(ByVal Field As String)
BuiltString = BuiltString + Field
End Function

Public Function RetString() As String
Return BuiltString
End Function

I am not sure if there is a different way but this is how I do it when I need to. Just a Note This method would have to show all the Detail values. The only other way I can think of would be to use lookup but it does not appear you have any lookup value in which to use.

Matt

EDIT: Never mind. Found it (Thanks Google!)

Thanks Matt. I’m used to the “three variable trick” in Crystal, and I’m fairly new to SSRS.

So were exactly doe one enter the “Code:” in SSRS?

If you right click in the gray area around the report and click report properties on the left you will see a tab for code. The code in SSRS is VB for your reference.

Thanks. I got it working.

It seems the code.function only gets called if the cell it is in is visible.
I know if the code doesn’t return a value, nothing displays, but the blank field still shows.

How can hide field (or whole row) with a calculation in it, so the calc still happens?

With SSRS this isn’t possible to hide and still evaluate. I believe there is a way to Create a Drilldown that can hide it but it would still allow it to be visible if the user chooses to.

Can you give an example of what you mean When you say hide row or field.

I have a BAQ report that uses a filter tab to let the user select one or more Jobs.

I want to display the job numbers selected (highlighted line in pict below). To do this I have a tablix above that cycles through the records and builds the string (using the method from Matt Spots post above).

But in order for the expression code.AddToString(JobNum) to be executed in the upper tablix, the row it is in must be visible. The 4 orange rows are the ones ones where code.AddToString(JobNum) are called.

If the row is set to hidden, the call to code.AddToString(JobNum) doesn’t happen. The best I can do is to set the height of each row to the minimal, but that still makes a variable sized space on the rendered output.

What would be ideal would be if the BAQ returned a string consisting of a comma separated list of the JobNum the user selected. Builtin Reports have this. It appears that BAQ Reports 's do not.

image

You could also create another dataset from SQL. Then call that field. Don’t know the rest of your report, just another option maybe.

This creates a comma delimited list based on JobNum of the erp.JobAsmbl table.

SELECT substring(list, 1, len(list) - 1) AS JobList
FROM   (SELECT list = 
          (SELECT DISTINCT JobNum + ','
           FROM  Erp.JobAsmbl
           ORDER BY JobNum + ','
           FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')) AS T

How would I implement that in a BAQ? As a sub-query?

In the SSRS report as another dataset. Temp tables are created you should be able to grab those.

Capture