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

    Domanda

  • 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


    lunedì 12 marzo 2012 03:54

Risposte

  • 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/

    martedì 13 marzo 2012 02:27
    Postatore

Tutte le risposte

  • 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/

    martedì 13 marzo 2012 02:27
    Postatore