Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 a simple? MDX query returning top 10 and rest result Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 15, 2008 4:23 AM
 Old 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 'ProductThe 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.320Product 2 4.924...Product 10 1.923Rest 2.345So 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 Group: Moderators Last Login: Friday, November 29, 2013 9:22 AM Points: 1,779, Visits: 3,301
 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 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 Group: Moderators Last Login: Friday, November 29, 2013 9:22 AM Points: 1,779, Visits: 3,301
 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 0FROM [some_cube] Steve.
Post #485689
 Posted Wednesday, April 16, 2008 8:30 AM
 Old 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 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! Greetings...
Post #1110089
 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:WITHSET [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 ROWSFROM [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 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

 Permissions