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.
- SSRS #72 – Use RunningValue() Function to Concatenate Row Values
- T-SQL – Concatenate row values in Transact-SQL
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.
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.
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
- loop through the [Top n Country] set,
- 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.
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.
