+ Reply to Thread
Results 1 to 4 of 4

GETPIVOTDATA from OLAP cube

  1. #1

    GETPIVOTDATA from OLAP cube

    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


  2. #2
    Miguel Zapico
    Guest

    RE: GETPIVOTDATA from OLAP cube

    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.

    "[email protected]" wrote:

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


  3. #3

    Re: GETPIVOTDATA from OLAP cube

    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 Zapico wrote:
    > 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.
    >
    > "[email protected]" wrote:
    >
    > > 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
    > >
    > >



  4. #4
    Miguel Zapico
    Guest

    Re: GETPIVOTDATA from OLAP cube

    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.

    "[email protected]" wrote:

    > 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 Zapico wrote:
    > > 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.
    > >
    > > "[email protected]" wrote:
    > >
    > > > 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
    > > >
    > > >

    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1