CUBESET with multiple dimensions

Hey folks!

I'm an intermediate excel user who's very new to OLAP cubes. I'm having a bit of trouble figuring out how the CUBESET function works and was hoping you beautiful people could give me a hand.

The cube I'm working with has dimensions for Items ([Item].[Item].&[X], different products we sell), Stores ([Store].[Location Code].&[X], locations they're sold at) and Divisions ([Division].[Division ID].&[X], type of product). What I'm trying to do is create a list of Top 30 Items (by [Measures].[Sales]) of a certain Division for each Store. Ideally, I would also like to exclude certain Items from the results entirely.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The formulas above give me a list of Items (which I can use to retrieve sales data with CUBEVALUE) but doesn't consider Division or Store. Any tips would be greatly appreciated

Regards,
notski

It works! Here's the solution:

Please Login or Register  to view this content.

C49 contains the set_expression, which contains TopCount and Except functions.

Please Login or Register  to view this content.

I then use a CUBERANKEDMEMBER formula to retreive the list of items. One strange thing to note is that despite using a European version of Excel where semi-colons are used as formula separators, I had to use commas in the TopCount function.

Hope this helps anyone else in need!

Regards,
notski