Pass a database value from report to subreport

[fabioparoni]

Hi guys,

I have a main report that shows information about two contacts (name and surname) taken from a database table. In the subreport I want to show below the name of each contact all the emails of each one. Emails are stored in another database table.

Example:

 

Name1 , Surname1

      firstemailof1@email.com

------------------------------------------------

Name2 , Surname2

      firstemailof2@email.com

      secondemailof2@email.com

 

I need to authomatically pass the id of each person to the subreport to select all the emails of each one. But how can I do that?

I'm using iReport 4.5.1.

Thanks in advance.

 

[law]

Do you know how to make a subreport? Lets say you do. In this case you'd end up with 2 sql statements. Just an example:

In main report: SELECT distinct name FROM account order by name                  

Then in subreport: SELECT id, division FROM user_account where company_name = $P{company_name}      

If you need more specifics you want to check out the sample called "Subreports" in Help/Samples Directory. But here are few basic steps:

1) Add subreport icon into the main report. 2) Choose new report.
3) Put in sql, avoid first time around things like "where fieldName=$p{paramName}" This will need to be set later.
4) once report exists, create the param, add "where fieldName=$p{paramName}" . Test teh sql with a string expession.
5) Build subreport, build report. 6) If subreport doesn't show up, make sure that subreport expression contains only report name.

 

[fabioparoni]

Hi liw,

thanks for reply.

Your example uses a Parameter to set the query in the subreport. So, following your example, iReport will ask me the value I want to set for that param before compiling the report and the subreport. Instead I need that the param is automatically set for the query in the subreport taking the value of the primary key of the table used in the main report.

The query in the subreport should be something like this:

                 SELECT id, division FROM user_account where company_name = $P{Primary Key From table in main report}

 

Is it possible?

Thanks in advance

Fabio

[law]

Hi fabioparoni,

When you have that parameter set properly, you wont be asked to input it while running the main report. That popup only occurs when you run a subreport separetely. My guess is that you created that parameter in the main report, but it should be only created in the subreport, on far left top. In main report, when you select subreport icon, in far right properties area, at the very bottom, you need to also add that parameter to the "Parameters" property.

I hope this helps.  You should compare what you have to the iReport "Subreports" example.

Post Edited by liw at 08/03/2012 17:54