Posts Tagged ‘Concatenate Row Values’

MDX #19 – There is more to do to concatenate row values into column

October 19, 2012 Leave a comment

Continuing from MDX #18 – Easy to concatenate row values into column in MDX.

I have not accomplished the goal of concatenating the set of top resellers in each country.

Count() function can count the number of tuples in a set

In this query, I am adding a count to show how many top sellers in the set for each country. The number [Top n] is 21, which is obviously wrong. It should show 3 for each country. 21 in some sense is correct too, since there are indeed 21 tuples in the set  (3 resellers x 7 countries including an ALL country). 


Adding a Existing key word to forces set [Top n Resellers per Country]  to be evaluated within the current query context


Autoexists rule, which prevents tuples of attributes from the same overall dimension (such as attributes zip code and city in customer dimension) from existing if the dimension did not have at least one combination of them (meaning valid zip codes in a city.

Here [Country] and [Reseller] are two attributes that come from two different dimensions [Sales Territory] and [Reseller], the Autoexists rule doesn’t seem to apply.

Existing is a useful MDX operator that evaluates set_expression within the current member context (current member context sounds a little foreign, but it really means the sub-space in our Y axis which is the [Country] in dimension [Sales Territory] in the above query). If the existence of members in set [Top n Resellers per Country] depends on the current member context (in all dimensions other than just those  of the set [Top n Resellers per Country]), the returned set will reflect the context. The long verbiage means that the returned set will be only in the context of each country, which is 3.

See EXISTING Keyword (MDX) for more details.

Show all 3 tuples in each country in a big string

SetToStr() function takes a set and converts it into a big string with each member being fully qualified.


The big string is not really useful to our business users.

Get a list of the reseller name only

We can use the second variation of the Generate() function to strip off the qualifiers and only get the name value for each member.


Although the Item() function works with the above query, I am still a little fuzzy on how the nested Item() function works.

This is how the set of 3 tuples looks like for France. Each tuple has two fully qualified members

{    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[638]),
    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[175]),
    ([Sales Territory].[Sales Territory Country].&[France],[Reseller].[Reseller].&[85])

I guess the first Item(0) returns each tuple, and the second item() function returns the first member France when index is 0 and returns the second member when the index is 1. Item() function works on zero-based index.

With the help of Count(), Existing, Generate() and Item() function…..

Finally we concatenated the three top resellers in each country.

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

August 13, 2012 2 comments

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.


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

%d bloggers like this: