a simple? MDX query returning top 10 and rest result

Question

Post reply

a simple? MDX query returning top 10 and rest result

Franky Leeuwerck

SSCrazy

Points: 2036
More actions
April 15, 2008 at 4:23 am

#124865

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

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

HTH,

Steve.

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 2

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.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 3

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.

Franky Leeuwerck SSCrazy Points: 2036 More actions · Answer 4

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.

oertel SSC-Addicted Points: 493 More actions · Answer 5

Hi Franky,

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

You saved my day! :w00t:

Greetings...

labj500-1 Valued Member Points: 55 More actions · Answer 6

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

supriyarani.kr Valued Member Points: 55 More actions · Answer 7

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 ...