Generate a set of typical reports based on sql query result

[gengon555]

Good day!

I have a task to generate a set of typical reports based on sql query result. That is, I have a query "select col1 from tab1" returning, for example, N values and I have a report filled by sql query "select ... from tab2 where col1=<PARAMETER>". So I need to generate N reports with PARAMETER = each value from the first query. Number of returning values from the first query is varying. How can it be done?

[danoldenkampgmail.com]

If the first query did not require any user input, I would handle this entirely with multiple statements in single SQL query.

There are lots of SQL consultants out there that would be glad to offer their services. (* conflict of interest, I am one.)

If you want to try it yourself, I would suggest trying a Common Table Expressions.

It allows you to have a with statement to make the second query from the first query with a join.

Alternative suggestions are subqueries within the query or temp tables.  Sub queries can be within the select, the from, or the where clause.

If you use a temp table you would need to turn off NoCount as the first response is what is processed by jaspersoft.

a CTE may be simplest.

 

Example: 

;with statement (col1) as

(select col1 from table1)

select col2 fom table 2

join statement on statement.col1 =  table2.col1

 

 

If it requires two sets of parameter inputs, you can feed the results of one into another with jaserpsoft, but it doesn't sound like that's what you are trying to do.

 

[C-Box]

Well it sounds more, that he has a MasterQuery and the results of this are the feed for the actual running reports.

So I would create a TopMost Report with the Query and use a SubReport for running the actual reports. The resulting field values from MasterQuery can be passed as SubReport-Parameters down to the nested SubReport(s) and there used within the SubRepoprt-Queries.

 I guess each SubReport should have it's own page count and so on, so a group at master level would be helpful with flag "StartOnNewPage" and also ResetPageNumber (if he wants to use PageHeader/-footer of MasterReport).

Perhaps also the ReportBook Feature of JasperReports would be helpful, as this is thought for combining several different (Sub)Reports to a kind "Book of Reports" !??!

So let us know what you want to reach! ;-)

hth + regards from Germany

C-Box

[gengon555]

Thanks for your replies!

C-Box, you are right. I've already begun reading about ReportBook, but didn't get how to make the set of report in a dynamic way. I mean, I have a table with set of rooms (result of first query - select roomID from rooms) and a typical report about room visitors (the second query - select ... from Visitors where roomID=PARAMETER). I'm able to make drop-down list to choose the parameter and generate every report manually, but I want to generate report for every value of the first query as parameter for the report automatically, schedule it.

[C-Box]

Not sure what you want with a dropdown or anything like this. But as I understand you run a main query that will return several room-ids in a resultset. Let's say room-id 1 to 10.

So now you want to run a Report that shows each visitors of each single room. So that e.g. 10 different pages will be printed and each of them just shows the visitors of the 10 rooms in our example. So this is what I meant with the MainReport and SubReport logic.

The main report has no actual design and is just responsible to loop through the rooms. (select * from rooms)  In the detail band of the main report you create a SubReport element and pass the current room-id as a SubReport-Parameter let's call "ParentRoomID" with expression $F{roomID}

In you SubReport (an own jrxml.design) you now create a ReportParameter with the same name ("ParentRoomID") and in the SubReport-Query you can use this parameter within your where condition (e.g. Select * from visitors where visitors.room_id = $P{ParentRoomID})

In the SubReport (the jrxml) you now can define the whole layout based on the visitors of the (current) room.

So this is a typical usage scenario of how to call a report x-times based on a (parent) query

You also can define different SubReports for different room types or whatever you could imagine and use a PrintWhenExpression or an If.Then.Else expression based on a certain room field to use different layout if needed... so the limit is the sky ;-)

hth + regards

C-Box.

 

[gengon555]

Well, I got it. I need SubReports :)

Just the last question. If the first query returns room-id 1 to 20 instead of 1 to 10 on some a day, the reports for room-id 11-20 will be added to the Report book automatically, right? I just need to export Report Book to jasper server, schedule and that's all, right?

[C-Box]

As I'm not so familiar with using ReportBooks (as I've used to build "normal" reports in my Jasper-career, so when I start it was version 0.5.3 when I remember right) I can't answer this question for 100% sure - sorry.

But when you've designed one (main) report for getting all rooms and for each room-record a SubReport is called like I suggested above, then of course also all new rooms from 11 to 20 (... even to Integer.MAX I guess) will cause a new SubReport-Run and so you will get 20 different pages/subReports.

Perhaps I will find some time to take TrialAndError a bit with the ReportBook feature to learn about it more ... but the sparetime is rare + the family + other tasks ... :-O

regards

C-Box

[gengon555]

I'll try to learn it by myself and write back.

Thank you for giving me right direction  :)

[gengon555]

I've tested - ReportBook and subReports are what I looked for.

Thank you all for help!