iReport: sending a foundset or doing everything in SQL?

Classic Servoy
1

Hi everybody,

I’m trying to create a report which will display the holidays stats of the subordinates (including the subordinates or each subordinates) of the current user.
So first I need to find all the subordinates and then for each of them get their holidays stats.

In Servoy, it’s very easy for me to get all these data. However, I don’t really know how to do with iReport.
I think the best solution would be to create a foundset that I send to iReport, but I need to merge results from several foundsets for that as I’m getting the stats from several tables.
Is it possible? :roll:

The other solution would be to do everything in SQL in iReport. But to date I only used queries in iReport and not stored procedure or other complex techniques and I don’t know if it is possible to use them.

2

Foobrother,

There are some pro’s and con’s to both ways.

A report based on an sql query can be more performant if the report is about very large result sets.
For instance for a report on orders with order_details you would write 1 query that joins all orders with details in 1 go.
When using a foundset the orders would be loaded in chunks and the order_details would be queried per order.
Additionaly there is some overhead of client-server communication because in the foundset-case intermediate data is sent from server to client.
If the report will always be on a limited set of data this should not be an issue.

On the other hand, when you are defining a complex report in terms of business logic, you will have to implement the logic twice in the sql-case.
Once in your solution (assuming you will need part of the same logic in the UI) and once in the queries for the report.
In the foundset-case you can reuse all calculations, aggregates, relations, global methods in the report so maintenance will be a lot easier.

Hope this helps,

Rob

3

Thanks for the tips!

My report query should generate a small amount of data (100 rows max). So using foundset seems to be a good solution.
However, I don’t know how to create a foundset with data from different tables.
Because for each person I need data from table A and data from table B as well. Then I send this composed foundset to iReport. Is that right?

4

This is documented and covered in the jasper report plugin forum under the google code site.

http://groups.google.com/group/servoy-j … rts-plugin

Basically, you will specify the base foundset when running the runReport statement, then specify fields from the other tables based on that foundset. So if I wanted a report of order details, the runReport statement would have a foundset of orderdetails, but I can specify the fields from orders that I want to include, I would use the relation in the field name for the jasper report.

example: orderdetails_to_orders.orderdate
better example (jrxml for jasper):

Hope this helps

Jason

5

Ok, I see now.

Last question: I assume it’s not possible to test a report using in iReport?

If I have more questions I’ll post them on the Google Group then.
Thx :D