next »

[Jeff W.]

I'm printing a name, fee type, and a fee amount on every row of a report.  I'm sorting by name and I want to print a subtotal under each name group (may have several fee types).  I tried many variations of AFTER GROUP in the report block and I tried many variations of the report layout, but I can't seem to make this work.  I don't see documentation or an example of this.  If this is possible, how?

Example . . .

Name           Fee Type      Fee Amt
-------          -----------      ---------
Doe, John     Jan Fee         25.00
Doe, John     Feb Fee         15.00
                        Total:       40.00

[Romain W.]

Hi Jeff,

I guess you tried to use the GROUP SUM statement, typically called in the AFTER GROUP section? Actually, we don't recommend using that statement, because it reorders the data, and this may drive to performance issues.
The best solution is the one used in the 'Reports' demo: perform calculation on intermediate variable(s) in your AFTER GROUP and provide it (them) in a PRINTX statement (like 'ordertotal' or 'overalltotal' in the demo).

Best regards,
Romain W.

[Jeff W.]

I don't find the Reports demo or project under any of my 4Js installation directories.  Can I access this code online, or can you attach the 4gl and 4rp files that show this?

[Reuben B.]

I don't find the Reports demo or project under any of my 4Js installation directories.  Can I access this code online, or can you attach the 4gl and 4rp files that show this?

The Report demo project can be found either My Documents/My Genero Files/sample/reports/Reports.4pw, or GSTDIR/samples/reports/Reports.4pw.

The first time you started Genero Studio it would've been a link in the Welcome Page but will have dropped off the bottom of the list no doubt. In my training courses, the first exercise is to become familiar with the demo reports.

I am going to contradict something Romain said.  Leave your AFTER GROUP and GROUP SUM in your 4gl code.  Just make sure you have the REPORT defined as ORDER EXTERNAL.  Basically use the strengths of the database to sort your database, have the runner sum your totals for you as it goes.  You shouldn't need to manually keep a running total like the demos do.  In the attached example you will see ...

Code

ORDER EXTERNAL BY x.group, x.code
 
FORMAT 
   BEFORE GROUP OF x.group
      PRINTX x.group
 
   ON EVERY ROW
      PRINTX x.*
 
   AFTER GROUP OF x.group
      LET grp_sum.sales = GROUP SUM(x.sales)
      LET grp_sum.costs = GROUP SUM(x.costs)
      LET grp_sum.count = GROUP COUNT(*)
      PRINTX grp_sum.*

... you can use these values in the AFTER GROUP in the report after the corresponding group detail has been processed.  It is a bit hard to explain but note in the attached report the Group Sum Row is after the Group Code trigger.

Now there was an error in Studio Bz 12567 that meant these values in the AFTER GROUP couldn’t be selected in the expression editor code completion, and appeared in the Document Errors.  With the attached report you should note that it runs fine and hopefully this bug will be fixed in the next version.

[Scott B.]

You can find details for adding subtotals and totals to a report in the GRW documentation page Design How-To:

https://4js.com/online_documentation/fjs-gst-2.20.06-manual-html/reportwriter/User/designhowto.html#Print_Totals

[Scott B.]

You can find details for adding subtotals and totals to a report in the GRW documentation page Design How-To:

https://4js.com/online_documentation/fjs-gst-2.20.06-manual-html/reportwriter/User/designhowto.html#Print_Totals

We are going to update the documentation to include the best practice (AFTER GROUP OF / GROUP SUM) provided by Reuben in this forum topic.

[Jeff W.]

Reuben,

I'm able to unzip your files, but can't open your .4rp file after that.  The error is due to it being created under a different version.  My GRD version is 1.00.06-69057

Is it possible for you to attach/send a version of aftergroup.4rp that I can view?

[Reuben B.]

Hi Jeff,

I created mine using Genero Studio 2.20.06 so a later version than what you have.  There isn't an automated back-port, what you can do is manually edit the .4rp using a texteditor and in one of the  first nodes you will see version="2.00".  Change that to version="1.0".  The file will load but will report some errors but you will be able to see the important thing which is the structure.

The key thing you will need to see is the structure and where the "Group Sum Row" is positioned.  It is positioned as a younger sibling of the"Group Code / On Every Row".  The information fed by the AFTER GROUP is available after all the detail for a GROUP has been sent.

I would recommend you try and stick closer to the later versions. 

Reuben