How to get top 2 count record and rest total(others) using two dimension in same mdx

Question

i have this query, i need to implement for two dimension

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]

I try but it is not working for two dimension

WITH 
SET FIPS as
  [Geography].[State-Province].[State-Province]
//TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
SET [TCat] AS 
  Generate( {FIPS}, CrossJoin( {[Geography].[State-Province].CurrentMember}, topsum( ([Product].[Subcategory].[Subcategory]), 2, [Measures].[Sales Amount] ) ))   
MEMBER [Product].[Subcategory].[Other] AS 
  Aggregate(Except( [Product].[Subcategory].[Subcategory].Members, TCat))
SELECT 
  { [Measures].[Sales Amount] } ON COLUMNS, 
  Union( 
     TCat , {[Geography].[State-Province].[State-Province],[Product].[Subcategory].[Other]} ) ON ROWS 
FROM [Adventure Works];

Guessing, TCat + { [Other] } ON ROWS – ic3 Aug 11 '14 at 15:01 — ic3, Aug 11 '14 at 15:01

score 0 · Answer 1 · answered Aug 11 '14 at 18:13

0

Try using Except.

WITH
SET [TCat] AS
TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
MEMBER [Product].[Subcategory].[Other] AS
Aggregate(Except( [Product].[Subcategory].[Subcategory].Members, TCat)

SELECT { [Measures].[Sales Amount] } ON COLUMNS,
Union( TCat , {[Product].[Subcategory].[Other]} ) ON ROWS
FROM [Adventure Works]

answered Aug 11 '14 at 18:13

nsousa

4,448
1
10
15

it is not working for two dimension, in this query you are using only single dimension product – user3843858 Aug 12 '14 at 11:28
In that case you will need to do a cross join between the two dimensions. The same principle applies, but beware of large cardinality of sets. – nsousa Aug 12 '14 at 18:31
Hi,Any body can help me on this – user3843858 Aug 13 '14 at 03:56
we think this is limitation of product SSAS – user3843858 Aug 19 '14 at 11:57

How to get top 2 count record and rest total(others) using two dimension in same mdx

1 Answers1