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 || '¶meter2=' || 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:
Hola,solamente con XLIB puedo pasar parametros desde mi aplicacion apex?por favor ayuda
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.
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.
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.
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 ...
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.
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?
Hi!
I'm wondering if this is poassed through URL, how to protect that from url tampering ... changing parameters?
RG
Damir
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.
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?
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
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!!
Hi Ricardo,
please explain in more detail ... I don't understand.
Cheers,
~Dietmar.
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.
Hi Diego,
this is currently not possible, just strings.
Cheers,
~Dietmar.
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
what are you trying to achieve ... what is your use case?
cheers,
dietmar.
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.
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.
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
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.
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
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.
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
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.
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?
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?
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?
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.
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.
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.
Post a Comment