Tuesday, February 17, 2009

JasperReports in Apex

A lot of people complain that Oracle integrated the very expensive BI Publisher in Apex. Of course there are alternatives, like the open source JasperReports. But how do you use this?

Jasper is completely new to me, so I started with a simple example. What you need is:
  • Oracle Apex (of course)
  • JasperServer
  • iReport
JasperServer and iReport can de downloaded from the Jaspersoft website.

Basically, iReport is a client/server graphical tool to design a report, and JasperServer runs the report in a web environment.

I created the simplest report I could think of in iReport using the Report Wizard:

select *
from emp
where deptno = $P{deptNo}


Publish this report in JasperServer. As a Jasper newbie I was surprised that in JasperServer you have to define the report parameter (deptNo) again as a control. This is not taken from the report definition. That took me some time to figure out, but once that was done, integration with Apex was easy!

Now back to Apex. Here I created another simple report based on:

select
"EMPNO",
"ENAME",
"DEPTNO",
"DEPTNO" "DEPTNO2"
from "EMP"


The two deptno columns are used to link to the Jasper report. I will explain the difference between those two.

For the first deptno column I created a simple link to JasperServer. Edit the report attribute so that it will become a link to a URL:




The URL is:

http://server:port/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/Apex/deptemp&output=pdf&deptNo=#DEPTNO#&j_username=un&j_password=pw


Now, as you can see this is not a very secure option. The username and password are exposed in the URL. Another option is to call the report from the database using the utl_http package. This is the procedure:


CREATE OR REPLACE procedure runJasperReport(i_deptno   in varchar2)
is
vReportURL varchar2(255);
vBlobRef blob;
vRequest Utl_Http.req;
vResponse Utl_Http.resp;
vData raw(32767);
begin
----------------------------------------------------------------------------
-- build URL to call the report
----------------------------------------------------------------------------
vReportURL := 'http://host:port/jasperserver/flow.html?_flowId=viewReportFlow'||
'&reportUnit=/reports/Apex/deptemp'||
'&output=pdf'||
'&j_username=un&j_password=pw'||
'&deptNo='||i_deptno;
----------------------------------------------------------------------------
-- get the blob reference
----------------------------------------------------------------------------
insert into demo_pdf (pdf_report)
values( empty_blob() )
returning pdf_report into vBlobRef;
----------------------------------------------------------------------------
-- Get the pdf file from JasperServer by simulating a report call from the browser
----------------------------------------------------------------------------
vRequest := Utl_Http.begin_request(vReportUrl);
Utl_Http.set_header(vRequest, 'User-Agent', 'Mozilla/4.0');
vResponse := Utl_Http.get_response(vRequest);
loop
begin
--------------------------------------------------------------------
-- read the next chunk of binary data
--------------------------------------------------------------------
Utl_Http.read_raw(vResponse, vData);
--------------------------------------------------------------------
-- append it to our blob for the pdf file
--------------------------------------------------------------------
Dbms_Lob.writeAppend
( lob_loc => vBlobRef
, amount => Utl_Raw.length(vData)
, buffer => vData
);
exception when utl_http.end_of_body then
exit; -- exit loop
end;
end loop;
Utl_Http.end_response(vResponse);

owa_util.mime_header('application/pdf',false);
htp.p('Content-length: ' || dbms_lob.getlength(vBlobRef));
owa_util.http_header_close;
wpg_docload.download_file(vBlobRef);

end runJasperReport;
/

To call this procedure you create a very simple On Demand Application Process:

runJasperReport(:deptno);

where :deptno is an Application Item.

Similar to the link in the first case, the URL is now:

f?p=&APP_ID.:&APP_PAGE_ID.:&APP_SESSION.:APPLICATION_PROCESS=runrep:::deptno:#DEPTNO2#

In a real application you will add extra parameters to the stored procedure, like logged in user. Make it re-usable by adding the report name and parameters as input parameters.

Added alternative

There is a third option to call a report. I think this is even better, since you have more authorisation control. On Demand Application Processes have the disadvantage that they do not have an authorisation control mechanism.

So, instead of calling an application process, you can call a page that has the same stored procedure as source.

Just create an empty page that calls the procedure in the On Load - Before Header process point.

In the report itself, change the link to call the page instead of the application process.

25 comments:

  1. Yess I'm the first to post a comment (beside Ino).

    Ino indeed a good idea to start this blog.
    And a new beginning maybe for our Oracle Apex workshop to start posting tips and tricks.
    Good luck!
    NatScorpio

    ReplyDelete
  2. Hello,

    Apart from the simple report you did in Jasper, have to further explored Jasper Reports? I am looking for info. on how easy/hard is a transition from Oracle Reports to Jasper. Any pointers would be appreciated.

    Thanks,

    Manish

    ReplyDelete
  3. Manish
    I looked into Jasper but did not use it any further than this. We are still using Reports a lot too.

    ReplyDelete
  4. Hi I'm trying to follow your example but I cant get the URL to work to call the report.
    I keep getting The page cannot be displayed errors, I can only get the report to display if I use the following:
    (Note, Im using a report without a parameter to simplify even further)

    http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/apex/deptemp&standAlone=true&ParentFolderUri=/reports/apex&j_username=un&j_password=pwd

    If I use the following I then get the page cannot be displayed error:

    http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow&reportUnit=/reports/apex/deptemp&output=pdf&j_username=un&j_password=pwd

    Its like it cant display the pdf because it doesnt exist yet, am I missing a setting somewhere?

    Thanks in advance

    ReplyDelete
  5. I can generate pdf output in firefox, it just doesnt work in IE 6 or 7.

    Did you manage to get it to work in IE?

    ReplyDelete
  6. That looks like a problem with you IE settings. Can you open another pdf on internet?

    ReplyDelete
  7. I can't see yesterday's reply. so, here it is again.

    I can't find any reference in the documentation to parameters standAlone and ParentFolderUri. Maybe it is a version issue? You can ask this on the Jasper forum.

    If the first URL works, why not use that one?

    ReplyDelete
  8. Sorry for the low level of my question, but is there any documentation about how to stablish a JDBC connection between Jasper Server and the Oracle Database? I did not find anything....

    Thanks

    ReplyDelete
  9. First, if you don't have it yet, download the JDBC driver from download.oracle.com (this is a jar file).

    Then, in iReport, go to the Services panel, open the Databases node and right click on the Drivers node to add the new driver.

    Now you can right click on the Databases node and add a new connection using the JDBC driver.

    In JasperServer go to the home page. In any of the folders where you want to create the connection, click on the Add Resource icon in the tool bar and choose Data Source. Follow the wizard from there. You can copy the values for Driver and URL from the values in iReport.
    E.g. for Driver: oracle.jdbc.OracleDriver
    For URL: jdbc:oracle:thin:@dbserver:1521:dbname

    ReplyDelete
  10. Execuse My intrusion, I was Just wondering, if you have considered the alternative of embedding the JasperViewer Applet into your page and on the report URL pass a parametrized URL of a generic reporting servlet from "any" servlet container.
    No stored procedures will be required, no JasperServer (I do like, though).
    Hosting applet can be local (XDB) or remote (servlet container itself).
    embedding is nothing but an HTML snippet in an HTML region.

    ReplyDelete
  11. Thanks SerpentOS for that suggestion. I have never heard of the JasperViewer, but I will look into that now.

    ReplyDelete
  12. I've managed to connect jasaperserver with apex. Everything works great if I call Jasperserver with the URL that contains username and password. But trying to get the report using the procedure I get the following message : "file does not begin with ' pdf-'".
    Has anyone had this kind of a problem?

    Thanks,
    Josip

    ReplyDelete
  13. Josip,

    It sounds like you have not generated the file as a pdf file. A pdf file starts with %PDF-version.

    Check if you missed the parameter:
    &output=pdf

    In my example code I have stored the file in a BLOB in the database. If you have done the same, you can get the file from the database and try to open it directly in Acrobat.

    Note that you don't have to store the file in the database. You can also create a temporary BLOB, as in:

    dbms_lob.createtemporary (vBlobRef, false);

    ReplyDelete
  14. Ino, thank you very much for your quick response.

    Everything works fine now. The parameter "&output=pdf" was there. The only difference between working and non working procedure was that at first I've put the URL part in only one line. Then I've changed it into a 5 - line text (like in your procedure) and now it works. Silly! :)

    Thank you very much again for a quick response and for a great explanation on how to connect Apex with Jasperserver.

    Josip

    ReplyDelete
  15. Thanks for the good information. I have jasperreports working. However, I'm looking for best practices on setting up jasperserver. Has anyone wrote a paper or blogged on it? For example, the default install doesn't create a service. I also discovered that iReports 3.6, which is the latest version, isn't out of the box compatible with jasperserver 3.5. You have to update the jasperreports jar files.

    ReplyDelete
  16. Josip, how dou you managed to connect jasperserevr with apex?

    ReplyDelete
  17. Im Working on Apex 4.0 and this procedure is not working, im receibig the message "file does not begin with ' pdf-'".

    ReplyDelete
  18. I haven't done anything with Jasper in a while.
    Maybe you can try Josip's suggestion in a previous comment.

    ReplyDelete
  19. Hi!
    Sorry, I haven't visited this page for a while... :)
    @Arouna - Do you still have problems with integration? As I said, I haven't looked at this page for a while... :)

    @Oscar - I had to do a little more changes. First, change the call to the url to:
    ----------------
    vReportURL := 'http://host:port/jasperserver/flow.html?_flowId=viewReportFlow'||
    '&'||'reportUnit=/reports/Apex/deptemp'||
    '&'||'output=pdf'||
    '&'||'j_username=un&'||'j_password=pw'||
    '&'||'deptNo='||i_deptno;
    ----------------
    (the & sign made some problems). Secondly, insert a new line to te part where you call the blob:
    -----------------------
    htp.p('Content-length: ' || dbms_lob.getlength(vBlobRef));
    /*this line*/
    htp.p('Content-Disposition: attachment; filename="name_of_the_report.pdf"');
    /*-*/
    owa_util.http_header_close;
    wpg_docload.download_file(vBlobRef);
    ------------------------

    This should do the trick (it has for me).

    Josip

    ReplyDelete
  20. oracle report can convert to ireport? please help me...

    ReplyDelete
  21. You can use an iReport in ApEx.
    In a PL/SQL region generating a URL like below used in an iframe in a htp.p() command:

    'http://server:8080/jasperserver/flow.html?_flowId=viewReportFlow&standAlone=true&_flowId=viewReportFlow&decorate=no&ParentFolderUri=%2Ftest&reportUnit=%2Ftest%2FChart__3&load=' || :P22_LOAD || '&j_acegi_security_check&j_username=apex&j_password=apex'

    Where load is a parameter the chart is driven off of.

    ReplyDelete
  22. Thank you for your blog,
    but i want to ask about ACL configuration and what user exactly should i configure for apex_public_user or my schema name?

    ReplyDelete
  23. Hola,necesitaba saber si me podria dar una orientacon de como pasar parametros a ireport desde mi aplicacion web echa en apex. Tengo un campo que se llama P55_SOLICITANTE,el valor que contiene este elemento debe ser enviado al reporte pero no logro que me funcione...Lo nque hice es:en la pagina donde esta el elemento P55_SOLICITANTE puse un boton con una accion dinamica la cual ejecuta un codigo JavaScript a travez de una funcion y luego en la cabecera html de la pagina coloco el script q llama a la URL

    agradeceria su ayuda,se que algo me falta y no logro entender,soy nueva en esto...andrea

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

    ReplyDelete
  25. Hi Ino,

    I am using ireport designer 4.6.0, and by using this i have created a report with jrxml extension. now my question is how can I run this report by oracle forms. I am using Oracle Developer Suit Version 10.1.2.0.2

    Thanks
    Kashif,

    ReplyDelete