SQL Query to use in main report when rendering four subreports

[pluralpronoun]

Hi all. I'm having some trouble understand the relationship between the SQL query you define in the main report and the SQL queries in each sub-report included in that report. I've been reading a stupid amount of stuff online the past few days and I still don't understand how I'm meant to implement this.

 

So here's my situation: I have a main report which contains 4 sub-reports in Jaspersoft Studio. Each of the sub-reports has an SQL query which is querying a PostgreSQL database. When you open an individual sub-report, pass in the three required parameters and generate the preview in Jaspersoft Studio, all goes well. I see my data. Here is an example of an SQL query that I am using in a sub-report:

 

SELECT sum(field1) AS total_field1, sum(field2) AS total_field2, field3, id_field, date_field_1, date_field_2 FROM table1, table2 WHERE id_field = $P{id_field} AND date_field1 >= $P{date_field1} AND date_field2 <= $P{date_field2}[/code]

 

Now, in the main report I add each of these four sub-reports in their own Groups, set the parameters up and then it comes to adding the SQL query for the main report. I don't really know what I'm meant to be typing in for the SQL query in the main report. The way I see it the main report doesn't really have to fetch any data since that is handled all by the sub-reports. So these were the following things I tried:

 

1. Running the main report without an SQL query. That totally didn't work (and I'm sure most of you are saying "well, obviously!").

 

2. Running it with a "SELECT 1" statement. This gives me "unknown column name: total_field1". As you can see from above "total_field1" is an alias for the sum of values in field1.

 

3. Running the main report with an SQL query I copied and pasted from one of the sub-reports (the one you see above). When I run the preview like this, each of the subreports gets printed twice, as the SQL query in the main report returns 2 results. Obviously this is not what I want.

 

4. I did the exact same as 3. except I added "LIMIT 1" at the end. This worked, but it does not feel like the correct way of doing it. Because if my main report SQL query gets no results back, NONE of my sub-reports will be printed even if some of them returned some results.

 

5. I came across a solution online which I don't trust either. It says in each of the sub-report elemets in the main report put this piece of code in the "Print When Expression": 

 

new Boolean($V{REPORT_COUNT}.intValue() ==1)

 

This seems to me like again like some sort of hack.

 

So finally after all that information, I'm wondering what the correct way I should implement this. And it would be nice if someone could explain the role of the SQL expression in the main report when it is seemingly not needed because all the data retrieval is handled by the subreport. 

[rajesh.sirsikar]

Hi pluralprnoun

Please check my blog on how to create and implement subreports from main report. http://www.rajeshsirsikar.com/?p=329 Basically you need to have the query in your main report to enter the parameter values. If your answers are not answered from my blog, please let me know, I will help you out.

[marianol]

Have you tried running the main report without any query BUT setting the report properties to for "when No Data Type" = "AllSectionsNoDetail"

This way the main report will render fully and the subreports will fill in the data as expected. SInce you have no detail the subreports should be in the title or summary band.

 

[pluralpronoun]

Hi Rajesh. Thanks for the link. I actually read your blog post before I posted this question! It did help me with the parameter stuff. Unfortunately it doesn't really go into my specific problem. My parameters aren't really the issue, it's rendering the records of the subreport based on the results of the main report. I think marional's answer is closer to what I'm looking for. But if you have any other ideas about this I'd be glad to hear them! Appreciate the help and the blog post!

[pluralpronoun]

Hi Marianol. Thanks for the info. So I removed my main report SQL query, moved all my subreports into the title band (I tried the summary band first but there was all this white space appearing above my subreports which I didn't understand) and tried again. This did seem to work, although the subreports were overlapping each other. I simply set the position of the three subreports under the main report to "Float" and the subreports were automatically pushed down. I'll accept this as my answer but just want to ask really quickly: is this the standard way of doing this? A main report which just renders subreports and needs to take in parameters seems like a pretty standard thing to be doing..... Or is it?