Monday, December 03, 2007

Call BI Publisher report from APEX

After my post about the integration of APEX and BI Publisher I got some questions about further integration.

Also during my presentation "Advanced PDF documents from Oracle Application Express using Oracle Business Intelligence Publisher" at Oracle Open World I got some questions about security.

If you didn't see my presentation yet, you can download it here or you can see me presenting this week at UKOUG or in London in March 2008 during the European APEX Training Days.

When Daniel sent me a mail about his thread in the Application Express Forum I decided to post some more about BIP and APEX integration.

What we want to do: link a report made in BI Publisher to a button in APEX. This need to be as automated as possible...

Why do we want to do this as APEX is providing PDF printing out-of-the-box?

The answer is that APEX is limited in printing and BI Publisher is specialized in this area. In a next version of APEX (3.1 and 4.0) the printing is enhanced, but today that's not yet the case.
If you want to include multiple queries in your APEX report or print/export it as a ppt for ex. or even just fax a report, APEX doesn't provide this functionality declarative, but BI Publisher (BIP) does.

I suppose you know how to create a report in BIP, so I won't go into detail about that. In a previous post I already explained how you can install BIP (for Windows and Linux) and link it to APEX, so that's something you won't find here.

If you don't have a BIP report yet, you find some example/demo reports when logging in into BI Publisher (for ex. an url like: http://your-server:9704/xmlpserver).
An example of a report, go to: Home (Reports) > Shared Folders > Supply Chain Management > Warehouse Inventory Report

If you call that report you need to be authenticated (known by BIP) to get the result. That's something we want to avoid as we don't want to login in BIP again when coming from APEX...

To avoid you need to login again, go to: Admin > Security Center > Security Configuration

There're some different Security Models available to you (LDAP, BIP, DB). If you would use Single Sign-On, your live would be quiet simple as you don't have to do anything. But in this article I'm concentrating on the standard BI Publisher security.

To solve my logon issue, I'm making my reports public. In BIP this is called a "Guest".
In the Security Center of BIP I name my "Guest folder" (or public folder).

I'll need to create that Guest folder which will hold all the reports I'm calling from APEX.
Go to Reports > Shared Folders and create a new Folder "Guest". In that folder I created a new report (a copy of the example I showed above).

When you call that report by clicking on it, you'll see the result. You can adapt it to see it in a different output format. Then the most important part... Click on "Link to this report" and select" Document Only". A new window will pop-up on the same page with the link to the report.
Copy that url.


Now we'll use that url in our button in APEX. Create a new button and as Target select "Url" and copy the url you got from BIP in the "URL Target" field.

If you run your APEX application and click on the button you just created, you should see the report coming from BI Publisher...

37 comments:

  1. Very nice post! On a related note, I would love to know if APEX Web Services work with the newly exposed BI Publisher Web Services. In theory, this would allow you to call BIP via a web service which might get around some of the authentication issues. I believe they expose a lot more functionality than is exposed in the parameterized URL method. If you're bored...

    ReplyDelete
  2. Hi Dimitri,
    Thank for putting time into this matter. I will test your solution and hopefully use it until we have this functionality in APEX.

    Best regards,
    Daniel Borgström

    ReplyDelete
  3. Hi Tyler,

    When I've time ... ;-)

    Dimitri

    ReplyDelete
  4. Hi Dimitri
    Nice post, you can certainly use the new web services to run/schedule reports over on the BIP server.
    Cheers, Tim

    ReplyDelete
  5. Hi Dimitri,

    Thanks for your post.I tried the solution.I copied a sample report to Guest folder.But i don't find the below step to connect to Apex when i run the report...
    Then the most important part... Click on "Link to this report" and select" Document Only". A new window will pop-up on the same page with the link to the report.

    Regards
    Sambit Pattanaik

    ReplyDelete
  6. Hi Dimitri,
    your blog is very useful and interesting. I did what you said but i could not find the "link to this report". mine is not a licensed BIP. is there any way to access report from apex? can you give me the url pls?
    raj kumar, India

    ReplyDelete
  7. Excellent! I was waiting for a reply on this subject from Oracle, but - I'm still waiting. This works perfectly.

    The only comment I have is that you need to restart BI Publisher for the changes to take effect.

    Cheers,
    Daniel

    ReplyDelete
  8. i left a comment yesterday. but it is not found here. anyway thanks a lot. i am able to access BI report from apex. wonderful. i was just wondering what is the level of awareness of apex among users and developers. i am a free lancer using apex and find it wonderful.
    raj kumar india

    ReplyDelete
  9. Hi Raj,

    I think APEX is getting bigger and bigger.
    Especially now that APEX is an option of the database.

    Thanks,
    Dimitri

    ReplyDelete
  10. Hi Dimitri...

    Now this works for me...

    Thanks...
    Sambit Pattanaik

    ReplyDelete
  11. Hi Dimitri,
    Thank you for the post. I followed it step by step, but I couldn't find the link "Link to this report" at the up-right conner in the view report page. Do you know why? Thank you.

    BR,
    Shichao

    ReplyDelete
  12. hi

    I got one problem. I am not able go get "Enable Report Pringing" option in Print Attributes. Out of 10 different option I am getting only 3 options that are 1. Response Header 2. Contact Disposition 3. Print Server Overwrite.

    I don't understand one thing I have downloaded "md_pdf_howto_installer" sample application and in that I m getting all 10 options but in the sample application provided by apex I m not getting that option.

    do I need to make any configuration or which option I need to set by which I can enable that options ???

    Is they anything that I am missing ??

    Please let me know.

    Thanks
    Harsh

    ReplyDelete
  13. Hi Dimitri

    Greetings from South Africa

    I am one of the older folk using apex. Have tried BI and thanks to you got it working. However how do i call the report with parameter and how do i make it open the report in a new window.

    Regards Jacques

    ReplyDelete
  14. IF I RUN REPORT WITH LOV FROM APEX VIA URL IS POSSIBLE

    ReplyDelete
  15. thank you
    but i have problem with Link to this report .it's not work .it's show me

    Webpage error details

    Message: 'reportLinksMenu' is undefined
    Line: 1
    Char: 1
    Code: 0
    URI: http://maram-pc:9704/xmlpserver/~administrator/Bill/Bill/Bill.xdo

    how can i solve it ?

    ReplyDelete
  16. Hi Dimitri,

    I have a parameter in my BI report.
    How to pass value to the parameter when calling this report from APEX.

    Thanks
    Krishna

    ReplyDelete
  17. Hi Dimitri,

    I need to populate values from apex page into bi publisher pdf template. I tried to map the xml data from the report queries with the pdf form fields. When i am trying to call the report from apex, the values are not populated. Can u tell me how to achieve that?

    Thanks

    ReplyDelete
  18. To pass session state information of items to BI Publisher do following:
    - Go to Shared Components - Report Queries
    - Select your report query and make sure you checked Session State: "Include application and session information".
    - Add all the page items/application items to the box

    That will add the page items to the xml that gets produced, so you can use it in your Report Layout (select in Word when you load the xml through the BI Publisher plugin)

    Hope that helps,
    Dimitri

    ReplyDelete
  19. I have a pdf document not word document, is it possible to insert the fields in pdf form which is in bi publisher.

    ReplyDelete
  20. Hi Dimitri

    I am trying to call a report in bi publisher from apex page, i created the report under guest folder. When i am trying to call the report it is asking for login credentials of bi publisher. Can u pls help me how to skip the login page so that users can directly view the report.

    ReplyDelete
  21. You can make it a public report in BI Publisher.
    It won't ask for the login credentials and everybody can just call it.

    ReplyDelete
  22. I created the folder named guest in shared folders, as u explained in the blog. Still its asking me the log in credentials. How to make it my report to be public? Do i need to follow or change any properties of the folder in bi publisher?Pls Let me know.

    ReplyDelete
  23. Hi Dimitri,

    Finally i figured it out, where i am going wrong. now everything is working fine. Thanks for the help

    ReplyDelete
  24. Nice post!
    Have you checked the licensing costs?
    $40,000,- per processor!!

    Joep

    ReplyDelete
  25. Hi Dimitri,

    thx for that article. We use the method with report queries in combination with a rtf-report-template (built with Bi Publisher Desktop). The application primary language is German and the application language is derived from the browser.
    Everything is fine, except the localisation format of numbers and the calculation of sums in the rtf report if the browser language is set to German.

    If we change the Browser language into English, everything is o.k., and the report shows the correct German number-format and the sums are even calculated (the BI Publisher is translating correct).

    The problem:
    The browser-settings have to be set to German, so the xml which is transferred to BI Publisher is in German number format (e.g. 5000,58) and the generated report can not calculate the sum (NaN) and we think the number is transferred into text (the sum is not calculated) and not a number and not shown in German numberformat (5.000,58).

    Is there a chance to fix the output of the generated xml to English number format without fixing the language (English) of the application translation (Buttons, Numberformat in Interactive Report etc.) which should be furthermore derived from browser.

    Perhaps you got an idea to fix our problem.

    Best regards.

    Kasi.

    ReplyDelete
  26. Maybe you can pass the format you need it for?

    In the BIP report you can specify in your template, for example



    To apply this decimal format, use something like:


    There might be other options, you can check the BIP and/or APEX Forum what others have to say about it.

    Hope that helps,
    Dimitri

    ReplyDelete
  27. Hello Dimitri,

    thanks for your reply.

    But we didn't catch up what to do ..., there are no examples to see in the blog.
    If it's ok send it to me via eMail: kaspar@convis.com

    Thanks.

    Best Regards.
    Kasi.

    ReplyDelete
  28. Hi,
    how we pass parameter value llke challan no ?

    thanks
    Khaleque

    ReplyDelete
  29. Hi Dimitri.
    I've a question on report localization.
    Is it possible setting up the locale of the report using an additional HTTP GET parameter in the url?

    Thanks in advance,
    Samuele

    ReplyDelete
  30. hi all

    i want call report bipublisher with apex item parameters
    for example :
    http://nepaco-server:9704/xmlpserver/Guest/Personel/Personel.xdo?_xpf=&_xpt=0&_xdo=%2FGuest%2FPersonel%2FPersonel.xdo&IN_USER_ID=:P1_IN_USER_ID&_xt=Personel&_xf=html&_xmode=4

    help me pleas

    ReplyDelete
  31. Helo,

    I also want the same thing as in my URL, i need to pass the apex item p1_var1.

    I tried passing like :p1_var1 or &:var1 or &var1 or 'var1' or ':var1' nothing works.

    Please help me and let me know, how to pass the apex page item. Thanks

    ReplyDelete
  32. hi please i need to call several bi publisher reports from apex dynamically, such that i can pass the report name because i dont want it hard coded and so that i can also pass parameters to this reports too. please its terribly urgent. tanks

    ReplyDelete
  33. This comment has been removed by the author.

    ReplyDelete
  34. hello dimitri,
    i removed the earlier post due to the fact that i want to rephrase it.

    i was able to follow the step stated in the article.

    what of a situation where i want to have multiple reports but are of different categories.I mean having the reports in different folders and place them all in Guest folder in order to avoid the login screen again. Is it possible?

    hope you understand my question?

    ReplyDelete
  35. Hi Dimitri,
    Thank u for a great post to run BI publisher pdf,excel etc. report from APEX. It helps for configuring and run report from apex.

    Best Regards

    MD. Kaium Hossain

    ReplyDelete
  36. Hi Dimitri,
    i found this message that i have this problem too,
    -----------------------------
    Anonymous Anonymous said...
    I created the folder named guest in shared folders, as u explained in the blog. Still its asking me the log in credentials. How to make it my report to be public? Do i need to follow or change any properties of the folder in bi publisher?Pls Let me know.

    1/03/2011 3:20 AM
    -----------------------------
    how can i fix this problem please help me.

    Best Regards
    moo+

    ReplyDelete
  37. Hi moo+,

    I'm not using BI Publisher anymore for my reporting in APEX.
    We switched a few years back to APEX Office Print (AOP).
    I would recommend looking into it at https://www.apexofficeprint.com
    AOP is much more integrated with APEX than any other solution out there, much more pleasant to work with and easy to get started.

    Dimitri

    ReplyDelete