Typically with reports you have a single query that returns a lot of data that mostly gets printed in the detail area. The report-writing tools I've seen are geared towards this type of report.
I'm finding myself writing some summary reports where there isn't just one query with one where clause that returns a lot of data. On these reports there are many queries with different where clauses that each returns just one number. The report is just one page and each number goes in a specific spot.
What I'm doing to accomplish this is to write a query that is huge. First, I select one row then I have a left join with its own SQL for each additional number I need to retrieve, which becomes it's own column in the results. I would like to get away from having this huge query. Also, I just ran into a situation where mysql was basically complaining that the query was too large; it was something about too deeply-nested subqueries, but removing one of the joins fixed it.
It would help a lot if I could make each query a separate sub dataset and populate a textfield with the resulting number.
Could a scriptlet maybe be used to perform the query and populate the resulting number in a variable?
Do other report-writing programs/libraries make this easier?
