I have a report which looks like this:

Sequence  NSN            Item                                 Quantity   Price   Total Price 
HR0200    5610002504677  Portland Cement, High Early Strnth   40.00      $5.96   $238.40 
HR0200    5610ZZ0010001  Aggregate Coarse Local Procurement   2.00       $.00   
HR0200    5610ZZ0010002  Aggregate, Fine, Local Procurement   3.00       $5.00   $15.00 
HR0200    5660ZZ0640019  Post, Fence, Rnd, Galv, 2.5", 13 Ft  101.00     $55.00  $5,555.00 
HR0300    5660007204530  Fencing,Wire                         22.00      $293.26 $6,451.72

I would like to add a grand total under the 'Total Price' column. Is this even possible?

So it would look something like this:

Sequence  NSN            Item                                 Quantity   Price   Total Price 
HR0200    5610002504677  Portland Cement, High Early Strnth   40.00      $5.96   $238.40 
...
HR0300    5660007204530  Fencing,Wire                         22.00      $293.26 $6,451.72
                                                                 Grand Total:    $12,259.72

I've looked at the SUBFOOT command, but I can't make heads or tails of all this WF stuff. . .

Thanks,
Dan

Here is my FEX:

-* File FacilityMaterial.fex

-* 1.- First, JOINs (Important, JOIN clears previous DEFINEs)
JOIN
 FACILITYNSN.FACILITYNSN.NSN AND FACILITYNSN.FACILITYNSN.UMCODE IN facilitynsn
 TO MULTIPLE NSN.NSN.NSN AND NSN.NSN.UMCODE IN nsn AS J0
 END
-* 2.- DEFINE over host table
DEFINE FILE FACILITYNSN
  FacilityProperCase/A35=LCWORD(35, FACILITYNSN.FACILITY.DESCRIPTION, FacilityProperCase);
END
-* 3.- Query.
TABLE FILE FACILITYNSN
PRINT
     FACILITY NOPRINT
     UMCODE NOPRINT
     SEQUENCE AS 'Sequence'
     NSN

COMPUTE Item/A35 = LCWORD(35, NOMENCLATURE, Item);
     QUANTITY/P12.2 AS 'Quantity'
     PRICE/P14.2CM AS 'Price'

COMPUTE TotalPrice/D12.2CSM = PRICE * QUANTITY; AS 'Total Price'
HEADING
"Material for <FacilityProperCase"
"Facility ID: <FACILITY "
FOOTING
"Report: &FOCFEXNAME "
WHERE FACILITY EQ '&FACILITY';
ON TABLE SET CSS ON
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     LEFTMARGIN=0.500000,
     RIGHTMARGIN=0.500000,
     TOPMARGIN=0.500000,
     BOTTOMMARGIN=0.500000,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     BORDER=1,
     FONT='ARIAL',
     SIZE=10,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
     RIGHTGAP=0.125000,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=DATA,
     COLOR='WHITE',
     BACKCOLOR=( 'SILVER' 'GRAY' ),
$
TYPE=DATA,
     COLUMN=N3,
     STYLE=NORMAL,
     TARGET='_self',
     FOCEXEC=FACILITYRESOURCE(FACILITY=N1 SEQUENCE=N3),
$
TYPE=DATA,
     COLUMN=N4,
     STYLE=NORMAL,
     FOCEXEC=NSNDETAIL(NSN=N4 UMCODE=N2),
$
TYPE=DATA,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=TITLE,
     SIZE=10,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 128 255),
     STYLE=-UNDERLINE+BOLD,
$
TYPE=TITLE,
     COLUMN=N3,
     STYLE=NORMAL,
     TARGET='_self',
     FOCEXEC=FACILITYRESOURCE(FACILITY=N1 SEQUENCE=N3),
$
TYPE=TITLE,
     COLUMN=N4,
     FOCEXEC=NSNDETAIL(NSN=N4 UMCODE=N2),
$
TYPE=TITLE,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=14,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=14,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=14,
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     SIZE=14,
     STYLE=BOLD,
$
TYPE=FOOTING,
     LINE=1,
     JUSTIFY=RIGHT,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=5,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=FOOTING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=10,
     STYLE=NORMAL,
$
TYPE=SUBHEAD,
     BACKCOLOR='SILVER',
$
TYPE=SUBFOOT,
     BACKCOLOR='SILVER',
$
TYPE=SUBTOTAL,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     BACKCOLOR=RGB(0 128 255),
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     COLOR='WHITE',
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=ACROSSVALUE,
     COLUMN=ROWTOTAL(1),
     STYLE=BOLD,
$
TYPE=ACROSSTITLE,
     SIZE=10,
     COLOR='WHITE',
     BACKCOLOR=RGB(0 128 255),
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
     TOPGAP=0.013889,
     BOTTOMGAP=0.027778,
$
TYPE=REPORT,
     COLUMN=N3,
     SQUEEZE=0.555556,
$
ENDSTYLE
END

This message has been edited. Last edited by: Dan Morphis,

Dan,

After WHERE FACILITY EQ '&FACILITY'; add -

ON TABLE RECOMPUTE TotalPrice AS 'Grand Total:'

Also add JUSTIFY=RIGHT to your style sheet section for GRANDTOTAL -

TYPE=GRANDTOTAL,
BACKCOLOR='SILVER',
STYLE=BOLD,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
JUSTIFY=RIGHT,
$

dan, i'm thinking that you're going to want to COLUMN-TOTAL that Revenue field. When using Summarize, the formula gets repeated, and in Price * Quantity calculations, that would mutliply the sum of price times the sum of quantity. Well, the sum of quantity is fine, but the sum of price would be meaningless.
so ON TABLE COLUMN-TOTAL QUANTITY TotalPrice
leaving PRICE out of the command.

Susannah and Tony,
I tried both of your suggestions, however when I save my FEX, DevStudio removes the new code.

I should have mentioned in my original post that I'm using DevStudio (And the accompying server) version 7.1.1

Dan,

If your new code is being removed on save then I take it you are using the report painter? Where are you saving it from and how are you opening it? (e.g. have you got the procedure editor tool as default and then saving from the edit tab)

I haven't had any real trouble with 7.1.1 DS apart from the undo function, if this turns out to be the "norm" for 7.1.1 then I might have to resort to editing from Windows explorer or stick with DS 5.3.2!

T

quote:

Originally posted by Tony A:
If your new code is being removed on save then I take it you are using the report painter? Where are you saving it from and how are you opening it? (e.g. have you got the procedure editor tool as default and then saving from the edit tab)

This happens to me when I use the source editor in the report painter.

I was able to get a grand total on the report, but it doesn't look very good. I added a column to group by (In my case, the facility id), then on the insert menu choose "Subtotal", and finally de-selected the columns I didn't need the total for.

But, that gets me something that looks like this (Imagine this is a HTML table):

+-----------+--------+--------+--------+------------+
| data      | data   | data   | data   | data       |
+-----------+--------+--------+--------+------------+
|Total      |
|61050YB    |
+-----------+--------+--------+--------+------------+
|           |        |        |        | $65,930.56 |
+-----------+--------+--------+--------+------------+

The '61050YB' represents the facility id and I haven't been able to turn that off. . .

-dan

Dan,

You have experienced the wonderful GUI that removes code that it can not handle even though it is correct syntactically. This is one of the frustrations I have with the GUI and why I resort to hand coding every time .

Try adding comments to the code (using the text editor) to describe what the process is attempting to achieve and then try and reopen it in the painter tool. You get a warning and all the comments are moved to the top of the fex. Very useful (NOT) and is the subject of an NFR (new feature request).

If you Take your original code and add the components that I suggested then you will have basically what you want. However, you will have to enter it via the text editor to achieve it.

T

p.s. Don't give up on the GUI, it does get better each release. Just don't expect it (the GUI) to be able to do everything you want it to do.

Dan,

You specified facility_id as the field on which to do the sub-total. We AUTOMATICALLY include the value, so the report shows to WHICH sort value the sub-total refers.

There is a 'trick' to remove the value from the 'TOTAL'. Add a new DEFINE for a field with a CONSTANT value of blank (' '). Let's call that field BLANK.

Now, add BLANK as a sort (noprint), immediately after facility_id, so it becomes:

BY FACILITY_ID BY BLANK NOPRINT

and change the SUBTOTAL field from facility_id to BLANK.

Every time facility_id changes, since it's a higher sort than BLANK, we treat it as if BLANK also changed. When the subtotal line prints, it will STILL print the value, but in this case the value is ' ', which doesn't display.

Hi all,

Heard from our senior programming management, removing of code is a known issue. Please contact Customer Support Service and open a case regarding the issue so that the case will be forwarded to programming for fix. The phone number for Customer Support Service is 1-800-736-6130, or you can utilize the online system InfoResponse.

Thanks to all.

Cheers,

Kerry