a simple? MDX query returning top 10 and rest result

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » a simple? MDX query returning top 10 and rest...

a simple? MDX query returning top 10 and rest result

Rate Topic

Display Mode

Topic Options

Author

Message

Franky Leeuwerck

Posted Tuesday, April 15, 2008 4:23 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:43 AM
Points: 349, Visits: 467

Hi,

I need a simple (if possible) MDX query on a cube.

My starscheme looks like this (simplified for this question ) :

- a Fact table 'Sales' with a measure 'Quantity'
- a Dimension table 'Product

The MDX query I need should list the top 10 products sold ( in terms of quantity ), completed with the aggregated
result for the rest of the products.

Example :

Product 1 5.320
Product 2 4.924
...
Product 10 1.923
Rest 2.345

So far, I have only used very simple MDX selects.

Thanks in advance,
Franky

Franky L.

Post #484856

stevefromOZ

Posted Tuesday, April 15, 2008 1:40 PM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

You might find some useful examples in the walkthroughs from Bill P (here), look for articles 31 & 32.

HTH,

Steve.

Post #485272

Franky Leeuwerck

Posted Wednesday, April 16, 2008 6:52 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:43 AM
Points: 349, Visits: 467

Hi Steve,

Thanks for the inputs.
A nice article indeed on the topcount() function.
This helps me to solve the first part of my question.

Any tips on the 2nd part.
How do I add to my top 10 results the aggregated result of the rest of the dimension members ?

Regards,
Franky

Franky L.

Post #485624

stevefromOZ

Posted Wednesday, April 16, 2008 8:22 AM

SSCommitted

Group: Moderators
Last Login: Friday, May 17, 2013 11:00 AM
Points: 1,763, Visits: 3,187

You could possibly utilize the Except function [EXCEPT( )]. You'd probably want to create a named set for the top 10 initially, then use that as the excluded set of items (using the except function) to create a calc member. So in rough pseudo code....

With
Set setTop10 AS
Member allElse AS SUM(EXCEPT( )
SELECT
{setTop10, allElse} on 1,
{[Measures].[Qty_sold]} on 0
FROM
[some_cube]

Steve.

Post #485689

Franky Leeuwerck

Posted Wednesday, April 16, 2008 8:30 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:43 AM
Points: 349, Visits: 467

Hi Steve,

In the meantime I found another syntactical solution on the web.
Here's an example.

WITH
SET [TCat] AS
TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
MEMBER [Product].[Subcategory].[Other] AS
Aggregate([Product].[Subcategory].[Subcategory] - TCat)
SELECT { [Measures].[Sales Amount] } ON COLUMNS,
TCat + [Other] ON ROWS
FROM [Adventure Works]
;

Franky L.

Post #485699

oertel

Posted Tuesday, May 17, 2011 5:28 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, May 23, 2012 1:50 PM
Points: 52, Visits: 78

Hi Franky,

thank you soooo much for posting your solution. I need exactly the same like you.

You saved my day! w00t

Greetings...

Post #1110089

labj500-1

Posted Monday, February 06, 2012 2:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 07, 2012 3:21 PM
Points: 1, Visits: 3

Thank you for this post.

I'm trying to add the percentage. This is what I have:

WITH
SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
MEMBER [Product].[Subcategory].[Other] AS Aggregate([Product].[Subcategory].[Subcategory] - TCat)
MEMBER [Measures].[Percent] AS [Measures].[Sales Amount] / ([Measures].[Sales Amount],[Product].[Subcategory].CurrentMember.Parent),format_string='0.00%'

SELECT { [Measures].[Sales Amount], [Measures].[Percent] } ON COLUMNS,
TCat + [Other] ON ROWS
FROM [Adventure Works]

It works fine, except for the [other].
Can somebody help here?

Thanks a lot,
lab

Post #1247703

supriyarani.kr

Posted Thursday, February 14, 2013 12:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:35 AM
Points: 1, Visits: 1

Hi,

The above query work for single dimension but not for two dimensions . i can able to order top 30 count but i am unable to add rest total ......mdx Query to get top 30 count

select {[Measures].[Sum]} on columns,
Generate({[V Airline].[Airline Ds].members},{TopCount(Order(HIERARCHIZE(crossjoin([V Airline].[Airline Ds].currentmember ,[v RPTS TRNS Visits].[DMA Ds].members)),[Measures].[Sum],BDESC),30,[Measures].[Sum])}) on rows from [Cube_ATA] where ([v RPTS TRNS Visits].[Country Cd].&[USA])

its retrieving top 30 records . Here we using two dimension Airline and DMA . So i couldn't able to get the Total of the rest. Please help

Thanks in Advance ...

Post #1419843

« Prev Topic | Next Topic »

Permissions