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.