Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

a simple? MDX query returning top 10 and rest result Expand / Collapse
Author
Message
Posted Tuesday, April 15, 2008 4:23 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 1:13 AM
Points: 350, Visits: 474
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
Posted Tuesday, April 15, 2008 1:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Sunday, April 13, 2014 11:43 PM
Points: 1,781, Visits: 3,340
You might find some useful examples in the walkthroughs from Bill P (here), look for articles 31 & 32.

HTH,




Steve.
Post #485272
Posted Wednesday, April 16, 2008 6:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 1:13 AM
Points: 350, Visits: 474
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
Posted Wednesday, April 16, 2008 8:22 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Sunday, April 13, 2014 11:43 PM
Points: 1,781, Visits: 3,340
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
Posted Wednesday, April 16, 2008 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 1:13 AM
Points: 350, Visits: 474
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
Posted Tuesday, May 17, 2011 5:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, January 23, 2014 1:59 AM
Points: 52, Visits: 80
Hi Franky,

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

You saved my day!

Greetings...
Post #1110089
Posted Monday, February 06, 2012 2:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, February 14, 2013 12:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse