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.

=CUBESET("Cube"; "[Item].[Item].children"; "Top Products"; 2; "[Measures].[Sales]")

=CUBERANKEDMEMBER("Cube"; $B$90; ROW(A1)) // B90 contains the CUBESET, ROW(A1) allows me to autofill the rank.

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:

=CUBESET("Cube"; C49; "Top Products")

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

TOPCOUNT(([Store].[Store].[XX],[Date].[Month].&[XX],[Item].[Division].&[XX],EXCEPT([Item].[Item].CHILDREN,{[Item].[Item].&[XX], [Item].[Item].&[XX]})),30,[Measures].[Sales])

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