return numeric expression "Qty" from CROSSJOIN({[NRx or TRx].[Total RXs]},{[Measures].[Qty] })?

return numeric expression "Qty" from CROSSJOIN({[NRx or TRx].[Total RXs]},{[Measures].[Qty] })?

Post by Glenn Engelbar » Thu, 10 Apr 2003 00:33:02



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

 
 
 

return numeric expression "Qty" from CROSSJOIN({[NRx or TRx].[Total RXs]},{[Measures].[Qty] })?

Post by George Spoffor » Fri, 11 Apr 2003 01:08:40


Well, there is no single numeric value across a set. You'd usually aggregate
across the set, for example:

Sum ( [TotQty] )

which would be fine, giving you:

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,
                   Sum([TotQty])
) ) )
on rows from NDCDocLevel

Since the set has only one tuple in it, you could also use .Item(0) to get the
single tuple:

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].Item (0)  ) ) )
on rows from NDCDocLevel

or, if this is really pretty hard-wired, just dispense with the set since it's
not buying you anything there and use the tuple:

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,
                  ([NRx or TRx].[Total RXs], [Measures].[Qty] )
 ) ) )
on rows from NDCDocLevel

I can't help you pick between them since I don't know the context.

HTH


> 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

--
George Spofford
Microsoft MVP
Chief Architect / OLAP Solution Provider
DSS Lab
http://www.dsslab.com

ISVs & IT organizations: Find out how DSS Lab can speed your development!

 
 
 

return numeric expression "Qty" from CROSSJOIN({[NRx or TRx].[Total RXs]},{[Measures].[Qty] })?

Post by Glenn Engelbar » Sun, 13 Apr 2003 00:50:29


Thanks much George,  I went with Item(0) and it works fine!

Quote:> Well, there is no single numeric value across a set. You'd usually
aggregate
> across the set, for example:

> Sum ( [TotQty] )

> which would be fine, giving you: