Odd problem with Subreport not appearing

[dgarratt]

I have a problem with a report that I have designed using iReport which does not work properly when I run it within my application. Obvisously there is a difference in the enviroment but I cannot identify what it is.

My report consists of a physical table called APP_MHN which has a primary key on a field called "MHN Number". I have created a group within the primary report on "MHN Number".

My primary report (top level) is called "MHN_Master1". I have a single field "MHN Number" in the Group Header which is keyed on the MHN Number field. So 1 record per group.

The SQL for the primary report looks like :- SELECT * FROM APP_MHN

I have a second (subreport) report called "MHN_Master2" which I have created which looks at another table. This second table also has as field called "MHN Number"  and the query in the second report uses the "MHN Number" from the primary table (report) as a parameter.

The SQL for the subreport looks like :-

SELECT MHN_NUMBER,DECISION,SUM(QUANTIY) as TOTAL_QUANTITY,count(SSCC) as TOTAL_COUNT from app_pallet group by mhn_number,decision having mhn_number = $P{PARENT_MHN_NUMBER}

I have defined the parameter PARENT_MHN_NUMBER in the parent report as being populated by the "MHN Number" field in the primary table.

When the report runs from with iReport it works as intended and you can see a sample of the output in the image attached called ireport_output.gif

When I run the report from my application I get the output shown in the second attachment called application_output.gif

My 1st though was that the SUBREPORT_DIR was not being passed to the master report. So I put an extra field on the master report which showed the content of this field and it seems fine - i,e the same value is displayed within my application as it is within the iReport designer.

When I run the report from my Application I am passing the master report my resultset. I'm not sure if iReport is doing the same.

I have other reports which include sub-reports which work fine, however the main distiction between them is that the subreport uses the parameter value passed to the main report directly and not a field from the main report.

I don't think there can be anything fundametally wrong with the report or it would not work within the IReport enviroment. However I don't know what I am doing differently within the application.

It's been a while since I started using JasperReports so I am wondering if I am missing some xml/flat file config information. All I currently use are the jar files.

Also iReport seems to have a lot more .jar files (with a few version number changes) than the core Jasper Reports distribution directory.

My source for the two reports is attached in case anyone is feeling sufficiently enthusiastic to have a look at it for me.

Any help or advice would be very much appreciated. I know how difficult this sort of thing can be to diagnose without access to the database and developent enviroment on my machine.

Thanks again.

Dave

[dgarratt]

I have been doing some more digging and found that the connection parameter does not seem to be populated with anything when run from my application, although this does not seem to prevent it from picking up data from the master (top level) report. Not sure what if any effect this will have on the subreport though. If it should be populated with something then I don't know what with and how.

 

I've attached some more screen dumps.

 

Dave

[dgarratt]

Well I've googled this to death and it would appear that I'm not on my own. I can get a subreport to work fine when both the master report and the subreport are using a paramter passed to the main report to retrieve data independantly. What I cannot get working (outside of iReport) is a subreport which derives a paramter from the master report to be used in a where clause. It appears that iReport is doing something that my application is not doing - but I've no idea what. At the moment my application code is generic and calls the standard top level report in the same manner for all reports. It would not be very practical if I had to invoke each subreport from my java swing application.

 

So does the way in which the master (top level) report gets invoked from java change if the master report is modified to include a subreport ?

 

Thanks in advance

 

Dave

[dgarratt]

 Anyone ?

I really need to get this one report working very soon.

 

If anyone has a simple example whereby the subreport is using a field from the master reports resultset to use as a parameter in it's own query (aginst a different table) it would be a huge help.

 

Many thanks

 

Dave

[dgarratt]

Well I've invested in the relavant "Ultimate Guides" and have to say that they didn't help one jot !

When I run my report (the master top level one) from my application I have told it to print the $P{REPORT_CONNECTION} parameter.

 

It shows as "null" - but all the fields of the master report look fine.

When I run the same report from iReports the same field prints "Connection ID:44"

I am passing a resultset to Jasper from my Java Swing application. Why does it show a null connection from my application when I run it ?

Am I missing a step ?

I'm sure this is the root cause for the subreport not appearing as well.

HELP HELP HELP HELP PLEASE

 

Dave

 

[dgarratt]

This is the log4j output I get :-

2010-08-23 12:51:26,057 WARN [MHN_List_subreport1 subreport filler] net.sf.jasperreports.engine.query.JRJdbcQueryExecuter - The supplied java.sql.Connection object is null.

I have attached 2 more images - one showing the output from I report (Main report output in RED, subreport output in BLUE)

The second image shows the same report when I run it from my application. The only difference I can think of is that when run from my application I am passing it a ResultSet.

 

 

[dgarratt]

Here is the latest source which matches the output from the previous screen dumps I have uploaded.

 

 

[Teodor Danciu]

Hi,

 

I don't understand how do you expect the REPORT_CONNECTION parameter to be NOT NULL, if your application does not provide a value for this parameter when the report is filled.

You say:

"I am passing a resultset to Jasper from my Java Swing application. Why does it show a null connection from my application when I run it ?"

 

Where do you want JR to take this connection from? If you give me a result set object, how can I know which DB is, which machine, what is the driver to use, what is the username and password? How can I create a connection from a ResultSet????

 

 

Thank you,

Teodor

 

[Teodor Danciu]

Hi,

 

I have looked into JDBC docs and saw there is indeed:

 

ResultSet.getStatement().getConnection()

 

But this is not what I wanted to point out.

 

What you are passing to JR from your application is not a direct ResultSet. It is actually wrapped inside a JRDataSource.

JasperReports simply does not try to detect the type of the data source. It simply does not care. You either pass a fully formed data source, like you do, or let JR create its data source from the internal query and you provide it with a Connection.

JR will never try to extract something from a data source, other then field values.

 

Did you actually expect JR to recuperate the connection from the result set? If you did, then you must have read that somewhere? Where did you read that?

 

Thank you,

Teodor

 

[dgarratt]

I've added the lines below in BOLD to my code to get it working. However the code was working with reports that did not contain subreports, and some reports which did providing that they were picking up their parameter from my calling program, and not using a field value from the parent report.

      resultset = preparedstatement.executeQuery();
      jasperresultset = new JRResultSetDataSource(resultset);
      parameterValues.put(JRParameter.REPORT_DATA_SOURCE, jasperresultset);
      parameterValues.put(JRParameter.REPORT_CONNECTION, connection);
      jasperPrint = JasperFillManager.fillReport(jasperReport, parameterValues, jasperresultset);

 

Post Edited by dgarratt at 08/23/2010 17:45