http://www.sqlservercentral.com/blogs/sherry-lis-bi-corner/2012/10/07/mdx-18-easy-to-concatenate-row-values-into-column-in-mdx/

Printed 2014/10/24 12:13PM

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

By Sherry Li's BI Corner, 2012/10/07

Concatenating row values (within groups) into column in SSRS and TSQL

Concatenating row values into column is a never ending topic for report developers. It should be a simple task. That is, if the rows are not within groups. If the rows you are about to concatenate are customers in each state (VS. all the customers), then the topic became a special case of aggregation (as opposed to SUM, AVG etc.).

We can achieve this in SSRS and TSQL, with relatively very little effort.

Easy to concatenate row values (not in groups) into column in MDX

This query retrieves the top 2 Countries in All Region. It does a few things:

clip_image001

What if our business users would like to show the top 2 countries in one row?

One quick way is to use the SetToStr() function

The SETTOSTR() function does what its name says, concatenating all members in the set into one big string.

clip_image002

A few of notes about the SETTOSTR() function:

There are two members, United States and Canada, in the named set [Top n Country]. They both are fully qualified with the dimension [Sales Territory] and the attribute hierarchy [Sales Territory Country].

I am pretty sure that business users will freak out if they see a report like this. What’s with all the crazy brackets, ampersands, etc?

It’s a very reasonable request to see just United States and Canada, separated by a comma.

Use the wonderful GENERATE() function in the place of  SETTOSTR() function

We can use the wonderful Generate function to

  1. loop through the [Top n Country] set,
  2. for each tuple, use the name() function or member_caption function to get just the caption of the member, instead of the fully qualified member reference.

clip_image003

A few notes about this query:

Generate( Set_Expression , String_Expression , Delimiter )



Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.