Subscribe for automatic updates: RSS icon RSS

Login icon Sign in for full access | Help icon Help
Advanced search

Pages: [1]
  Reply  |  Print  
Author Topic: Question about generating reports so there is no header/footer  (Read 3370 times)
Candy M.
Posts: 128


« on: November 21, 2017, 04:42:56 am »

We are starting to explore the capabilities of the GRE.
In 4gl, we were able to generate a report with no header/footer (so the report would be continuous), in the follow way:
Code
  1. IMPORT FGL libgre
  2. SCHEMA fms
  3. MAIN
  4.  
  5.        DEFINE
  6.        p_country RECORD LIKE country.*,
  7.        report_handler  om.SaxDocumentHandler
  8.  
  9.        database mt11
  10.  
  11.        #LET report_handler = fgl_report_createProcessLevelDataFile("test1")
  12.  
  13.        #START REPORT rp_test1 TO XML HANDLER report_handler
  14.        START REPORT rp_test1 to "test1.txt"
  15.  
  16.        DECLARE country_data CURSOR FOR
  17.                SELECT * FROM country
  18.                ORDER BY cntryid
  19.  
  20.        BEGIN WORK
  21.        FOREACH country_data INTO p_country.*
  22.                OUTPUT TO REPORT rp_test1(p_country.*)
  23.        END FOREACH
  24.        COMMIT WORK
  25.  
  26.        FINISH REPORT rp_test1
  27.  
  28. END MAIN
  29. REPORT rp_test1 (r_country)
  30.  
  31.        DEFINE
  32.        r_country record like country.*
  33.  
  34.        OUTPUT
  35.                LEFT MARGIN 0
  36.                TOP MARGIN 0
  37.                BOTTOM MARGIN 0
  38.                PAGE LENGTH 1
  39.                LEFT MARGIN 0
  40.  
  41.        ORDER EXTERNAL BY r_country.cntryid
  42.  
  43.        FORMAT
  44.  
  45.        ON EVERY ROW
  46.                PRINT r_country.*
  47.  
  48. END REPORT
  49.  
  50.  
Using the PAGE LENGTH of 1, the above code would produce a file of 265 lines without any headers/footers.
Example output:
Code
  1. AD                  ANDORRA
  2. AE                  UNITED ARAB EMIRATES
  3. AF                  AFGHANISTAN
  4. AG                  ANTIGUA AND BARBUDA
  5. AI                  ANGUILLA
  6. AL                  ALBANIA
  7. AM                  ARMENIA
  8. AN                  NETHERLANDS ANTILLES
  9. AO                  ANGOLA
  10. AQ                  ANTARCTICA
  11. AR                  ARGENTINA
  12. AS
  13. AT                  AUSTRIA
  14. AU                  AUSTRALIA
  15. AW                  ARUBA
  16. AX                  ÅLAND ISLANDS
  17. AZ                  AZERBAIJAN
  18. ...etc
  19.  
So if I remove the comments on lines 11 and 13 and then comment line 14, it would produce an XML file like this:
Code
  1. http://www.4js.com/2004/REPORT[/url]">
  2.  <PageHeader pageNo="1"/>
  3.  <Group>
  4.    <OnEveryRow>
  5.      <Print>
  6.        <Item name="r_country.cntryid" type="CHAR(20)" value="AD                  " caption="Cntryid"/>
  7.        <Item name="r_country.des" type="VARCHAR(40)" value="ANDORRA" caption="Des"/>
  8.      </Print>
  9.      <PageTrailer/>
  10.    </OnEveryRow>
  11.  </Group>
  12.  <Group>
  13.    <OnEveryRow>
  14.      <PageHeader pageNo="2"/>
  15.      <Print>
  16.        <Item name="r_country.cntryid" type="CHAR(20)" value="AE                  " caption="Cntryid"/>
  17.        <Item name="r_country.des" type="VARCHAR(40)" value="UNITED ARAB EMIRATES" caption="Des"/>
  18.      </Print>
  19.      <PageTrailer/>
  20.    </OnEveryRow>
  21.  </Group>
  22.  <Group>
  23.    <OnEveryRow>
  24.      <PageHeader pageNo="3"/>
  25.      <Print>
  26.        <Item name="r_country.cntryid" type="CHAR(20)" value="AF                  " caption="Cntryid"/>
  27.        <Item name="r_country.des" type="VARCHAR(40)" value="AFGHANISTAN" caption="Des"/>
  28.      </Print>
  29.      <PageTrailer/>
  30.    </OnEveryRow>
  31.  </Group>
  32. .....
  33.  <Group>
  34.    <OnEveryRow>
  35.      <PageHeader pageNo="264"/>
  36.      <Print>
  37.        <Item name="r_country.cntryid" type="CHAR(20)" value="ZM                  " caption="Cntryid"/>
  38.        <Item name="r_country.des" type="VARCHAR(40)" value="ZAMBIA" caption="Des"/>
  39.      </Print>
  40.      <PageTrailer/>
  41.    </OnEveryRow>
  42.  </Group>
  43.  <Group>
  44.    <OnEveryRow>
  45.      <PageHeader pageNo="265"/>
  46.      <Print>
  47.        <Item name="r_country.cntryid" type="CHAR(20)" value="ZW                  " caption="Cntryid"/>
  48.        <Item name="r_country.des" type="VARCHAR(40)" value="ZIMBABWE" caption="Des"/>
  49.      </Print>
  50.      <PageTrailer/>
  51.    </OnEveryRow>
  52.  </Group>
  53.  <OnLastRow/>
  54. </Report>
  55.  

So using XML, it will produce 265 pages instead of 265 continuous "on every row".

If this is the expected behavior, I have the following questions:
1) Is there any way to produce an XML file with continuous rows (no headers/footers) in XML using the report constructs.
2) If not, if we produced the XML another way using the XML methods, would we be able to get the report engine to accept it.  But maybe GRE would interpret this for the Excel and put each of those pages on one line. (And actually the XML that was produced doesn't look the same from page 1 to page 2)

I haven't tried number 2 and will experiment with it.   My goal is to be able to send an XML file to the report engine that will produce an Excel spreadsheet.   I do know that there are other ways to produce Excel using the channel and the java method, but I wanted to explore this other way.

This is 3.10.09.

Thanks,
Candy
Alex G.
Four Js
Posts: 114


« Reply #1 on: November 21, 2017, 10:09:00 am »

Hi Candy,

Thanks for the precise description of what you have tried.
What you have done is fine and can be used with slight modifications. The "Report Writer" way of creating Excel spreadsheets would additionally require a report design file (a .4rp).
You would start by creating a report design file (a .4rp file) and loading it via a call to fgl_report_loadCurrentSettings(). For Excel output you might choose to start from the "Simple List" document. A description of the 4GL program can be found in the documentation here: http://4js.com/online_documentation/fjs-gst-manual-html/#gst-topics/c_grw_BDLfile_004.html#c_grw_BDLfile_004
For the design file I suggest to use the List document template as described in this YouTube video: https://www.youtube.com/watch?v=ZAIXnSRxl0U
The video is slightly outdated so that some things can be done in a simpler way now but it will serve.

This report can then be used to produce various formats (e.g. PDF or XLS(X)). The default behavior will create a sheet per page but by calling the function fgl_report_configureXLS(X)Device() you can request page merging which will result in one, possible huge, sheet.
Creating and configuring Excel output is described here: http://4js.com/online_documentation/fjs-gst-manual-html/#gst-topics/c_grw_outputoptions_008.html

Make sure that you set the "Section" property to the RTL expression "Runtime.producingExcelOutput()?Port.FirstPageHeader:Port.AnyPageHeader" as described in the chapter "Output a single-sheet document to Excel" here: http://4js.com/online_documentation/fjs-gst-manual-html/#gst-topics/c_grw_xls_single_sheet.html
This is the option that suppresses the headers on subsequent pages so that it appears only once at the top of the spreadsheet. Using a visibility condition you can also disable the header for Excel spreadsheets entirely. If the report is to be used for Excel only then you could of course also use a report design that has no header to begin with or remove the header from the "List" document template.

Kind regards,

A. Geller 



 
Candy M.
Posts: 128


« Reply #2 on: November 21, 2017, 04:10:46 pm »

Thank you Alex.

Looking further at the documentation it looks like I may be able to accomplish my final goal.

I want to take a user input query, create XML output for that, create an .4rp file for that and from that create an Excel spreadsheet.   I want the application to do that and not have the to use the report designer or studio for every single query.   I want to generalize the process.

In our ERP application, we currently store queries in the database to be used in a third-party report writer (we had to go that route because FourJs didn't have the report writer completed yet).   What we have found from requests from customers, is that they frequently want a flat file of data in an Excel spreadsheet, then they
can format it the way they want.   So I want to take these queries and see if we can come up with a scheme to have our application do all the work.

I can do the first steps, take the query and analyze and produce XML (if it is similar to format above).   But what I'm lacking is producing the .4rp by the application.   It looks like the GRE provides a utility GenerateReport that will do that.   The only thing I am lacking is the ".xsd" which I think I could generate as well (do you know if the BDL has the capability of generating an .xsd from a query?).   If you have a sample .xsd that I could go by as to what is expected and recommend which template to use.   We aren't going to have any totals etc., just a row a data where the columns vary.  I'm hoping I will be able to accomplish this.  And do you have and example of using GenerateReport.   Thank you for your help and guidance.
Candy
Alex G.
Four Js
Posts: 114


« Reply #3 on: November 21, 2017, 04:42:34 pm »

Hi Candy,

if it has to be dynamic then you might want to look at the demonstration report "Order Report" and look at the "Generic List" example.
This report prompts the user with a dynamic dialog showing all fields in the report and lets him choose fields that should appear in the report.
Internally the template mechanism is used to create a list report containing these fields.
The Excel output of this report should suit your needs.
The relevant code snippet from OrderReport.4gl is this:

    IF filename == "Generic List.4rp" THEN
        IF NOT fgl_report_loadCurrentSettings(NULL) THEN #note that no .4rp design file is needed
            EXIT PROGRAM
        END IF
        CALL promptForFieldsToPrint("OrderReport.rdd","report_all_orders") RETURNING retval,fieldNames
        IF NOT retval THEN
            RETURN NULL
        END IF
        CALL fgl_report_setAutoformatType("FLAT LIST") #enables generic reports
        CALL fgl_report_configureAutoformatOutput(NULL,8,NULL,"Order List",fieldNames,NULL)
        CALL fgl_report_configurePageSize("a4length","a4width")
        CALL fgl_report_configureXLSDevice(NULL,NULL,FALSE,NULL,NULL,NULL,TRUE) #preserve spaces and merge pages in XLS output
        CALL fgl_report_setTitle("Order List")
 
Note that the only requirement is an .rdd file compiled from your report source. You don't need a .4rp design file.

You can use GenerateReport but this is simpler (it uses the template mechanism internally and discards the .4rp after the run)

Best regards,

Alex
Candy M.
Posts: 128


« Reply #4 on: November 21, 2017, 05:29:46 pm »

Thank you Alex.   I do want to go the GenerateReport route as I am not going to be generating the .rdd file from a Report Source.   I'll check with USA support to see if they can provide me with the information I need.

Thanks,
Candy
Reuben B.
Four Js
Posts: 622


« Reply #5 on: November 22, 2017, 02:39:41 am »

Candy,

With requirements of

Quote
I want to take a user input query, create XML output for that, create an .4rp file for that and from that create an Excel spreadsheet.   I want the application to do that and not have the to use the report designer or studio for every single query.   I want to generalize the process.

I know you said ...

Quote
I do know that there are other ways to produce Excel using the channel and the java method, but I wanted to explore this other way.

... but I would strongly recommend the Apache POI Java method.  The main reason being is you have finer control over the output.

I modified my Apache POI example so you can see how simple it is.  I added this test program https://github.com/FourjsGenero/fgl_apache_poi/blob/master/fgl_excel_generic_test.4gl
note the ... sql_to_excel(sql, filename, header) function.  Pass the SQL and it will write the output to Excel.

Reuben

PS Back in mid 2000's when I first implemented this generic, enter an SQL, get data, place it in Excel, as we had a generic zoom window, we just re-utilised that.  Provide a means for the user to enter a SQL, output it in the generic zoom window, and from there the user simply had to Select All, Copy, and Paste into Excel.  The only tricky bit was the Select All and Copy.  The GDC's "Copy Visible Table" was impractical so did some tricky code to get the whole array into the clipboard, that tricky code was able to be subsequently removed when Multi-Row Select came along.  So if you already have a generic display array, just allow multi row select and the user can copy and paste into Excel.  (look at ON ACTION copyall here https://github.com/FourjsGenero/fgl_zoom/blob/master/fgl_zoom.4gl)






Candy M.
Posts: 128


« Reply #6 on: November 22, 2017, 03:30:08 am »

Thank you Reuben.   I think we will have use for that for some custom code that  we did for a customer and for one of our applications that use the DDE.

But I still want to explore the GenerateReport option because if we can generate a .4rp, then the customer would have some means of making their own modification.
We could even keep a database of templates for the customer or just store them in a special template directory.  So user enters a query (and this user would most likely be one of our implementers/trainers), query is stored in the database, we generate a .4rp for that query.   The .4rp can be duplicated and the user can change the .4rp to their tastes.

I have an email into support and hopefully they can get some further documentation on the command or I'll resort to trial and error to see if I can get it to work.

Thanks for responding,
Candy
Alex G.
Four Js
Posts: 114


« Reply #7 on: November 22, 2017, 11:05:00 am »

Hi Candy,

Find attached a shell script generate.sh that will expand the attached template ListTemplate.4rt into the attached "Result.4rp" containing all fields from "main.rdd".
The file main.rdd is obtained by calling "fglcomp --build-rdd main.4gl". To get the attached result I took the 4GL report that you posted in this thread and named it "main.4gl".
The documentation doesn't state it, but it is possible to pass a .rdd file name for the "schemaFileName" parameter.
I am using the "-stdin" option for the arguments to "generatereport" so that it is easier to shield the options from the shell.
Best regards

Alex

* generate.sh.txt (0.31 KB - downloaded 120 times.)
* ListTemplate.4rt.txt (5.11 KB - downloaded 106 times.)
* Result.4rp.txt (3.61 KB - downloaded 123 times.)
Candy M.
Posts: 128


« Reply #8 on: November 22, 2017, 02:32:56 pm »

Thank you so much Alex!  I appreciate it very much!

Candy
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines