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: AFTER GROUP subtotals  (Read 15708 times)
Jeff W.
Posts: 31


« on: April 06, 2010, 04:31:24 am »

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.
Four Js
Posts: 48


« Reply #1 on: April 06, 2010, 04:44:53 pm »

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.
Posts: 31


« Reply #2 on: April 06, 2010, 09:11:37 pm »

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.
Four Js
Posts: 1047


« Reply #3 on: April 07, 2010, 02:23:50 am »

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
  1. ORDER EXTERNAL BY x.group, x.code
  2.  
  3. FORMAT
  4.   BEFORE GROUP OF x.group
  5.      PRINTX x.group
  6.  
  7.   ON EVERY ROW
  8.      PRINTX x.*
  9.  
  10.   AFTER GROUP OF x.group
  11.      LET grp_sum.sales = GROUP SUM(x.sales)
  12.      LET grp_sum.costs = GROUP SUM(x.costs)
  13.      LET grp_sum.count = GROUP COUNT(*)
  14.      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.



* aftergroup.zip (3.6 KB - downloaded 925 times.)

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Scott B.
Four Js
Posts: 25


« Reply #4 on: April 07, 2010, 05:49:58 pm »

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.
Four Js
Posts: 25


« Reply #5 on: April 10, 2010, 01:34:53 am »

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.
Posts: 31


« Reply #6 on: April 13, 2010, 09:45:28 pm »

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.
Four Js
Posts: 1047


« Reply #7 on: April 14, 2010, 01:13:00 am »

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

Product Consultant (Asia Pacific)
Developer Relations Manager (Worldwide)
Author of https://4js.com/ask-reuben
Contributor to https://github.com/FourjsGenero
Pages: [1]
  Reply  |  Print  
 
Jump to:  

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines