Home > SSRS Expert > SSRS #72 – Use RunningValue() Function to Concatenate Row Values

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.

T-SQL – Concatenate row values in Transact-SQL

We can achieve the same in SSRS with a wonderful aggregate function, RunningValue() function.

image

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.

image

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:

image

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.

image

In the Code section, create a custom function ConcatenateString() to concatenate resellers for each country. It will take Country and Reseller as 2 parameters.

image 

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].

image

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.

About these ads
  1. Prashant
    September 28, 2012 at 5:59 am

    Hi,

    In SSRS Datetime Parameter I need to select Time as well.

    May I know how to activate/Enable Time Selection in SSRS?

    Like

  2. Alphonse
    May 5, 2013 at 10:14 pm

    Excellent! This got me over a rough spot. Thank you.

    I would also like to know if anyone has an idea how to handle the situation where the dataset is not ordered by Sales_Territory_Country…

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 175 other followers

%d bloggers like this: