What I want to do is get a result set that looks like the following:

Product Family | Final G Code | Cust Ret Last Week | xrank
PF1 | GCodeZ | 8 | 1
PF1 | GCodeB | 7 | 2
PF1 | GCodeY | 7 | 3
PF1 | GCodeA | 3 | 4
..
..
..
PF2 | GCodeT | 7 | 1
PF2 | GCodeG | 5 | 2
PF2 | GCodeH | 4 | 3
..
..
..

Currently with the following query I have a subset of the desired
result for a Product Family given in the filter:

with
set [top 10 final g codes] as
'topcount([Attributed Class].[All Attributed Class].[US].children,
10, [Measures].[Cust Ret Last Week])'
member [Measures].[xrank] as
'rank([Attributed Class].CurrentMember, [top 10 final g codes])'
select
{[Measures].[Cust Ret Last Week], [Measures].[xrank]} on columns,
[top 10 final g codes] on rows
from
[MyCube]
where
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid],
[Product Family].[All Product Family].[PF1])

which results in:
Final G Code | Cust Ret Last Week | xrank
GCodeZ | 8 | 1
GCodeY | 7 | 2
GCodeB | 7 | 3
GCodeA | 3 | 4

Looking good. Now to get the Product Family in, I use crossjoin:

with
set [top 10 final g codes] as
'topcount([Attributed Class].[All Attributed Class].[US].children,
10, [Measures].[Cust Ret Last Week])'
member [Measures].[xrank] as
'rank([Attributed Class].CurrentMember, [top 10 final g codes])'
select
{[Measures].[Cust Ret Last Week], [Measures].[xrank]} on columns,
crossjoin({[Product Family].[All Product Family].[PF1]},{[top 10
final g codes]}) on rows
from
[MyCube]
where
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

Now the members of the result are ordered by xrank, but they are not
ordered by Cust Ret Last Week:

Product Family | Final G Code | Cust Ret Last Week | xrank
PF1 | GCodeZ | 8 | 1
PF1 | GCodeB | | 2
PF1 | GCodeY | 5 | 3
PF1 | GCodeA | 6 | 4

Can someone clarify what is happening here and help with any ideas to
work around the problem. I have also tried various uses of Generate()
with the same results.

I'm also curious regarding another subtlety involved. If I have 2 rows
in the result which have a common Cust Ret Count Last Week, the first
query above will rank them as desired. Even a tie is broken by some
algorithm (name/value/other?). Once the crosstab is applied then the
tie rows are removed from the final result. Is there any way to get
around that?

Finally, I have used the {[Product Family].[All Product Family].[PF1]}
in the crossjoin as a sample. What I really want is to use a subset of
the products. I can express the subset of products with:

select
filter([Product Family].[All Product Family].children,
[Measures].[Cust Ret Last Week] > 0) on columns
from
[MyCube]
where
([VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid],
[Attributed Class].[All Attributed Class].[US],
[Drive Diagnosis Code].[All Drive Diagnosis Code].[valid])

MDX doesn't seem to like subqueries. How would one go about getting
all these criteria on all these different dimensions into a named set
for the purpose of crossjoining with [top 10 final g codes]?

Thanks for any help!

First, the Product Named Set can be created by applying appropriate
dimension context in the Filter():

'Filter([Product Family].[All Product Family].children,
([Measures].[Cust Ret Last Week],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid],
[Attributed Class].[All Attributed Class].[US],
[Drive Diagnosis Code].[All Drive Diagnosis Code].[valid])
The problem with [top 10 final g codes] may be that Named Sets are not
dynamically re-evaluated, so instead all the cross-joined combinations
can be computed and saved:

'Generate([ProductSubset],
topcount(CrossJoin({[Product Family].CurrentMember},
[Attributed Class].[All Attributed Class].[US].children),
10, [Measures].[Cust Ret Last Week])'
member [Measures].[xrank] as
'Rank(([Product Family].CurrentMember,
[Attributed Class].CurrentMember),
[top 10 final g codes]) - (10 *
Int((Rank(([Product Family].CurrentMember,
[Attributed Class].CurrentMember),
[top 10 final g codes])-1) / 10))'

select
{[Measures].[Cust Ret Last Week], [Measures].[xrank]} on columns,
[top 10 final g codes] on rows
from
[MyCube]
where
([Drive Diagnosis Code].[All Drive Diagnosis Code].[valid],
[VMI].[All VMI].[valid],
[Source Code].[All Source Code].[valid])

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Deepak,

Awesome! There was a single missing ) but that wasn't hard to find.
Thanks for the help. I still have one more tricky report to solve.
Hopefully I won't have to resort to the boards...

Cheers,