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

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

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

clip_image001

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

clip_image002

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.

clip_image003

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.

clip_image004

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.

About these ads
  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: