Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Friday, August 3, 2012

Creating Reports Using Oracle BI Publisher & APEX integration

I had recently explored the way to use BI publisher for creating reports and intgrating it with any APEX application regions.

(a) APEX - BI Publisher Integration Requirements
================================
1) Download & Install Oracle BI publisher(trial version of course) from OTN, It needs a license.




2) Download & Install Oracle BI publisher desktop again from OTN. (After this install you can see an additional BI pulisher plugin in MS word).



3) You need to start the BI pulisher using the start icon that is created on ur desktop after step-2.
(BI Publisher will be hosted by weblogic server..You will notice this when you start it)



Now you are ready to create reports in various formats, like PDF, Excel, HTML, RTF etc....

(b) Creating Reports and Integrating it with APEX Applications
===========================================
1) Login to your APEX workspace, and then click on the application to which reports are to be added.



2) Click on the page with your reporting regions.

3) Now select the region for which you intend to have a report download in PDF format.



4) The region source query needs to be copied to a notepad

5) Now again go back to the applications and select shared components.



6) Under shared components, click on report queries and create a report query with the query you saved in step-4

7)While creating the report query, download the XML output of the report query and save it, Further, it asks for the report layout and the source for it. Pause here the activity. (We are continuing to create a RTF template for our report using MS word)

8) Now open MS Word, Under BI Publisher tab. Click on Sample XML, and select your sample XML you saved on step-7, The XML data will be loaded into the word.





9) Now using table wizard, design your report layout as you require.


10) After creating the report table, you can preview the report(of course with actual data), in various formats.

11) Save the word report layout created as turnover.rtf.

12) Again go back to step-7, Under shared components page of the application, select the turnover.rtf file created in step-11 as the report layout and click finish for creating the report queries. (Save the report URL soemthing like below)



13) Now, goto the page definition of with the reports region and create a button and a branch as shown below.(Branch should be to the Print URL as shown above)
14) Now, you are all set to test your report, run your reports page in your application, and just click your button to view your repor tin PDF format.

HAPPY LEARNING!


1 comment:

  1. Thanks, This blog is really helpful when struck with an Oracle issue.

    ReplyDelete

Thanks for you valuable comments !