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