Subreports with Basic code and "power filtering"
Posted: Tue Jul 31, 2018 7:39 pm
Problem: Base reports are not embeddable in Writer, even when you save them to disk as separate Writer files. You can't link parts of saved reports to master documents nor Writer sections. In theory, the "old style report" of Apache OpenOffice without report builder extension creates sections in report documents but these are difficult to distinguish from each other. The report builder does not create any sections at all.
The attached database contains some dummy data, a filter table, a query, a form and 2 versions of the same report derived from the query, one "old style report" and one report builder report.
For testing these reports are stored in subfolders of the reports container:
Folder1/OldStyle/Report1
Folder1/ReportBuilder/Report1
Both versions are based on query "qReport1" which is almost trivial if you are familiar with "power filtering" (search this forum for "power filtering").
It reports summed up values for each category of every person where some group number equals "Filter"."INT1" in the row where "Filter"."FID"=1. If "Filter"."FID" is Null, the whole unfiltered report will be generated.
Open the form. It shows the "Persons" table with a group column where you can enter integer numbers from 0 to 255. Equal numbers will be printed together into the same output document. The groups could be stored in another table, it could be names or anything else. For simplicity, I just added an integer field, so you have something to play with. Just refresh the subform after you changed the grouping.
The little form with the list box lets you select one of the distinct group numbers and writes it into row #1 of the filter table.
The "open form" button opens the filtered report or the whole report if the Null entry has been selected.
Apart from the trivial "open form" button, this works pretty well and reliable without any macro code as demonstrated a hundred times on this forum.
How can I split the record in pieces that can be used with Writer?
The embedded Basic macro is not trivial and it requires some customization when you want to transfer it to your own project.
First there are some hard coded strings:
1) The hierarchical report name, including any folders and subfolders, for instance Folder1/ReportBuilder/Report1
2) A SELECT statement which selects the distinct category values. In the example this is: SELECT DISTINCT "GRP" FROM "Persons"
3) An UPDATE statement with an unnamed parameter (unquoted question mark): UPDATE "Filter" SET "INT1"=? WHERE "FID"=1
First thing the macro does is this: it deletes all stand-alone documents in <path_of_Base_document>/Folder1/ReportBuilder/Report1_???.odt before it dumps the new set of subreports.
The macro walks through the record set returned by the select statement and uses its integer "GRP" value to substitute the first integer parameter of the update statement. In other words: it modifies the filter criterion.
Then it runs the report and saves it as a stand-alone document <path_of_Base_document>/Folder1/ReportBuilder/ with a 3 digit suffix representing the group and .odt.
Finally it closes the report and start all over again with the next value from the record set.
In order to transfer this, you have to adjust the three hard coded strings and rewrite the part where setInt(param, value) substitutes the parameter with some value. May be you substitute text values or dates rather than integers.
May be you have to substitute more than one parameter.
You have to find your own way to create unique file names. ReportName_001.odt works fine with one integer parameter and with up to 1000 groups but naming the files becomes more difficult with many parameters.
May be you don't like the approach with the "mirrored subfolders". Just use a hard coded folder name for your report files.
May be you don't want to delete all existing report files before creating the new ones. Not doing so, may leave some outdated reports on your disk.
The attached database contains some dummy data, a filter table, a query, a form and 2 versions of the same report derived from the query, one "old style report" and one report builder report.
For testing these reports are stored in subfolders of the reports container:
Folder1/OldStyle/Report1
Folder1/ReportBuilder/Report1
Both versions are based on query "qReport1" which is almost trivial if you are familiar with "power filtering" (search this forum for "power filtering").
Code: Select all
SELECT "Persons"."N" AS "Person", "Categories"."N" AS "Category", SUM( "Data"."V" ) AS "Sum (Values)"
FROM "Data", "Categories", "Persons", "Filter"
WHERE "Data"."CID" = "Categories"."ID"
AND "Data"."PID" = "Persons"."ID"
AND ( "Persons"."GRP" = "Filter"."INT1" OR "Filter"."INT1" IS NULL )
AND "Filter"."FID" = 1
GROUP BY "Persons"."N", "Categories"."N"Open the form. It shows the "Persons" table with a group column where you can enter integer numbers from 0 to 255. Equal numbers will be printed together into the same output document. The groups could be stored in another table, it could be names or anything else. For simplicity, I just added an integer field, so you have something to play with. Just refresh the subform after you changed the grouping.
The little form with the list box lets you select one of the distinct group numbers and writes it into row #1 of the filter table.
The "open form" button opens the filtered report or the whole report if the Null entry has been selected.
Apart from the trivial "open form" button, this works pretty well and reliable without any macro code as demonstrated a hundred times on this forum.
How can I split the record in pieces that can be used with Writer?
The embedded Basic macro is not trivial and it requires some customization when you want to transfer it to your own project.
First there are some hard coded strings:
1) The hierarchical report name, including any folders and subfolders, for instance Folder1/ReportBuilder/Report1
2) A SELECT statement which selects the distinct category values. In the example this is: SELECT DISTINCT "GRP" FROM "Persons"
3) An UPDATE statement with an unnamed parameter (unquoted question mark): UPDATE "Filter" SET "INT1"=? WHERE "FID"=1
First thing the macro does is this: it deletes all stand-alone documents in <path_of_Base_document>/Folder1/ReportBuilder/Report1_???.odt before it dumps the new set of subreports.
The macro walks through the record set returned by the select statement and uses its integer "GRP" value to substitute the first integer parameter of the update statement. In other words: it modifies the filter criterion.
Then it runs the report and saves it as a stand-alone document <path_of_Base_document>/Folder1/ReportBuilder/ with a 3 digit suffix representing the group and .odt.
Finally it closes the report and start all over again with the next value from the record set.
In order to transfer this, you have to adjust the three hard coded strings and rewrite the part where setInt(param, value) substitutes the parameter with some value. May be you substitute text values or dates rather than integers.
May be you have to substitute more than one parameter.
You have to find your own way to create unique file names. ReportName_001.odt works fine with one integer parameter and with up to 1000 groups but naming the files becomes more difficult with many parameters.
May be you don't like the approach with the "mirrored subfolders". Just use a hard coded folder name for your report files.
May be you don't want to delete all existing report files before creating the new ones. Not doing so, may leave some outdated reports on your disk.