Business Intelligence – Oracle

Archive for January 28th, 2009

Oracle BI EE 10.1.3.4 – Integration between BI EE & Hyperion Financial Reporting(HFR) – Drilling from BI EE to HFR by passing parameters – Part 1

Posted by Venkatakrishnan J on January 28, 2009

In the prior blog entry, i had given an overview of how to go about achieving integration between any two reporting toolsets. As a followup to that, through a series of articles, i would show the integration between the following toolsets

1. BI EE and Hyperion Financial Reporting
2. Hyperion Financial Reporting and Hyperion Web Analysis
3. BI EE and Hyperion Web Analysis

Today we shall see how to integrate BI EE and HFR. The integration between BI EE 10.1.3.4 and HFR 11.1.1 would be seamless in terms of security as SSO is now available between these 2 products. So, our aim today is to drill from a BI EE report to an HFR report by passing parameters from BI EE to HFR. So, lets start with a simple HFR report as shown below.

As you see, this is a very simple report showing the Total Sales for a Year and the corresponding products. Now, assume that the sales transaction details are reported through BI EE. For example, consider the BI EE report shown below.

Now, our aim is to enable drill on the Month column of the BI EE report i.e when anyone clicks on, say the Month January, the HFR report should also provide the data for January. In order to achieve this, we need to understand how HFR accepts parameters in the URL. To know that, go to any data cell in HFR and click on related content.

In the related content, just include the same report as the related content report.

Then click on properties. You would get the below URL.

http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&run=1&sso_token=$SSO_TOKEN$&$CONTEXT$&rcp_version=$RCP_VERSION$&rcp_alias=$RCP_ALIAS$&mimetype=application/hyperion-reports-report

As you see, in the above URL there are certain dollar prefixed and suffixed names that pass the values from the base report to the target report. In this case $CONTEXT$ is the actual keyword which passes the actual parameter POV values to the target report. In order to see the actual value of the CONTEXT parameter, lets go back to the related content again and enter the below link instead of the above report

http://localhost?$CONTEXT$

Now, lets click on the URL from the HFR report and see what happens to the target URL.

http://localhost/?attribute=Product.id.Product&attribute=Year.id.Year&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario

As you see, all the POV values are passed to the target report in the form of URL parameters. The parameter is of the form

attribute=Market.id.Market

i.e we can pass dynamic parameters to a HFR report by passing the parameters in the form shown below

attribute=<dimension>.id.<dimension value>

Now, lets test this out using the below URL.

http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&attribute=Product.id.Product&attribute=Year.id.Jan&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario

As you see, in the above URL we are passing Jan as a POV to the year dimension.

Now, once we are sure that the parameters are getting passed to the HFR report, lets go to the BI EE report that we created earlier. In the column formula, enter the below formula

'<a href="http://localhost:19000/workspace/index.jsp?module=tools.relatedcontent&repository_path=/Drill%20Through%20Report&elementType=2&repository_name=Drill%20Through%20Report&repository_format_id=html&attribute=Product.id.Product&attribute=Year.id.'||LEFT(TIMES.FISCAL_MONTH_NAME, 3)||'&attribute=Market.id.Market&attribute=Accounts.id.Accounts&attribute=Scenario.id.Scenario">'||TIMES.FISCAL_MONTH_NAME||'</a>'

Now, convert this column format to HTML. We would get the below report with the drills enabled for all the months.

Now, if we drill on any month, it would take us the HFR report. But after that you should see the HFR report for the corresponding month.

The above uses the approach 1 that i had blogged about here. Next, lets look at how we can pass dynamic parameters from HFR to BI EE.

Advertisements

Posted in All Posts, EPM, Hyperion Financial Reporting, OBI EE Plus | 7 Comments »