This discussion is archived
5 Replies Latest reply: Feb 8, 2013 4:21 PM by Andy1785 RSS

Combine data from two grid to one

Andy1785 Newbie
Currently Being Moderated
Hi All,

I am new to Hyperion Financial Reporting. I nned some help with a report that I am trying to generate. Following are the details :

Grid 1 : ( Built from Essbase 11.1.1.3)

Account Type, Amount

101, 123,000
102, 145,650
103, 100

Grid 2: (Built from HFM 11.1.1.3)

Account Type, Amount

101, 123,000
102, 145,000
104, 200

Above two grids are ready but i want to achieve the following :

Grid 3 (Main)

Account Type, Grid 1 Amount, Grid 2 Amount, Difference

101, 123,000 , 123,000, *0*
102, 145,650 , 145,000, *650 *
*103*, 100 , , *100*
*104,* , 200 , *200*

Also if we can somehow achieve this in grid 1 or 2 , that is also fine. Also if needed we can hide Grid 1 and 2. Please advice if this is do able or not.

Any help/suggestion is appreciated.

Thanks.
  • 1. Re: Combine data from two grid to one
    Stuart Game Journeyer
    Currently Being Moderated
    Hi

    There are several ways to do this and the best approach will depend upon the answer to a few questions:

    1. Does your report contain fixed members or are you using functions that return a variable number of members, e.g. Children / Descendants?
    2. Is the dimensionality the same between the two data sources?


    Option 1:- Best if answer to qn 1 is 'Fixed'
    Create two grids, one connected to Essbase the other to HFM.
    Use formula rows / column to pull the data from one grid to the other, this can be done by entering syntax such as Grid1.[A,1] in the formula

    This is quite easy providing you don't have variable numbers of rows and columns returned and that the number of cells is relatively limited (creating and maintaing a lot of formulae is time consuming!!). You may be able to speed it up if you always want the value for the correspnding row of column A by using Grid1.[A], this works within a grid but you would need to test it across grids. Also be careful, what if for some reason the number of rows / columns does not line up, this isn;t like an Excel lookup that finds the same instance of an Account.


    Option 2:- Best if the answer to qn 1 is 'Variable' and qn 2 is 'Yes'
    You can select row(s) or column(s) within a grid and change the database connection for that row / column.
    This way you just make your selections as normal but it is selecting from the different data source.

    This is best if your dimensionality is the same or at least very similar, e.g. you have the same dimensions or preferably even the same / similar members. However you can, if my memory is correct specifiy a member to use on dimensions that may be in the new source but not the one assigned to the overall grid. make sure you test that it all works if you are using POV, e.g. do you have to select a member from each data source, that can be a pain.


    It sounds as though the report you are trying to produce is a sort of reconciliation between HFM and Essbase? if that is the case then option 2 sounds like it would work best for you.

    Hope this helps and let us know if you need any more help
    Stuart
  • 2. Re: Combine data from two grid to one
    Andy1785 Newbie
    Currently Being Moderated
    Hi Stuart,

    Thanks a lot for such detailed explanation. Yes you are right , we are trying to build a reconciliation report between HFM and Essbase.
    The only issue now is the essbase dimension which we want to pull has "IC_" appended to the membername (eg. "IC_145") whereas HFM has only 145 as membername.
    Is there anything that I can do or that ?

    Please advice. Highly appreciate your help and time
  • 3. Re: Combine data from two grid to one
    Stuart Game Journeyer
    Currently Being Moderated
    Hi

    I assume if you are reconciling HFM to Essbase that the dimension structures are the same but in Essbase Intercompany is prefixed "IC_" so you have unique member names?

    If the sturcture is the same then are you able to select descendants of "EntX" from the HFM source and descendants "IC_EntX" from the Essbase source? I've not tried it to know whether it would work I'm afraid. Or do you need to be at that level, could you structure your report such that you are only using [ICP Top] and the equivalent in Essbase, then potentially you could set that in the POV for each source?

    Regards
    Stuart
  • 4. Re: Combine data from two grid to one
    Andy1785 Newbie
    Currently Being Moderated
    Hi Stuart,

    Thanks again. Let me try to implement that and update this post .
  • 5. Re: Combine data from two grid to one
    Andy1785 Newbie
    Currently Being Moderated
    Completed the report using two grids.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points