All,

I am really stuck in trying to figure out how to determine percentages
inside a CROSSJOIN where I'm only returning the TOP 3 (or Top 'x')
items.

Let's say I have some products and some customers. I have a the
following type of SET:

SET MyInfo AS ' {
GENERATE (
{ DESCENDANTS (Products , ProductName ) } ,
TOPCOUNT ( NONEMPTYCROSSJOIN (
{ [Products].CurrentMember },
{ DESCENDANTS ( Customers , CustomerName ) }
) , 4 , Measures.Units ) )
} '

This information works and I get basically what I need. I get the Top
'4' customers for each product based on units sold.

What I'm having a hard time doing is summing the top '4' units for each
product. My output looks something like this:
Product Customer Units
======= ======== =====
ABC C1 12
C2 10
C3 8
C4 2
XYZ C1 11
C2 10
D1 5
D2 3

What I want to do is to provide a percentage and a average based on the
top 4 items returned. I want the results to be something like the
following:
Product Customer Units %
===== ======== ===== =======
ABC C1 12 37.5 (12/32)
C2 10 31.25 (10/32)
C3 8 25 ( 8/32)
C4 2 6.25 ( 2/32)
XYZ C1 11 36.67 (11/30)
C2 10 33.33 (10/30)
D1 5 16.67 ( 5/30)
D2 4 13.33 ( 4/30)

The value in the paranthesis are just to show where the percentages
should be coming from. The '32' is from 12+10+8+2 and the '30' is
from 11+10+5+4.

I want to be able to show my percentage (and also averages) based on
the items in the top '4' of my customers.

I have the MDX query working (using Analysis Services 2000) if I don't
return the Top 'x'. I can get the value if all the customers for the
product are returned. The problem happens when I return only the top
'x' customers. I've modified this query to be products and customers
but I can post the actual query that I have working to a point if
needed.

Hope that makes sense and thanks, in advance, for your help.

Here's a Foodmart Sales cube query, which lists the % share of each of
the Top 4 cities, for each Product Family:

'GENERATE(
DESCENDANTS(Product, [Product].[Product Family]),
TOPCOUNT (NONEMPTYCROSSJOIN(
{[Product].CurrentMember},
DESCENDANTS (Customers , [Customers].[City])
), 4, [Measures].[Unit Sales]))'
Member [Measures].[Top4Share] as
'[Measures].[Unit Sales]/
Sum(Generate({[Product].CurrentMember} as CurProd,
Filter(MyInfo, [Product].CurrentMember is CurProd.Item(0).Item(0))),
[Measures].[Unit Sales])',
FORMAT_STRING = 'Percent'

select {[Measures].[Unit Sales], [Measures].[Top4Share]} on 0,
Non Empty MyInfo on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Deepak,

Thanks for the post and solution.

Can you explain what is actually going on here:
Member [Measures].[Top4Share] as
'[Measures].[Unit Sales]/
Sum(Generate({[Product].CurrentMember} as CurProd,
Filter(MyInfo, [Product].CurrentMember is CurProd.Item(0).Item(0))),
[Measures].[Unit Sales])',
FORMAT_STRING = 'Percent'

As the percentage is figured out, for each member, what is the FILTER
function doing? What does the "[Product].CurrentMember is
CurProd.Item(0).Item(0)" do in this respect? What I'm having a hard
time understanding is, how is the product of the GENERATE (or
CROSSJOIN) iteriated through when the query is executed and how can we
use that SET in the MEMBER. How this question makes sense.

As I was trying, I found that what I have below also worked. What is
the better way to attack this problem. What are the performance
considerations that I should make.

MEMBER Measures.SumAtLevel AS ' Sum ( { TOPCOUNT (
[Customers].CurrentMember.Parent.CHILDREN , 4 , Measures.[Unit Sales] )
} , Measures.[Unit Sales] ) ' , SOLVE_ORDER = 100
MEMBER Measures.MyPercentage AS ' Measures.[Unit Sales] /
Measures.SumAtLevel ' , FORMAT_STRING = 'Percent'

My idea was to take the CurrentMember of the Customers (in our example
that you provided) and then get the parent of it and the top 4 children
(which would return the same items as the intial SET and sum them up.
Then this could be used for the Percentage for that level. The
complete query based on what you provided would be as follows (both
return same results):

With SET MyInfo AS
'GENERATE(
DESCENDANTS(Product, [Product].[Product Family]),
TOPCOUNT (NONEMPTYCROSSJOIN(
{[Product].CurrentMember},
DESCENDANTS (Customers , [Customers].[City])
), 4, [Measures].[Unit Sales]))'

Member [Measures].[Top4Share] as
'[Measures].[Unit Sales]/
Sum(Generate({[Product].CurrentMember} as CurProd,
Filter(MyInfo, [Product].CurrentMember is CurProd.Item(0).Item(0))),
[Measures].[Unit Sales])',
FORMAT_STRING = 'Percent'

MEMBER Measures.SumAtLevel AS ' Sum ( { TOPCOUNT (
[Customers].CurrentMember.Parent.CHILDREN , 4 , Measures.[Unit Sales] )
} , Measures.[Unit Sales] ) ' , SOLVE_ORDER = 100
MEMBER Measures.MyPercentage AS ' Measures.[Unit Sales] /
Measures.SumAtLevel ' , FORMAT_STRING = 'Percent'

select { Measures.SumAtLevel , Measures.MyPercentage ,
[Measures].[Unit Sales], [Measures].[Top4Share]} on 0,
Non Empty MyInfo on 1
from Sales

Thanks,
Pallav

Deepak Puri wrote:

Hi Pallav,

Generate() is just used to create a set alias, to save the current
member of Product before Filter() iterates over MyInfo, when the current
member will change. This technique has been discussed earlier in the
newsgroup, like here:

http://groups.google.com/group/micro...olap/msg/75024
bf566d6dba1

From: George Spofford
Date: Wed, Sep 11 2002 11:01 am

you need to use the little-known set alias construct, and
Generate() to frame it:


Generate (
{ [DateTime].CurrentMember } AS [A],
Filter (
[DateTime].[Day].Members,
[DateTime].CurrentMember.Properties ("Day of Week") =
[A].Current.Item(0).Properties ("Day of Week")
)
)

Your approach will invoke TopCount() more times, which could result in
worse query time - you can check to confirm.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***