[RESOLVED] jasper & iReport multi-reports

[yann luppo]

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]

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]

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:

        <queryString>
		<![CDATA[$V{sql}]]>
	</queryString>

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

        <variable name="sql" class="java.lang.String">
		<variableExpression><![CDATA[($P{reportType}.toLowerCase().equals("summary")) ? new String("SELECT  CURRENT_DATE") : new String("Select CURRENT_TIME")]]></variableExpression>
	</variable>

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

        <parameter name="reportType" class="java.lang.String"/>

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]

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]

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.

[yann luppo]

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]

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:

<parameter name="reportType" class="java.lang.String" isForPrompting="true"/>

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

<parameter name="dynamicSelect" class="java.lang.String" isForPrompting="false">
  <defaultValueExpression>
    <![CDATA[
      (new String("testValue").equals($P{reportType}.toLowerCase()) ? new String("Select CURRENT_TIME") : new String("Select CURRENT_DATE"))
    ]]>
  </defaultValueExpression>
</parameter>

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

<queryString>
  <![CDATA[
    $P!{dynamicSelect}
  ]]>
</queryString>

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]

I have created a Jasper Action and jrxml. The following code retrieves data from database, but I am getting a blank report page.

   <?xml version="1.0" encoding="UTF-8"  ?>
<!-- Created with iReport - A designer for JasperReports -->
<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">
<jasperReport name="samplereport" >

<field name="number" class="java.lang.Long"/>
<field name="english" class="java.lang.String"/>
<field name="german" class="java.lang.String"/>

<columnHeader>
<band height="28" isSplitAllowed="true">
<staticText>
<reportElement x="20" y="0" height="20" width="35"/>
<text>Month ID:</text>
</staticText>


<staticText>
<reportElement x="220" y="0" height="20" width="65"/>
<text>English:</text>
</staticText>


<staticText>
<reportElement x="380" y="0" height="20" width="75"/>
<text>German:</text>
</staticText>
</band>
</columnHeader>

<detail>
<band height="27" isSplitAllowed="true">

<textField>
<reportElement x="56" y="0" height="20" width="164"/>
<textFieldExpression class="java.lang.Long">
<![CDATA[$F{number}]]>
</textFieldExpression>
</textField>

<textField>
<reportElement x="286" y="0" height="20" width="84"/>
<textFieldExpression class="java.lang.String">
<![CDATA[$F{english}]]>
</textFieldExpression>
</textField>


<textField>
<reportElement x="456" y="0" height="20" width="94"/>
<textFieldExpression class="java.lang.String">
<![CDATA[$F{german}]]>
</textFieldExpression>
</textField>
</band>
</detail>
</jasperReport> 

Jasper related page

JasperCompileManager.compileReportToFile(contextPath,getServlet().getServletContext().getRealPath("/WEB-INF/classes/jasper/month.jasper"));
			InputStream reportStream = getServlet().getServletConfig().getServletContext() .getResourceAsStream("/WEB-INF/classes/jasper/month.jasper");
			System.out.println("JasperAction,A're reportStreaming");
			
			//QueryResults

			JRBeanCollectionDataSource beanCollectionDataSource=new JRBeanCollectionDataSource(utilDAO.generateJasperReport(),false);
			System.out.println("JasperAction,Datsource saved");
			//generate report
			System.out.println("GetRecordCount:"+beanCollectionDataSource.getRecordCount());
			
			JasperFillManager.fillReportToFile(getServlet().getServletContext().getRealPath("/WEB-INF/classes/jasper/month.jasper"), new HashMap(), beanCollectionDataSource);
			System.out.println("JasperAction,A're fillReportToFile");
			//view pdf file in browser window
			JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, new HashMap(), connection);    

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]

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]

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

    
   $fillManager = new JavaClass("net.sf.jasperreports.engine.JasperFillManager");  
   $params = new Java("java.util.HashMap");
   
   $params->put('emis', 105480827);

   $jasperPrint = $fillManager->fillReport($report, $params, $objDbConnect);  
  
   $exportManager = new JavaClass("net.sf.jasperreports.engine.JasperExportManager");  
	  
    $outputPath = realpath(".")."/"."promotion.pdf";  
    $exportManager->exportReportToPdfFile($jasperPrint, $outputPath);  
    header ('Location: promotion.pdf'); 

And here's my respective jrxml code:

    

<queryString language="SQL">
<![CDATA[SELECT *
FROM
     `RPT_PROMOTIONSCHEDULE` RPT_PROMOTIONSCHEDULE WHERE NATIONALEMIS = $P!{emis}]]>

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]

Thanks for sharing the solution... all I needed what the "!"

[Martijn Verburg]

And welcome to Javaranch Saul!

[Rajubhai Kotwal]

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]

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.