Re: TOPCOUNT giving different results

Tech-Archive recommends: Speed Up your PC by fixing your registry

On Feb 27, 4:04 pm, "Gautham Kamath via SQLMonster.com" <u9571@uwe>
wrote:
The following are the three queries and their respective results that I tried
Against Adventure Works cube in Adventure Works DW SSAS database

Query 1:
Select [Measures].[Internet Sales Amount] on 0,
TOPCOUNT([Product].[Product Categories].[SubCategory].Members, 5,
([Customer].[Customer Geography].[Country].&[United States],[Measures].
[Internet Sales Amount])) on 1
from [Adventure Works]

Query 1 Results:
Internet Sales Amount
Road Bikes - $14,520,584.04
Mountain Bikes - $9,952,759.56
Touring Bikes - $3,844,801.05
Tires and Tubes - $245,529.32
Helmets - $225,335.60

Query 2:
Select [Measures].[Internet Sales Amount] on 0,
TOPCOUNT([Product].[Product Categories].[SubCategory].Members, 5, [Measures].
[Internet Sales Amount]) on 1
from [Adventure Works]
WHERE [Customer].[Customer Geography].[Country].&[United States]

Query 2 Results:
Internet Sales Amount
Road Bikes - $4,289,925.90
Mountain Bikes - $3,417,457.74
Touring Bikes - $1,292,475.90
Tires and Tubes - $88,769.73
Helmets - $76,768.06

Query 3:
Select ([Measures].[Internet Sales Amount], [Customer].[Customer Geography].
[Country].&[United States]) on 0,
TOPCOUNT([Product].[Product Categories].[SubCategory].Members, 5,
([Customer].[Customer Geography].[Country].&[United States],[Measures].
[Internet Sales Amount])) on 1
from [Adventure Works]

Query 3 Results:
Internet Sales Amount
Road Bikes - $4,289,925.90
Mountain Bikes - $3,417,457.74
Touring Bikes - $1,292,475.90
Tires and Tubes - $88,769.73
Helmets - $76,768.06

Query 2 and Query 3 return the correct results that I want (Top 5 Internet
Sales Amount for [Product SubCategory members in US) .

But Query 1 gets me the top 5 total of [Product].[Product Categories].
[SubCategory].Members despite the presence of the tuple ([Customer].[Customer
Geography].[Country].&[United States],[Measures].[Internet Sales Amount]).
Why is this happening? Any help is appreciated.

--
Message posted viahttp://www.sqlmonster.com


Your query gets top 5 products of United States and gives total sales
of that 5 Product.

Change it to

Select [Measures].[Internet Sales Amount] on 0,
TOPCOUNT([Product].[Product Categories].[SubCategory].Members, 5,
([Customer].[Customer Geography].[Country].&[United
States],[Measures].
[Internet Sales Amount])) on 1
from [Adventure Works]
where [Customer].[Customer Geography].[Country].&[United States]


Thanks and Regards
Amish Shah
http:\\shahamishm.tripod.com

.

Relevant Pages

  • TOPCOUNT giving different results
    ... Against Adventure Works cube in Adventure Works DW SSAS database ... [Internet Sales Amount])) on 1 ... Query 1 Results: ... Internet Sales Amount ...
    (microsoft.public.sqlserver.olap)
  • MDX question on combining result sets
    ... select [internet sales amount] on columns, ... from [adventure works] ... However, for each month, I want to "insert" a calculated member that calculates the 12 month moving average for all countries. ... However, I can't seem to fit that calculated member into the original query, in order to produce the revised result set. ...
    (microsoft.public.sqlserver.olap)
  • Re: MDX question on combining result sets
    ... from [adventure works] ... select [internet sales amount] on columns, ... August 2002 United Kingdom $64,033.43 ... However, I can't seem to fit that calculated member into the original query, in order to produce the revised result set. ...
    (microsoft.public.sqlserver.olap)
  • More effective Set to Sum function?
    ... MEMBER Measures.CustTotal AS Sum({.[Customer ... {CustTotal, [Internet Sales Amount]} ... FROM [Adventure Works]; ...
    (microsoft.public.sqlserver.olap)
  • Re: MDX in RS calculated member
    ... Source Currency ... Whereby the contents are of course the Top 5 "Internet Sales Amount" ... I would rather store the query in a calculated member in the cube - ... Or could defining a "Named Set" in the cube help out? ...
    (microsoft.public.sqlserver.olap)