Apply ORDER() to set returned by GENERATE()?

#1

Reading a tutorial on usage of GENERATE() function, this example against
Foodmart 2000 DB shows top 5 selling products with respective sales total
from each state.

I want to enhance the query to order by State, then by Sales (DESC). So
first five items would be all for CA in order (407.76, 325.27, 323.95,
306.99, 301.77)

SELECT
{[Measures].[Warehouse Sales]} ON COLUMNS,
GENERATE([All Stores].[USA].Children, CROSSJOIN({[Store].CurrentMember},
TOPCOUNT( [Product].[Product Name].Members, 5, [Store].CurrentMember)
)) ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1997])

#2

This version returns the CA items in the desired order:

Quote:

SELECT

{[Measures].[Warehouse Sales]} ON COLUMNS,
GENERATE([All Stores].[USA].Children,
CROSSJOIN({[Store].CurrentMember},
TOPCOUNT([Product].[Product Name].Members, 5,
[Measures].[Warehouse Sales])
)) ON ROWS
FROM [Warehouse]
WHERE ([Time].[Year].[1997])

Quote:

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apply ORDER() to set returned by GENERATE()?

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Apply ORDER() to set returned by GENERATE()? - 03-22-2005 , 05:33 PM

Re: Apply ORDER() to set returned by GENERATE()? - 03-22-2005 , 06:33 PM