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.

31 comments:

Andrea Ortiz said...

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

Unknown 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.

Unknown 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 ...

Unknown 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

Unknown 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.

Unknown 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

Unknown 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!!

Unknown 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.

Unknown 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

Unknown said...

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

cheers,
dietmar.

Unknown 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.

Unknown 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.

Datta said...

Hi Guys,

I created a Jasper report and checked preview in pdf format. It shows correct pdf.
when the same report is called from application "A" (on DB schema A). It displays a blank page immediately without any error. The code for calling report in form is as below,


However, in the same report if the report format changed to html it display correct report output in html format.

The report attributes are correct because I tried calling the report from application B (on schema B) and it works fine with pdf output.

I do not understand the issue. Pls help

Unknown said...

Hi Datta,

difficult, you provide too little information.

You did not provide any code and don't know anything about the report.

How can I test what you are doing?

Cheers,
~Dietmar.

tespos58goo said...

Hello Dietmar,
Hope you are doing well.
When passing parameters to the JasperReportsIntegration from APEX, will it handle parameters that are date datatypes (e.g. fields from APEX that are populated by an APEX date picker?
If so, how is that done in the PL/SQL that is used to call the report?

Prost / Zum wohl
Tony

Unknown said...

Hi Tony,

just pass it as a regular item , e.g. apex_util.url_encode(:p1_picked_date). In APEX all variables are text (varchar2).

in the report you will have to convert it to a real date (e.g.: to_date($P{pDate}) in the report query.

Does that make sense?

Cheers,
~Dietmar.

Harikrishna said...

Dear Dietmar,

I am new to the jasper reports.here i developed jasper reports through the jasper reports studio.

finally i was deployed report into jasper server. in server datasource i was manually configured the oracle schema name.

for me i don't want to manual configuration of the schema names, because all the development phases we are having different schemas.

schema names no need to change each time . here in oracle reports we will send schema names in url.

http://localhost:8080/jasperserver/flow.html?_flowId=viewReportFlow
&_flowId=viewReportFlow
&ParentFolderUri=/reports
&reportUnit=/reports/reportID
&standAlone=false
&_repFormat=pdf
&P_REP_SR_NO=102
&P_COMP_CODE=120
&P_REP_ID=samplerep
&j_username=jasperadmin
&j_password=jasperadmin

this is the way i am calling jasper reports from java.
is there any way to do like this..

Please help me on this one.

Thanks

Unknown said...

Hi Harikrishna,

I am sorry, but I cannot help you with this.

You are using the JasperServer but I have created my own J2EE for Jasper using the public libraries. You can find it here:
http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/2.3.0-beta/Index.html

It creates a wrapper using pl/sql to build the report url and call a j2ee application through utl_http on the database.

Best of luck,
~Dietmar.

JBL said...

Using JasperSoft Studio 6.1.1, they now have some "Built-In" java functions in the Expression Editor. If I use any of the built-in functions I get errors when running on my apex hosting server. I'm sure it is missing the .jar that includes these functions, I'm just wondering if I just need to request the hosting company add the jar to their report server or if that would require an update of your plug-in?

JBL said...

I really need to adjust the time data in my report to the client timezone. I keep getting the timezone of the report server. Would it be possible to have the integration updated to be able to pass time zone, just like you can pass locale?

Unknown said...

Hi JBL,

> they now have some "Built-In" java functions in the Expression Editor. If I use any of the built-in functions I get errors when running >on my apex hosting server
I have just a few weeks ago learned about that. It was a missing .jar file in my package. It is now fixed in release 2.4.0, you can find it here: http://www.opal-consulting.de/downloads/free_tools/JasperReportsIntegration/

It will need some more testing but it already looks quite good. The jasperreports libraries are also upgraded to 6.4.1, the current release.

Cheers,
~Dietmar.

P.S.: BTW, where are you hosting your application?

Unknown said...

Hi JBL,

>I really need to adjust the time data in my report to the client timezone. I keep getting the timezone of the report server. Would it be
>possible to have the integration updated to be able to pass time zone, just like you can pass locale?
Yepp, I have just added the feature to the release 2.4.0 ... please give it a try and report the feedback.

Cheers,
~Dietmar.

JBL said...

Wow, thank you Dietmar for jumping on both of my requests. I am hosting my application at maxapex.com, so I will see if they will upgrade to your latest updates.

Unknown said...

Hi JBL,

> Wow, thank you Dietmar for jumping on both of my requests
Well, you got lucky :). I was in the middle of upgrading the integration to the latest jasperreports version anyway ... so I just took care of that as well.

Cheers,
~Dietmar.