Monday, August 13, 2012

JasperReportsIntegration: Passing multiple parameters to the report

With the JasperReportsIntegration kit I provide a mechanism to call JasperReports reports from an APEX application.

The reports are stored locally in the filesystem accessible to the j2ee container running the JasperReportsIntegration j2ee application. This application will connect to the desired Oracle schema using connection information deployed in the J2EE container.

This j2ee application provides an url interface to calling the desired report against a desired data source.

So how can we pass parameters to our report, e.g. for producing a report for a specific customer or order number?

Let's consider my test report for that purpose (test.jrxml). The test report will list the user objects installed in the Oracle schema we connect to as well as produce some header information about the selected report locale. You can even pass three parameters to the report called parameter1, parameter2 and parameter3.

So, how can we pass the parameters to the report?

First of all we need to specifiy the parameters in the report itself. I have added the three parameters to the parameter list:


Currently all parameters (which we want to call from the JasperReportsIntegration kit) have to be specified as java.lang.String, no other data types are allowed. I might add that as a new feature in a later release. But for now only strings are allowed:


We assume that we have an APEX page with two page items, P1_FILTER_OBJECT_NAME and P1_FILTER_OBJECT_TYPE.

In order to pass these two parameters to the test report using the pl/sql interface XLIB_JASPERREPORTS, you would do the following:
declare 
  l_additional_parameters varchar2(32767);
begin
  -- set the url for the j2ee application
  -- better retrieve that from a configuration table
  xlib_jasperreports.set_report_url('http://localhost:8090/JasperReportsIntegration/report');

  -- construct addional parameter list
  l_additional_parameters := 'parameter1=' || apex_util.url_encode(:p1_filter_object_name);
  l_additional_parameters := l_additional_parameters || '&parameter2=' || apex_util.url_encode(:p1_filter_object_type);

  -- call the report and pass parameters
  xlib_jasperreports.show_report (p_rep_name => 'test',
                                  p_rep_format => xlib_jasperreports.c_rep_format_pdf,
                                  p_data_source => 'default',
                                  p_additional_params => l_additional_parameters);

  -- stop rendering of the current APEX page
  apex_application.g_unrecoverable_error := true;
end;

Why do we use apex_util.url_encode? This is required since we essentially pass the parameters via an url to the j2ee application. 

When the report is executed, we get the following result (assuming P1_FILTER_OBJECT_NAME=APEX$ and P1_FILTER_OBJECT_TYPE=TRIGGER):
 
Using the test report this will just display the passed parameters. If you want to filter the user objects using the parameters you could modify your query to:

This will filter the result either bei object_name or object_type (in the example I filtered by the object type TRIGGER):

Hope that helps,
~Dietmar.

19 comments:

Andrea Ortiz said...

Hola,solamente con XLIB puedo pasar parametros desde mi aplicacion apex?por favor ayuda

Dietmar Aust said...

Hi Andrea,

I am sorry, but I only speak German, English and French ... and Latin is not really helpful any more ;).

Please once again in English :).

Cheers,
~Dietmar.

Andrea Ortiz said...

Forgiveness. I need to know if I can pass additional parameters to JasperReport my apex application without using xlib. I need another way to do it but I do not understand.
What I did is put a button in the application of apex with dynamic action that calls a function Javascript, javascript.el code is:
script language="JavaScript" type="text/javascript">
function print (APPLICANT) {
var name = document.getElementById ('Applicant');
alert (name.value);
window.open("http://munisvn:8098/JasperReportsIntegration/report?_repName=habilitacion&_repFormat=pdf&_dataSource=dcdb&_outFilename&_repLocale=en_US&_repEncoding=ISO-8859-1", "_blank&persona=name");
}
script

applicant's my page element that I must pass to fill the parameter that believes in iReport which is called "person". need your help please.

Dietmar Aust said...

Hi Andrea,

make sure the get the name of the report parameter right. Also make sure, the report parameter is defined as java.lang.String, this is the only type that will work.

You would use encodeURIComponent in javascript to add each parameter : http://stackoverflow.com/questions/75980/best-practice-escape-or-encodeuri-encodeuricomponent.

e.g.
alert("http://munisvn:8098/JasperReportsIntegration/report?_repName=habilitacion&_repFormat=pdf&_dataSource=dcdb&_outFilename&_repLocale=en_US&_repEncoding=ISO-8859-1&person=" + encodeURIComponent("hello world"));

I can see that you added the parameter to the second parameter of the window.open command, this is wrong. You need to add it to the first part after _repEncoding.

But why are you doing it this way anyway? It is pretty insecure. You should rather use the database as a tunnel via the xlib package and restrict access to your j2ee server munisvn.

Cheers,
~Dietmar.

Andrea Ortiz said...

thank you very much, helped me a lot its respuesta.Quisiera help me in one last little thing:
I have two parameters in my report (fechadesde and fechahasta) would the url in JavaScript code, so I did:

script language="JavaScript" type="text/javascript">
function imprimir(P1_DESDE,P1_HASTA){
var desde = document.getElementById("P1_DESDE");
var hasta = document.getElementById("P1_HASTA");
window.open("http://munisvn:8098/JasperReportsIntegration/report?_repName=habilitacion&_repFormat=pdf&_dataSource=dcdb&_outFilename&_repLocale=en_US&_repEncoding=ISO-8859-1&fechadesde=" + encodeURIComponent(desde.value)&fechahasta=" + encodeURIComponent(hasta.value));

}
/script

returned but not working ...

Dietmar Aust said...

Hi Andrea,

sorry for the late response, been to busy with work, new products and conferences ;).

What is not working exactly? Any error message?

Why don't you want to use the xlib-packages? The way you use it right now is not really secure. Also, this way you cannot control wich data the user is allowed to access because he can manipulate the http request.

This is one of the major reasons to use the database as a proxy to the j2ee server. In the database you can use the current user (:app_user) to make sure he/she can only see what he/she is allowed to see.

Cheers,
~Dietmar.

bayan said...

Hi Dietmar,
I am facing a new weird case.
when I preview the report from ireport it's giving me the right report but when I try it from apex it's just showing blank page. I tried to put a number in the report sql instead of the parameter to check if the report going to appear from apex. yes it is giving me the report but once i returned the parameter it stops working.
note I am using java.lang.string for it and the name of the parameter is just the same as the one I am using in apex page process region.any clue?

Damir Vadas said...

Hi!

I'm wondering if this is poassed through URL, how to protect that from url tampering ... changing parameters?

RG
Damir

Dietmar Aust said...

Hi Damir,

I don't see a problem here. The URL is constructed using the package xlib_jasperreports. The url is ONLY used when calling the J2EE application using utl_http from whithin the database. This URL is never exposed to the client, the rendering request is tunneled through the database which functions as a proxy.

I always even suggest that the J2EE is only reachable by the database server and not the end user directly.

Thus, the end user will have no access to the URL. You can do any kind of security check within you database package or plsql process in APEX before calling the api.

In JasperReports itself, you will typically construct the where clause using the passed parameters.

If you use $P{..} JasperReports will produce bind variables (query parameters), while $P!{..} will be replaced as text in the query string.

By using $P{..} you will also be pretty save with regards to SQL injection attacks.

If you spot any security issue here, please let me know.

Cheers,
~Dietmar.

Prashanth Ashok said...

Hi,

I am currently faced with a situation. You have Parameter 1,2,3 etc. and values can be passed to each of these, right?

What if you have one parameter to Jasper, but this parameter can have multiple values and each of these values should be passed to Jasper? Can this be done?

Bayan Aljifri said...

Hi,
it's working great with me when I pass one parameter but when I tried to pass two it gave me an error "the file might be damaged".
removing the second parameter from the code is returning me to a printable pdf!
Bayan Aljifri

Ricardo Capuz said...

Hi, i have a problem with ".show_report", there is a way to show the report on the browser with ".get_report"? i really needed!!

Dietmar Aust said...

Hi Ricardo,

please explain in more detail ... I don't understand.

Cheers,
~Dietmar.

diego said...

hi Dietmar,
i would like to know how can we pass parameters of the following types: integer and date to the report in iReport.

thanks for your answer.

Dietmar Aust said...

Hi Diego,

this is currently not possible, just strings.

Cheers,
~Dietmar.

Anonymous said...

Hi,
can anyone suggest me where i am doing wrong. only first condition is working fine.remaining all are failing to display in ireport.
please suggest.

((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase(“All”)) &&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase(“All”)) &&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase(“All”)))?$F{national_avg_cond1}.doubleValue():
((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase($F{DELIVERY_NM}.toString()))&&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase(“All”))&&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase(“All”)))?$F{national_avg_cond1}.doubleValue():
((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase(“All”))&&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase($F{SELECTED_INST}.toString()))&&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase(“All”)))?$F{national_avg_cond1}.doubleValue():
((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase($F{DELIVERY_NM}.toString()))&&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase($F{SELECTED_INST}.toString()))&&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase(“All”))) ? $F{national_avg_cond2}.doubleValue():
((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase(“”))&&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase(“All”))&&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase($F{COURSE_NM}.toString())))?$F{national_avg_cond2}.doubleValue():
((null == $P{reportSearchDTO.deliveryType} || $P{reportSearchDTO.deliveryType}.equalsIgnoreCase(“”))&&
(null == $P{reportSearchDTO.instructorID} || $P{reportSearchDTO.instructorID}.equalsIgnoreCase($F{SELECTED_INST}.toString()))&&
(null == $P{reportSearchDTO.course} || $P{reportSearchDTO.course}.equalsIgnoreCase($F{COURSE_NM}.toString())))?$F{national_avg_cond3}.doubleValue():0.0

Dietmar Aust said...

what are you trying to achieve ... what is your use case?

cheers,
dietmar.

Shilpa Reddy said...

Thank you for the reply aust.

based on the parameter value selected according to the scenario, i want to display the field.
in the above condition, am calculating the average of responses based on the value selected for deliveryType,course_id,instructor_id
parameters.

Dietmar Aust said...

well, you could troubleshoot your statement here ... but I would rather recommend computing this flag in your sql statement so that in the report it boils down to a single and thus simple check.

it is good practice to create a view for your report and compute as many relevant parts of your report already WITHIN sql ... makes your life a lot easier.

Cheers,
~dietmar.