I’m currently working on integration of YARG into our Django-based web application (so, not a CUBA-related question really). YARG is called from command line.
My question is - how do I make a report with subsections (similar to Bookstore report for example), using only one SQL query?
In other words, Bookstore report has two bands - Shop and its sub-band Book.
SQL query for Shop fetches the list of book shops (“id”, “name”, “address”) and for each record found:
- inserts its named range of cells (“Shop”) from the template into the report;
- inserts Book sub-band, which results in -
2.1) Book SQL query being executed (“author”, “name”, “price”, “count”);
2.2) for each record in that query, “Book” named range is inserted into the report.
So Book query is executed as many times as there are shops, which is wasteful.
Let’s suppose I wrote an SQL that fetches the following fields:
“id”, “name”, “address” – these refer to the shop
"author", “name”, “price”, “count” – and these - to the book
Each record from this set would represent a certain book in a certain shop. Just one SQL query instead of N+1.
How would I create the same report from this query? I can imagine making a “master band” with the query and using Groovy queries in the sub-bands to fetch data from it, BUT:
- the master band is still a band and it would be added to my report and I don’t want that;
- the seb-bands would be invoked for each record in the master band (which means, for each shopbook combination), having access to that record only, and not to the whole dataset. And in order to, say, extract the list of shops from the bookshop dataset you have to iterate over the whole set, I’m afraid.