Problem with Subreport-Parameters

[Linuxhippy]

Hello,

 

I tried to embed a subreport as shown in the video-tutorial here: http://ireport.sourceforge.net/swf/Subreport_viewlet_swf.htm

 

Here's what I did:

1.) Created the two reports, select one as subreport of the other one.

The query of the main-report contains a db-field called "fn_id"-

2.) subreport-right-click -> parameter -> add -> Parameter = $F{fn_id}

3.) subreport->view->report-parameters: name:fn_id, type java.lang.Integer

4.) added where-clause to the subreport: WHERE ${fn_id}=la.fn_id

 

However when I try to run the main-report, it stops with an SQL exception because ${fn_id} is still in the query and has not been replaced.

 

Has the synthax changed, or am I doing something wrong?

 

Thank you in advance, lg Clemens

 

PS: Thanks a lot for the detailed video-tutorial. I guess without it, it would have been really hard to figure out howto do it.

Post edited by: Linuxhippy, at: 2007/12/12 10:34

[lucianc]

The video contains a small mistake, you need to use WHERE $P{fn_id}=la.fn_id in the query (notice the $P{..}).

 

Regards,

Lucian

[Linuxhippy]

Thanks a lot, that solved the execution Problem :-)

 

However I now have the problem, that my sub-reports are empty (completly, although I did not specify "empty when null" for the fields.

 

My main-report query is:

Code:

mysql> SELECT k.name AS kundenname, alnk.fn_id AS fn_id FROM auftrag a LEFT JOIN verpackung v ON (v.id=a.verpackung) LEFT JOIN kunde k ON (k.kundennummer=a.kunde) LEFT JOIN land ll ON (ll.id=a.lieferland) LEFT JOIN auftrag_fn_lnk alnk ON (a.id=alnk.auftrag_id);
+------------+-------+
| kundenname | fn_id |
+------------+-------+
| kunde2     |     1 |
| kunde2     |     2 |
+------------+-------+

 

and one example-query for the subreport looks like:

Code:

[code] mysql> SELECT anzahl AS stk, type AS type, kommentar AS kommentar_ltb FROM lieferumfang_auftrag la WHERE la.fn_id=2; +------+---------+-------------------------+ | stk | type | kommentar_ltb | +------+---------+-------------------------+ | 1 | bmw2299 | mit Kraftstromanschluss | +------+---------+-------------------------

 

If I replace the WHERE la.id=$P{fn_id} with WHERE la.id=2, it also works. However with la.id=$P{fn_id} the subreport is simply empty :-/

 

Sorry for nerving so much, and thanks a lot for beeing so helpful.

 

lg Clemens

Post edited by: Linuxhippy, at: 2007/12/12 12:41

[lucianc]

Finding out what happens is not easy unless a full (and simple enough to understand) sample is provided.

 

You can also try to debug the issue, for instance by printing the value of the subreport parameter in a text field to see whether the it has the correct value.

 

Regards,

Lucian

[Linuxhippy]

Thanks a lot for beeing that patient with me.

 

1.) I already added a textfield showing the subreport-parameter, and its shows the right values in the main-report (1 and 2).

I used wireshark to sniff the packets between iReport/mysql-jdbc and mysql - and here I see the following queries two times:

 

SELECT anzahl AS stk, type AS type, kommentar AS kommentar_ltb FROM lieferumfang_auftrag la WHERE la.fn_id=?;

 

So it seems the parameter is not passed correctly to the sub-report but instead '?' is passed. Any ideas where this could come from?

 

Thanks a lot, lg Clemens

[lucianc]

Linuxhippy wrote:

So it seems the parameter is not passed correctly to the sub-report but instead '?' is passed. Any ideas where this could come from?

 

This is the correct behaviour. $P{..} query parameter are translated to prepared statement parameters (which are represented by question marks).

 

Regards,

Lucian

[Linuxhippy]

Hello again,

 

I created a small testcase which also shows the problem.

Its really simple, the main-report only selects a key, and the subreport selects a name which is related to that key.

I also added SQL DDL and testdata.

 

It would be great if you could have a short look. Sorry for bothering you that much :-/

 

lg Clemens [file name=subreports.gz size=1506]http://www.jasperforge.org/components/com_joomlaboard/uploaded/files/subreports.gz[/file]

[lucianc]

You're not sending any value for the subreport parameter from the master, hence the default value for the parameter is used.

 

Regards,

Lucian

[Linuxhippy]

Sorry for bothering you so long. I re-read the video-tutorial and found out that I forgot to set the default-expression to $F{fnid} so that the parameter get passed down to the subreport.

 

Thanks a lot for your patience ... and for the great software.

Its amazing how poerful jasperreports is, and how easiy this power can be leveraged by iReport :)

 

Thanks a lot, lg Clemens