next »

[Candy M.]

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

IMPORT FGL libgre
SCHEMA fms
MAIN
 
        DEFINE
        p_country RECORD LIKE country.*,
        report_handler  om.SaxDocumentHandler
 
        database mt11
 
        #LET report_handler = fgl_report_createProcessLevelDataFile("test1")
 
        #START REPORT rp_test1 TO XML HANDLER report_handler
        START REPORT rp_test1 to "test1.txt"
 
        DECLARE country_data CURSOR FOR
                SELECT * FROM country
                ORDER BY cntryid
 
        BEGIN WORK
        FOREACH country_data INTO p_country.*
                OUTPUT TO REPORT rp_test1(p_country.*)
        END FOREACH
        COMMIT WORK
 
        FINISH REPORT rp_test1
 
END MAIN
REPORT rp_test1 (r_country)
 
        DEFINE
        r_country record like country.*
 
        OUTPUT
                LEFT MARGIN 0
                TOP MARGIN 0
                BOTTOM MARGIN 0
                PAGE LENGTH 1
                LEFT MARGIN 0
 
        ORDER EXTERNAL BY r_country.cntryid
 
        FORMAT
 
        ON EVERY ROW
                PRINT r_country.*
 
END REPORT
 
 

Using the PAGE LENGTH of 1, the above code would produce a file of 265 lines without any headers/footers.
Example output:

Code

AD                  ANDORRA
AE                  UNITED ARAB EMIRATES
AF                  AFGHANISTAN
AG                  ANTIGUA AND BARBUDA
AI                  ANGUILLA
AL                  ALBANIA
AM                  ARMENIA
AN                  NETHERLANDS ANTILLES
AO                  ANGOLA
AQ                  ANTARCTICA
AR                  ARGENTINA
AS
AT                  AUSTRIA
AU                  AUSTRALIA
AW                  ARUBA
AX                  ÅLAND ISLANDS
AZ                  AZERBAIJAN
...etc
 

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

https://4js.com/2004/REPORT[/url]">
  <PageHeader pageNo="1"/>
  <Group>
    <OnEveryRow>
      <Print>
        <Item name="r_country.cntryid" type="CHAR(20)" value="AD                  " caption="Cntryid"/>
        <Item name="r_country.des" type="VARCHAR(40)" value="ANDORRA" caption="Des"/>
      </Print>
      <PageTrailer/>
    </OnEveryRow>
  </Group>
  <Group>
    <OnEveryRow>
      <PageHeader pageNo="2"/>
      <Print>
        <Item name="r_country.cntryid" type="CHAR(20)" value="AE                  " caption="Cntryid"/>
        <Item name="r_country.des" type="VARCHAR(40)" value="UNITED ARAB EMIRATES" caption="Des"/>
      </Print>
      <PageTrailer/>
    </OnEveryRow>
  </Group>
  <Group>
    <OnEveryRow>
      <PageHeader pageNo="3"/>
      <Print>
        <Item name="r_country.cntryid" type="CHAR(20)" value="AF                  " caption="Cntryid"/>
        <Item name="r_country.des" type="VARCHAR(40)" value="AFGHANISTAN" caption="Des"/>
      </Print>
      <PageTrailer/>
    </OnEveryRow>
  </Group>
.....
  <Group>
    <OnEveryRow>
      <PageHeader pageNo="264"/>
      <Print>
        <Item name="r_country.cntryid" type="CHAR(20)" value="ZM                  " caption="Cntryid"/>
        <Item name="r_country.des" type="VARCHAR(40)" value="ZAMBIA" caption="Des"/>
      </Print>
      <PageTrailer/>
    </OnEveryRow>
  </Group>
  <Group>
    <OnEveryRow>
      <PageHeader pageNo="265"/>
      <Print>
        <Item name="r_country.cntryid" type="CHAR(20)" value="ZW                  " caption="Cntryid"/>
        <Item name="r_country.des" type="VARCHAR(40)" value="ZIMBABWE" caption="Des"/>
      </Print>
      <PageTrailer/>
    </OnEveryRow>
  </Group>
  <OnLastRow/>
</Report>
 

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.]

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.]

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.]

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.]

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.]

Candy,

With requirements of

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 ...

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.]

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.]

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

[Candy M.]

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

Candy