OLAP Get Pivot Data function vs. Non-OLAP Pivot tabel

[Ed_at_Barilla]

Due to change in our Business Intelligence system I have to pull data into Excel files using OLAP.  When I use the GetPivotData function the formulas are much more complicated.  Here is an example of the new formula.  If you are using OLAP pivots I am sure you will recognize how it is built.

=GETPIVOTDATA("[Measures].[Previous Day's Order Volume Tons]",Pivot!$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].&[Whole Wheat]")

In the old way I was able to us the GetPivotData function to dynamically select data from the table.  By dynamically I mean I could use a cell reference in the function to select the data.  Below is an example.  For instance, getting data for "Sales Organization" the reference is cell A12 where I store the name of the Sales Organization.  I have been unsuccessful in trying to get the new OLAP GetPivotData function to recognize cell references as variables.  HELP!!

=GETPIVOTDATA("Previous Day's Order Volume [Tons]",Pivot!$A$1,"Sales Organization",A12,"Distribution Channel",B12,"International Category",C12,"Category",$D12,"Subbrand",$E12)

[#2]

@Ed_at_Barilla 

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

[#3]

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 & "]")

[#4]

@Ed_at_Barilla 

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.

[#5]

@SergeiBaklan 

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.

[#6]

@Ed_at_Barilla 

It's hard to say what exactly is wrong without the file. For example, in the cell it could be "Lights " instead of "Lights".

Try to play with attached file, that's the sample from my previous post.