[SOLVED] SSRS Group Summary - Multiple Records in Single Row

SSRS Group Summary - Multiple Records in Single Row

I need to reformat an SSRS document to summarize the same Item and Lot Numbers on one line instead of breaking them out on individual lines by the PKG #.

For example:

ITEM1234, LOT1234, PKG #'s 1 - 5, 8, 11, 16

The current format is as such:

ITEM1234 / LOT1234 / PKG1 
ITEM1234 / LOT1234 / PKG2 
ITEM1234 / LOT1234 / PKG3 
ITEM1234 / LOT1234 / PKG4 
ITEM1234 / LOT1234 / PKG5 
ITEM1234 / LOT1234 / PKG8 
ITEM1234 / LOT1234 / PKG11
ITEM1234 / LOT1234 / PKG16

Ideally, we would like to see the item and lot on one line, and a combination of all packages on only one line following, turning this 8 line combo in 2 lines.

ITEM1234 / LOT1234 
PKG 1,2,3,4,5,8,11,16

Does anyone have an idea on how would we go about doing this?

Solution

You'll want a table to group by both your ITEM and LOT numbers and add a second line for the second line of data.

To concatenate the package numbers, you could use the LOOKUPSET function to get the data and the JOIN function to convert the multiple lines of data to a single string.

="PKG " & 
Join(LookupSet(Fields!ITEM.Value & Fields!LOT.Value
    , Fields!ITEM.Value & Fields!LOT.Value
    , REPLACE(Fields!PACKAGE.Value, "PKG", "")
    , "DataSet1"), ", ")

The REPLACE function is used to get rid of the PKG in front of each number.