Random SSRS Musings 1 : Rowset Concatenation Using Native SSRS Expressions
Context
A common reporting requirement is to show a set of related data as a delimited list. There are multiple ways of implementing this in T-SQL with methods ranging from User Defined Function based concatenation logic up to the latest high performing FOR XML PATH method.
The best approach is to try to do as much of this on the database side. There are certain cases where this is not possible.
Recently there was a forum question whether the same requirement can be implemented through SSRS expressions as the developer had no access to change the back-end procedure used by the dataset. Yet another scenario where this has to be dealt with at the reporting end is when the data comes from more than one sources which are heterogeneous.
This article discusses on how the requirement can be implemented in SSRS report using native SSRS expressions.
Illustration
The scenario can be illustrated using a simple dataset as shown below
Consider the case of cars getting assigned to guests in a hotel. This shall be represented by the below two tables (for simplicity i'm following a flat structure and including only the main columns)
- GuestVehicleAllocation Table consisting of GuestID, GuestName, JourneyDate, VehicleID, PlateNumber fields and
2. DriverVehicleAllocation consisting of DriverID,DriverName,JourneyDate,VehicleID, ContactNumber fields
There will be two drivers allotted per each car one as a reserve driver. The requirement was to show the report in the below format
GuestName JourneyDate PlateNumber VehicleType DriverContacts
The DriverContacts field had to display DriverName and ContactNumber values in comma separated format
For this requirement there was a procedure provided with resultset as below
GuestName JourneyDate PlateNumber VehicleType DriverName ContactNumber
The sample data looks like below
Solution
The solution for this problem can be designed as below
In our SSRS report add a simple tablix to display the required data.
Since the attempt is to merge multiple rows from the source into one its obvious that we need to apply some kind of grouping. For this purpose add a row group on the tablix based on the set of fields based on which we want to merge the rows. So in the above example we would need to group on the fields GuestName, JourneyDate, PlateNumber and Vehicle.
Once the grouping is added we can add the common fields as is within the columns within the row. The only case where we need to add an expression would be for DriverContacts fields for getting a delimited list. We make use of LookupSet function for this purpose.
LookupSet is a very useful addition to SSRS functions which was introduced from SSRS 2008 onwards. Its used mostly for doing lookup of values between two datasets based on common field / set of fields. The function will return you the set of matching values as an array. We implement a self lookup operation using LookupSet to generate the concatenated value array for the merged rows and then make use of Join function to convert the array of values to a delimited list in our case.
On the whole the sample report for our scenario looks like below
The expression used for DriverContacts column looks like below
=Join(LookupSet(CStr(Fields!GuestName.Value) + "," + CSTR(Fields!JourneyDt.Value),CStr(Fields!GuestName.Value) + "," + CSTR(Fields!JourneyDt.Value),CStr(Fields!Drivers.Value) + " (" + CStr(Fields!PhNo.Value) + ")","DataSet1"),",")
Now try running the report and you will get your data displayed in the below format
As you see from the above the rows will get merged based on the grouped columns and shows the allotted driver information in delimited format.
Summary
As shown above we can make use of the LookupSet function to do a self join to concatenate multiple column values into single rows column inside SSRS expressions