GETPIVOTDATA from OLAP cube

J

jaroscak

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab
 
G

Guest

The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.
 
J

jaroscak

Thanks Miguel!

Your advice works, indeed.
I was starting to think that there is no way out, so I actually filled
the whole database pointing at the pivot table manually.

The only drawback I just found is that for cascaded dates, the
GETPIVOTDATA generates something like "[Date of Sales]","[Date of
Sales].[All Date of Sales].[2006].[Quarter 1].[January]", while in the
related excel database, I have separate rows for these three dimensions
(Year, Quarter, Month).
I believe there probably is a simple way how to concatenate these three
rows into one cell in a auxilliary row, so if you know how, please let
me know.

Good evening
Pab


Miguel said:
The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab
 
G

Guest

If you have them writen exactly as they are in the cube, you can concatenate
them in a row, something like:
="[Date of Sales].[All Date of Sales].[" & A1 & "].[" & A2 & "].[" & A3 &"]"
Where the year is in A1, the quarter in A2 and the month in A3.

Hope this helps,
Miguel.

Thanks Miguel!

Your advice works, indeed.
I was starting to think that there is no way out, so I actually filled
the whole database pointing at the pivot table manually.

The only drawback I just found is that for cascaded dates, the
GETPIVOTDATA generates something like "[Date of Sales]","[Date of
Sales].[All Date of Sales].[2006].[Quarter 1].[January]", while in the
related excel database, I have separate rows for these three dimensions
(Year, Quarter, Month).
I believe there probably is a simple way how to concatenate these three
rows into one cell in a auxilliary row, so if you know how, please let
me know.

Good evening
Pab


Miguel said:
The multiple references is something that you can not avoid, you will have as
many references as dimensions you are looking in the pivot table.
In order to use cell references, the contents of the cell must have the same
format as the one you see on the GETPIVOTDATA function. That is, if you want
to refer the product on cell A14 the contents of cell A14 should be:
[Group].[All Group].[Product A]
And then the formula can be writen as:
=GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]",A14)

I had to make one of these tables, and because the contents of the cell are
not too visually atractive I created an auxiliary table on other worksheet,
relating the caption that I wanted to show with the value that the formula
needed. Then I used VLOOKUP in the report worksheet, hiding the columns and
rows where the data for the formula was.

Hope this helps,
Miguel.

Hi there

I am trying to figure out how to change the absolute reference created
automatically by GETPIVOTDATA to a cell reference.

E.g. =GETPIVOTDATA("[Measures].[Sum]",OLAP!$A$3,"[Group]","[Group].[All
Group].[Product A]"

I need to change the .[Product A] part to some cell reference like A14
to be able to copy down the whole formula.
Moreover the GETPIVOTDATA usually consist of multiple references which
get created automatically and it is a nightmare to copy them.

Any help is appreciated.

Thanks, Pab
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top