Subreport within main Report

[davidbhybrid]

 

 

I am having an issue getting a subreport to link to the main report. I need to give the user different input values in order to genereate the report. For instance, I have 5 parameters established (city, cust_id, job_number, state, zipcode), any of which can be left blank which tells the QRY to grab all:

where CASE WHEN $P{cust_id} = 0 THEN cust_contact.id > 0 ELSE cust_contact.id = $P{cust_id} END
and CASE WHEN $P{city} = 'ALL' THEN cust_contact.city like '%' ELSE cust_contact.city ilike $P{city} END
and CASE WHEN $P{state} = 'ALL' THEN cust_contact.state like '%' ELSE cust_contact.state ilike $P{state} END
and CASE WHEN $P{zipcode} = 'ALL' THEN cust_contact.zip like '%' ELSE cust_contact.zip ilike $P{zipcode} END
and CASE WHEN $P{job_number} = 0 THEN j.id > 0 ELSE j.id = $P{job_number} END

When the cust_id and the job_number is used as the input, the report generates correctly and the subreport is populated within the main report as expected. When I want to see ALL customers in the state of NJ (leaving cust_id, job_number, etc as all and selecting NJ as the state), I get a main report with a subreport in it hundreds of times.

Is there a way to have the subreport link back to the main report using the job_number so that each report will only have one subreport generated inside of it?

Thanks in advance!

[mikolajj]

Ca't you use "Print When Expression" for subreport?
Creating proper conditions in this place?

[davidbhybrid]

Thank you for the quick response. I am new to this...i have the job_number being returned in BOTH the qry's for the Master Report and the Subreport. How do I go about setting up the Print When Expression to only print the Subreport when the job_number of the Master Report equals the job_number of the Subreport?

Thanks again!

[mikolajj]

I think you should recreate your logic. MAIN report should ask SUBREPORT with paramteres to get exact answer. This would be the best practice... as I think.

 

Still there is a way to pass information from SUB to MAIN report.

Take a look here http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=29420

[cbarlow3]

It sounds to me like what you're saying is that you get back the right information, but you get it repeated when your input parameters result in more than one row being returned.  It seems to me that there are two logical ways to organize your report, depending on what you're trying to do:

1. Don't use a subreport at all...I haven't seen anything in your explanation that tells me why you need it...use the SELECT you've built as your main query, and for each row that meets the criteria based on your input parameters (and your potential interpertation of wild cards), output the information for that returned row in the detail record.

2. Use your main query to determine which customer ID's (assuming that thisis the unique key) match the criteria based on the input parameters, and for each one, call a subreport, passing only that customer ID, and then in the subreport you have some completely different query that drills down into new details or whatever based solely on the customer ID, not based on the original input parameters, which were only used in the main query to qualify this customer to appear on the report

It sounds to me like maybe you're combining the two ideas:  using your main query to identify which customers match the input criteria, and then within a detail record calling a subreport that uses the same query.  So if five customers match the input parameters, you end up with a list of those five in a subreport that is in your first detail record of the main query...but is also repeated in the other four detail records you get returned.  If the main query and the subquery are doing exactly the same thing, you probably don't need that subreport at all...certainly not in the detail section.  I have used a List element with a dataset that had essentially the same query as my main query (the List dataset works very similarly to a subreport), but I put it in the summary section, and it's typcially to redundantly repeat subtotals that I've already displayed at the end of each group.  I wish that were a standard feature, by the way--some way to repeat all your group footers in a combined List/Table at the end of the report without having to run an additional query.

Carl