It's not a secret anymore!*
The moose likes Other Open Source Projects and the fly likes [RESOLVED] jasper & iReport multi-reports Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "[RESOLVED] jasper & iReport multi-reports" Watch "[RESOLVED] jasper & iReport multi-reports" New topic
Author

[RESOLVED] jasper & iReport multi-reports

yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
Hi everyone,

I am currently working on a reporting feature at work that looks like it could be improved. There are 4 reports running on Jasper and made with iReport. Those 4 reports are very similar, in fact they use the same data to show different type of information.

One is a daily detailed report, the other is a daily summary report, then there is a monthly detailed report and finally a monthly summary report. By now you might have already guessed where I'm going with that... Being new to iReport and Jasper, I was wondering if I could have only one template and work the slight differences between each report with some conditions based on some additional input parameters? Or any other way.

Anyone who's ever done that or something similar with those tools by any chance?

Thanks.
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
Just found out about Scriptlets. I'm gonna dig in that direction, just in case someday another Jasper Report beginner like me ever finds that post.
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
yann luppo wrote:Just found out about Scriptlets. I'm gonna dig in that direction, just in case someday another Jasper Report beginner like me ever finds that post.


After reading about scriptlets it seems like it could be the solution but I got side tracked thinking there might be an easier way to do that. Namely using a variable to store the sql to be executed based on an input parameter parsed in a java expression.
Here it is illustrated:



That's my querystring pointing to a variable, which is declared as such:



I also have declared the parameter "reportType" right before the querystring like this:



But this method doesn't seem to work properly, I get an error message:


Error filling print... Error executing SQL statement for : report_summary
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report_summary      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:668)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1212)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:842)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:791)      at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)      at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:890)      at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:561)      at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:986)  Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$"      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:255)      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)      ... 11 more 
Print not filled. Try to use an EmptyDataSource...


Could anyone point me in the right direction in how to implement mulitple queryies in one jrxml template please?

Thank you.
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
I'm hitting a dead end on this. The variable solution I was talking about earlier doesn't seem to work no matter what I do.

I'd either need to inject the query based on an input parameter or change a couple of things in the query based again on an input param, because the 2 queries will actually be very similar...

Any Jasper super Guru out there with that'd have an idea on how to approach this?
Darya Akbari
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
yann luppo wrote:
One is a daily detailed report, the other is a daily summary report, then there is a monthly detailed report and finally a monthly summary report. ... Being new to iReport and Jasper, I was wondering if I could have only one template and work the slight differences between each report with some conditions based on some additional input parameters? Or any other way.

Anyone who's ever done that or something similar with those tools by any chance?

Thanks.


You can have one template or better .jrxml file. Go and create that one report with your iReport visual design tool. For each type of report create a group with iReport.


SCJP, SCJD, SCWCD, SCBCD
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
Darya Akbari wrote:
You can have one template or better .jrxml file. Go and create that one report with your iReport visual design tool. For each type of report create a group with iReport.


Hi Darya,

Thank you for your reply. The 2 queries I have are slightly different, so I still would need to have 2 queries defined in the JRXML, one for each group... I'm not sure how to do that.
If that can be solved then I can see how to display only one group with a Java expression, iReport made that easy enough to do, but I seem to be allowed to have only one querystring...
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
Ok so I resolved how to make a dynamic query in the jrxml itself and within the querystring tag.... It just took me a few days so I'm going to share hoping it will save other's people time.

The solution using the variable I was trying earlier and explained above isn't far from the actual solution. Except instead of using variables, you need to use a parameter.

First declare a parameter that will help trip the right logic:



Then the parameter that contains the actual logic (please note the attribute :



Finally your querystring can use that last parameter however you need it:



Please note the use of $P!{} instead of the plain $P{}. The exclamation point is important to make sure that the content of the parameter is one of the first thing to be evaluated.

Using that same logic you can also modify only pieces of your SQL if the queries are close enough.

Hope that will save time for some people.

Cheers,
Yann
lekshmi sdsd
Greenhorn

Joined: Apr 28, 2009
Posts: 1
I have created a Jasper Action and jrxml. The following code retrieves data from database, but I am getting a blank report page.


Jasper related page


Please help me in this. The beanCollectionDataSource is having all records from the table.But those values are not getting displayed in the pdf. I am getting a blank pdf.
yann luppo
Greenhorn

Joined: Oct 29, 2008
Posts: 26
Hi lekshmi,

Replying to a post marked as resolved is probably not going to get you a whole lot of responses.... I would suggest you copy your reply and create your own post so you can get more help.

I haven't used the Jasper Action class and therefore can hardly help you with that.

Wishing you good luck,
Yann
steven foukaridis
Greenhorn

Joined: Oct 21, 2009
Posts: 3
Hi guys,

I'm having trouble getting my variables across to my .jrxml file.

I'm using php with a java bridge and reports created in ireports.

Here's my PHP code



And here's my respective jrxml code:


However, it keeps coming back with the following error:

Report design not valid : 1. Query parameter not found : emis

I'm busy going nuts with this one...
Saul Davis
Greenhorn

Joined: Jul 06, 2010
Posts: 3
Thanks for sharing the solution... all I needed what the "!"
Martijn Verburg
author
Bartender

Joined: Jun 24, 2003
Posts: 3274
    
    5

And welcome to Javaranch Saul!


Cheers, Martijn - Blog,
Twitter, PCGen, Ikasan, My The Well-Grounded Java Developer book!,
My start-up.
Rajubhai Kotwal
Ranch Hand

Joined: Nov 03, 2009
Posts: 47
Hello experts,

This is pretty older post but I hope, I would find a solution to my problem. I have been generating reports using java and ireport. I used to have only one subreport earlier and didn't face any problem while generating those reports. Now, I have to add another subreport and merge it with the main report. In short one main report along with 2 subreports. Right now, in my main report and subreport1, i am passing one parameter (same for both master and slave i.e. $P{recordId}). Subreport1 contains data based on the JDBC connection whilst 2nd one empty data source.subreport2(i want to display 3 columns of data called formula, status, result) . I am using empty datasource for the subreport3. Now, my question is, "can we pass objects to the reports from subreports?" I might have different values to display in the report via my java program. It would not be feasible to use multiple parameters for same columns/data. I would like to know, can we pass objects via any mechanism? Can we store any mechanism which can store objects those eventually consist of different parameters? I searched on the forum and found JRBeanCollectionDataSource. How should I use it in the program? It would be great if I know the steps to include this bean in java or in ireport. Will it be useful for me? Is it the right direction to get for what I am looking for? Please suggest your ideas. Thank you in advance.
Allan Border
Greenhorn

Joined: Oct 20, 2013
Posts: 2
hello everyone

I had a similar requirement where I needed to dynamically load the query(to be used to run the report) among two queries based on some condition. This condition in my case was the database the application is using. I had to run an application with two databases(oracle and posgresql). I did not want to make much changes in my java code to run my jasper reports. I wanted to modify the jrxml file only and put the condition there. The solution given here almost worked except for a small change.
This is the part of the jrxml where I have the condition put:

<parameter name="post" class="java.lang.String">
<defaultValueExpression><![CDATA[]]></defaultValueExpression>
</parameter>

<parameter name="query" class="java.lang.String" isForPrompting="false">
<defaultValueExpression><![CDATA[(new String("postgres").equals($P{post}) ? "select * from employee limit 5" : "select * from employee where ROWNUM>=1")]]>
</defaultValueExpression>
</parameter>
<queryString>
<![CDATA[$P!{query}]]>
</queryString>

Here's the explanation:
When you run the report, it is going to ask to enter value for the parameter "post". If you enter "postgres"(for postgresql) then the first query("select * from itgs_bank_master limit 5") is returned and this is set in "query" parameter and viceversa. This might seem complex but it is not. Then in the <queryString > tag, the value(which is the query) which is there in "query" parameter is put which is then picked while running the report.

isForPrompting specifies whether or not you want a prompt for entering the value for the parameter after pressing preview. This is set to true(default) in the first parameter and false in the second. Reason for this is that I had to enter the database name(oracle or postgres) I am using for running the report. In the second one I don't want to enter anything, I want it to be dynamically set during run time.

limit and rownum are different keywords in postgres and oracle respectively. Therefore different queries.

I spent a lot of time doing all this and intend to save time of those who seek a similar solution.

A big thanks to yann luppo's post without which I wouldn't have come up with this solution.
 
 
subject: [RESOLVED] jasper & iReport multi-reports