I'd like to automate the generation of reports, except for the UI (for report options). Is anyone aware of some code or framework that uses dynamic buffers and queries to build the results in a temp table, and a second piece that takes those results and outputs them into dynamic frames?
We need to also provide the ability to run reports against multiple databases, with totals across databases (we already do this), and also submit reports as jobs to a queue (run on the server). We can add these pieces later. I'm not expecting to find something that has everything we're looking for at the get go.
Here's a high level picture of what I'm thinking...
Any thoughts? Thanks.
Dan
We need to also provide the ability to run reports against multiple databases, with totals across databases (we already do this), and also submit reports as jobs to a queue (run on the server). We can add these pieces later. I'm not expecting to find something that has everything we're looking for at the get go.
Here's a high level picture of what I'm thinking...
Code:
[FONT=Courier New]/* Program calls are the same as before, with no parameters, because the batch
job id is defined as a global variable (giBatchJob) */
run moend/baddebt.p.[/FONT]
[FONT=Courier New]/* NOTE: A persistent procedure maintains the temp-tables that are dynamically
created and populated by the internal procedures defined within the
same procedure. The persistent procedure and it's internal procedures
do all the work related to the temp tables, eliminating the need to
clone blocks of code in every report program. */[/FONT]
[FONT=Courier New]/*******************/
/* moend/baddebt.p */
/*******************/[/FONT]
[FONT=Courier New]/* moend/baddebt-ui.p does the following:
- defines and manages the UI
- builds "report options" temp table records from the frame widgets,
allowing for more than one frame if necessary.
+ run buildopt-frame (xhFrame1)
+ run buildopt-frame (xhFrame2) */[/FONT]
[FONT=Courier New]if giBatchJob = 0 then /* interactive */
run moend/baddebt-ui.p.
else /* batch */
run buildopt-batch. /* builds "report options" temp table records from the
single batch job record */[/FONT]
[FONT=Courier New]/* additional "where" clause conditions, defined by the programmer are also
stored in the "report options" temp table (4 params, comma separated) */
run buildopt-prog (xsField, xsValue, xsDataType, xsCondType).[/FONT]
[FONT=Courier New]/* builds the query string for the dynamic query, based on the "report options"
temp table values */
run build-query.[/FONT]
[FONT=Courier New]/* generates the results of the query, storing the data in one or more "results"
temp tables (also consolidated results) */
run build-results.[/FONT]
[FONT=Courier New]/* builds the report layout for each frame on a report (comma separated param)
storing all the data in a "frame format" temp table (Future: we could define
these records in a permanent database record, instead of building them for
each run of the report -- then we only need to change the data, not the
program, to modify the layout) */
run buildfmt ("xfHead", xsType1, xsLabel1, xsField1, xsFormat1, "").
run buildfmt ("xfSubHead", xsType2, xsLabel2, xsField2, xsFormat2, "").
run buildfmt ("xfMain", xsType3, xsLabel3, xsField3, xsFormat3, "").
run buildfmt ("xfSubTot", xsType4, xsLabel4, xsField4, xsFormat4, xsTot4).
run buildfmt ("xfTot", xsType5, xsLabel5, xsField5, xsFormat5, xsTot5).
run buildfmt ("xfConsol", xsType6, xsLabel6, xsField6, xsFormat6, xsTot6).[/FONT]
[FONT=Courier New]/* writes the output to a text file based on the "frame format" table */
run write-results.[/FONT]
[FONT=Courier New]/* Run the cleanup of memory and objects prior to running the viewer or other */
run cleanup.[/FONT]
[FONT=Courier New]if giBatchJob = 0 then /* interactive */
run view-results. /* run the text viewer */
else
run batch-results. /* update queue data for user browsing */[/FONT]Any thoughts? Thanks.
Dan
