Changing Report Filter without VBA / Returning dimensions dynamically with CUBE functions

Question

Hi,

I've got a 'SKU Dashboard' in my desktop PowerPivot file that works really well - you enter the SKU in a box, press enter and a little bit of VBA updates the pivot tables and refreshes the data. My problem is that I now want to deploy something similar to SharePoint where I will be unable to employ the VBA method.

I've done some research and discovered that using the CUBE formulas is the way forward and although I've got a fair bit of the thing working I;ve got on issue I can't solve: A bunch of metrics around the SKU are effectively other dimensions from the product table such as Supplier, Category etc. 

Using live pivots I simply bring these aspects in as dimensions however once I turn that pivot table to CUBE formulas using the option on the PivotTable tab, the dimensions 'below' product code are no longer dynamic and actually feature the name of the dimension within the formula.

e.g

=CUBEMEMBER("PowerPivot Data",{"[Product].[Department].&[Car & Travel]"})

In the above I need [Car & Travel] to be worked out automatically based on the productcode - maybe there's a measure I can write that solve this but at the moment I'm stumped!

Any one got any ideas?

Regards

Jacob

Answer 1

See:

http://www.powerpivotpro.com/2010/06/using-excel-cube-functions-with-powerpivot/

Regards,

David Hager

Answer 2

Hi Jacob

Suppose you have a slicer for Product "Color", and you want to show only the Products with "EnglishProductName" that belong to the color you selected.  To do this dynamically in a cube formula, you can use the CUBERANKEDMEMBER function:

=IFERROR(
	CUBERANKEDMEMBER("PowerPivot Data",
	"([DimProduct].[Color].[All].[" & 
		CUBERANKEDMEMBER("PowerPivot Data",Slicer_Color,1) & 
		"],[DimProduct].[EnglishProductName].Children)"
	,ROW(A1))
,"")

The innermost CUBERANKEDMEMBER selectes the current slicer selection and maps it to a color in the PowerPivot 'product' dimension.  Then, the outermost CUBERANKEDMEMBER constructs a set for that color crossjoined with all the products that have a value for the selected color.  Finally, using ROW(A1) you can copy the same formula for a few thousand rows to ensure you will have enough cells using the expression to accomodate any color selection

Hope that helps!

---

Javier Guillen
http://javierguillen.wordpress.com/