MDX Query for 'Collapsing' Set

[DanB]

I have a need for a unique query and wanted to see if anyone had any ideas.

I wish to show a chart of products by annual sales. The list of products can be quite long so I would use topcount or toppercent or topsum to limit the list. But then I would like to show the remainder of the products as a member 'Other' which doesn't exist in the cube. So, in effect the top 15 products would be shown in detail in the graph but then the other 250 products would be collapsed and aggregated into an 'Other' member for the set.

I posted this in MDX and not Reporting Services since it is more a question related to member manipulation and not how to construct the graph.

[Island1]

A calculated member for the "top x," and another for the "reciprocal," might be a quick answer, but if there is a possibility that you would be likely to want to do more (say, parameterize the "top" number, etc., in Reporting Services or another reporting mechanism), you might want to consider other options.

Get back on specifics if you want to discuss beyond the simpler alternative. I'll be happy to offer suggestions based upon my own experiences.

Good Luck!

Bill

[DanB]

Ah, I believe your'e thinking that I'm wanting to aggregate members based on selected metrics? I'm really referring to manipulating members of the product dimension while the associated metrics remain the same. I'll try to give an example:

Top 3 Product Sales (of 10 products total)

NAME SALES
widget1 100
widget2 90
widget3 80
other (widgets 4-10) 150

Returning the top three widgets in a set is straightforward by using topcount([Prod Dim].[Products].members,3,[measures].[Sales]). You can even aggregate the bottom 7 members of the set by Sales easy enough. But how would one best collapse a set (of the bottom 7 members) into a single member called 'other' that doesn't exist in the cube?

I was trying to think of different approaches such as adding a null 'other' member to the product dimension and then aggregating the bottom 7 but I can't apply that value to the 'other' member. Ideas?

[DanB]

Well, I solved this but had to revert(?) to t-sql to accomplish what I wantd. I created a select query returning the top 3 products and their sums grouped by product and then union another query which uses a subquery to group by an 'Other' Product and exclude the products from the top 3. Something like this from the AdventureWorks db:

select x.prodsum,x.prodname
from (select top 3 sum(a.unitprice) as prodsum,b.name as prodname
from Sales.SalesOrderDetail a inner join Production.Product b on a.ProductID = b.ProductID
group by b.name
order by sum(a.unitprice) desc ) x

union

select sum(a.unitprice) as prodsum,'Other' as prodname
from Sales.SalesOrderDetail a inner join Production.Product b on a.ProductID = b.ProductID
where b.name not in
(select x.prodname from
(select top 3 sum(a.unitprice) as prodsum,b.name as prodname
from Sales.SalesOrderDetail a inner join Production.Product b on a.ProductID = b.ProductID
group by b.name
order by sum(a.unitprice) desc) x)

It's a little curious that there isn't a way to combine members in MDX. Anyway, thanks for your time.