I am trying to obtain the top 5 Quantity accounts for all Territories in

District5.

The problem is that our Measures have no values when they are

not split by a dimension. The only way to get measures with values is to

CrossJoin [Measures].[Qty] on [NRx or TRx].[Total RXs] or [NRx or TRx].[New

RXs]. Because the TopCount function below is looking for [Measures].[Qty]

which is empty because I have not cross joined to [TotalRxs] I just get the

first 5 accounts for each Territory in district 5.

WITH

SET [TotQty] AS 'CROSSJOIN({[NRx or TRx].[Total RXs]},

{ [Measures].[Qty] } ) '

select

{ [TotQty] } on columns,

Generate( {[Sales Territory].[SalesForce3].[Region2].[District5].Children},

CrossJoin({[Sales Territory].CurrentMember},

TopCount( [CAM RAM Deployment].[Account Name].Members, 5,

[Measures].[Qty] ) ) )

on rows from NDCDocLevel

When I try to force the topcount to look at Qty for Total Rxs, I get errors

because I just can't figure out how to get the set of tuples that

CROSSJOIN({[NRx or TRx].[Total RXs]},{ [Measures].[Qty] } ) to yield the

numeric expression that the TOPCOUNT is looking for.

-------------EXAMPLE OF 1 of MY BAD MDX statements-----------------

WITH

SET [TotQty] AS 'CROSSJOIN({[NRx or TRx].[Total RXs]},

{ [Measures].[Qty] } ) '

select

{ [TotQty] } on columns,

Generate( {[Sales Territory].[BlueRed].[RT01].[DT03].Children},

CrossJoin({[Sales Territory].CurrentMember},

TopCount( [CAM RAM Deployment].[Account Name].Members, 5,

[TotQty].Members ) ) )

on rows from NDCDocLevel

------------TOKEN IS NOT VALID [TotQty].^Members^------------------

Can anyone please help me find the correct syntax to return the numeric

expression "Qty" from the set of tuples based on

CROSSJOIN({[NRx or TRx].[Total RXs]},{ [Measures].[Qty] } )?

Much appreciated,

Glenn

gengelbart at Domain below:

www.ihsiq.com