SSRS #72 – Use RunningValue() Function to Concatenate Row Values
I had a post quite a while ago on how to use FOR XML clause with PATH mode, which is available since SQL 2005, to concatenate row values into columns.
We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.
But to make it work, it can take more than a few tries.
In this example, I am going to use the MDX query from my post MDX #10 – Top 5 Resellers in Each Country.
Once I have the MDX query ready, I want to concatenate the top 5 resellers into a column, and the result will look like this:
A great post on beyondrelational.com has inspired this post. I forgot the link, but still want to attribute to the original blogger.
First add some custom code to the report
The first we need to do is to add some custom code to the report by going to the Report Properties.
In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.
Next create a row group
Next we need to create a row group, named “Group_Country”. In the group property, make sure that the group by column is [Sales_Territory_Country].
Use RunningValue in the expression
In the Top 5 Resellers expression, enter:
=RunningValue(Code.ConcatenateString(Fields!Sales_Territory_Country.Value, Fields!Reseller.Value), Max, "Group_Country")
- The ConcatenateString() function take 2 parameters, Fields!Sales_Territory_Country.Value and Fields!Reseller.Value.
- The aggregate function Max is the second parameter for the RunningValue() function.
- The last parameter is the name of the row group.
- The RunningValue() function iterates through each [Sales_Territory_Country] in the row group, finds each reseller, concatenates them, until it finds all the resellers in each [Sales_Territory_Country].
Concatenating row values into column can be easily done in TSQL, and also in SSRS.