User Profile
Ed_at_Barilla
Copper Contributor
Joined Apr 04, 2019
User Widgets
Recent Discussions
Re: OLAP Get Pivot Data function vs. Non-OLAP Pivot tabel
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.1.7KViews0likes1CommentRe: OLAP Get Pivot Data function vs. Non-OLAP Pivot tabel
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 & "]")1.9KViews0likes3CommentsOLAP Get Pivot Data function vs. Non-OLAP Pivot tabel
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.2KViews0likes5Comments
Recent Blog Articles
No content to show