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

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

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:

  • Creates a named set, [Top n Country].
  • The named set contains top 2 countries in all sales territory. They are United States and Canada.

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:

  • Here is the syntax: SetToStr(Set_Expression)
  • It is primarily used to create a string-representation of a set that can be parsed by an another function. My example does not do any string parsing. VBA functions are routinely used for string parsing/manipulation in MDX queries (or calculations). Not all VBA functions are supported in MDX. Here is the reference for VBA functions in MDX and DAX. Replace() and many other VBA functions are not supported in MDX, which is a disappointment. But many other nice functions, such as IIF, LEFT, MID etc are supported in MDX.
  • The string that is returned is enclosed in braces {}, with each item in the set separated by a comma.

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:

  • I am using the second variation of the Generate() function: String expression syntax

Generate( Set_Expression , String_Expression , Delimiter )

  • See the MDX reference article for the details on how to use the Generate function, especially the second variation: http://technet.microsoft.com/en-us/library/ms145526.aspx
  • Here is a blog that shows an example of using the first Set expression syntax of the Generate() function: MDX #10 – Top 5 Resellers in Each Country
  • It does not take long for us to get familiar with the CurrentMember function, which returns the current member along a specified hierarchy during iteration. Here is the syntax: Hierarchy_Expression.CurrentMember.
  • But it might take some us longer to get to know the Current function. The syntax is: Set_Expression.Current. Instead of a member, the Current function returns the current tuple from a set during iteration. In our example, the first tuple is {[Sales Territory].[Sales Territory Country].&[United States]}, and the second tuple is {[Sales Territory].[Sales Territory Country].&[Canada]}. With the Name function, we strip off the qualifiers and retain only the caption.
About these ads
Categories: MDX Challenges Tags:
  1. No comments yet.
  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 169 other followers

%d bloggers like this: