Forum Discussion
OLAP Get Pivot Data function vs. Non-OLAP Pivot tabel
In general it works the same way, for example within cube formula instead of
,"[3. Customer].[Distribution Channel Desc]",you may use
,"[3. Customer].[" & B12 & "]",Another story with cube it's usually better to use combination of CUBEMEMBER/CUBEVALUE instead of GETPIVOTDATA
- Does the last bit at the end look correct because I am still getting a #REF.
=GETPIVOTDATA("[Measures].[Previous Day's Order Volume Tons]",$A$1,"[3. Customer].[Sales Organization Desc]","[3. Customer].[Sales Organization Desc].&[Barilla USA FG sales]","[3. Customer].[Distribution Channel Desc]","[3. Customer].[Distribution Channel Desc].&[Modern Trade]","[2. Product].[International Category Desc]","[2. Product].[International Category Desc].&[DRY PASTA]","[2. Product].[Category Desc]","[2. Product].[Category Desc].&[DRY SEMOLINA PASTA]","[2. Product].[Sub brand Desc]","[2. Product].[Sub brand Desc].[" & M150 & "]")In general formula shall work, here is the sample
You exact formula is
=GETPIVOTDATA( "[Measures].[Previous Day's Order Volume Tons]", $A$1, "[3. Customer].[Sales Organization Desc]", "[3. Customer].[Sales Organization Desc].&[Barilla USA FG sales]", "[3. Customer].[Distribution Channel Desc]", "[3. Customer].[Distribution Channel Desc].&[Modern Trade]", "[2. Product].[International Category Desc]", "[2. Product].[International Category Desc].&[DRY PASTA]", "[2. Product].[Category Desc]", "[2. Product].[Category Desc].&[DRY SEMOLINA PASTA]", "[2. Product].[Sub brand Desc]", "[2. Product].[Sub brand Desc].[" & M150 & "]" )it could give #REF! error if the value in M150 is not recognized for the cube.
You have been very patient so far but I have to profess my ignorance in your last statement when you say that the cell reference may not be "recognized for the cube". I started the formula by typing an = sign in a cell outside the pivot table that was created from an OLAP connection and then letting Excel build the formula. Then I was trying to edit that formula with the syntax you showed. Not sure what I am doing wrong. I just tried refreshing the cube again by running the OLAP query but no change - still #REF.
