This is more than meets the eye.
The question is: I need to use the negative number in the calculation/total but display it as a positive; i.e. without the negative sign, when the Account Type is EXPENSE
This, not very well formatted, copy and paste of the report shows an attempt. The data after the EXPENSE prints correctly, but the total is double what it should be. The total should be $.00 as shown in the next column.


Title Account Type ACCTD2 Accepted Budget YTD Activity Encumberance Remaining Balance
Ferris State University REVENUE 189,253,828.64 $189,253,828.64 $147,852,814.26 $.00 $41,401,014.38
Ferris State University EXPENSE 189,253,828.64 -$189,253,828.64 -$131,054,671.70 -$31,248,584.96 -$26,950,571.98

TOTAL
378,507,657.28 $.00 $16,798,142.56 -$31,248,584.96 $14,450,442.40

There could be a simpler method but I'd do in your case would be to create 2 fields:

- An actual field that carries the "real" value to be used (-189,253,828.64) - NOPRINT
- A "display" field which constains the abosulte value of the previous one (see ABS() for reference)

The trade off here is that you won't be able to use SUBTOTAL/TOTAL anymore but will have to resort to SUBFOOT to position each column totals where you want it.

- Neftali.

This sample code using the CAR table illustrates the concept:

DEFINE FILE CAR
ACCTD2/A7 WITH BODYTYPE = IF BODYTYPE EQ 'SEDAN' THEN 'REVENUE' ELSE 'EXPENSE';
SORT_SQ/I2 = IF ACCTD2 EQ 'REVENUE' THEN 1 ELSE 2;
BUDGET/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN SALES*200 ELSE (-1 * SALES*200);
ACCEPTED/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN RETAIL_COST ELSE (-1 * RETAIL_COST);
ACCEPTED_DSP/D12.2 = ABS(ACCEPTED);
END
TABLE FILE CAR
SUM
      ACCEPTED     NOPRINT
      ACCEPTED_DSP AS 'Accepted'
      BUDGET       AS 'Budget'
BY SORT_SQ NOPRINT
BY ACCTD2
ON TABLE SUBTOTAL AS 'WRONG TOTAL'
ON TABLE SUBFOOT
"RIGHT TOTAL<+0><TOT.ACCEPTED<TOT.BUDGET"
ON TABLE SET STYLE *
TYPE=GRANDTOTAL, COLOR='RED',$
TYPE=TABFOOTING, HEADALIGN=BODY, JUSTIFY=RIGHT, COLOR='BLUE',$
TYPE=TABFOOTING, OBJECT=TEXT, JUSTIFY=LEFT,$
END

ACCEPTED refers to the actual amounts (both positive and negative) you're report is dealing with. This field is not displayed in the body of your report but is used to calculate the "right" total at the end.
ACCEPTED_DSP is the field you used to display the expected values in your report with *no* negative sign. That field is not supposed to be used in totals as it only has positive values.

You can apply the concept to your own report; it requires an extra piece of work (use of SUBFOOT instead of TOTAL/SUBTOTAL) but gives more control (relatively speaking) in what you actually want to display.

Hope that helps,
- Neftali.

Thanks. WOrking on. I'm having trouble positioning the totals. Spot markers don't seem to do anything anymore <+20>

Which output format are you using? If it's exclusively HTML or EXL2K then a HEADALIGN=BODY in the style sheet definition on TYPE=GRANDTOTAL and/or TYPE=SUBFOOT is usually sufficient (see my example which does exactly that).

If PDF, then you may play with POSITION={column} for each ITEM in your SUBFOOT or GRANDTOTAL.

Using spotmarkers can be quite cumbersome sometimes (most times? ) but HEADALIGN and/or POSITION have proven to be very useful for me in the past and the only markers I need are <+0> (no offset).

- Neftali.

Rick, just a little "caveat" you should keep in mind before it's too late. Just be aware of the fact that, when declaring your style definitions, SUBFOOT can only have BORDERs around the whole block and not around each internal column or line, as opposed to SUBTOTAL and GRANDTOTAL which give you more control in styling (but not that much in their content).

As I said before, it's a trade off. My users pretty much accepted to live without those fancy borders as long as the information presented is consistent and accurate. Other environments may not be as forgiving

- Neftali.

Thanks much. I'm having trouble positioning the SUBFOOT and I need that because that is what the report uses. I would like to use column notation to position, P4.

Here is my code

DEFINE FILE CAR
ACCTD2/A7 WITH BODYTYPE = IF BODYTYPE EQ 'SEDAN' THEN 'REVENUE' ELSE 'EXPENSE';
SORT_SQ/I2 = IF ACCTD2 EQ 'REVENUE' THEN 1 ELSE 2;
BUDGET/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN SALES*200 ELSE (-1 * SALES*200);
ACCEPTED/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN RETAIL_COST ELSE (-1 * RETAIL_COST);
ACCEPTED_DSP/D12.2 = ABS(ACCEPTED);
END
TABLE FILE CAR
SUM
     CAR
     MODEL
     BODYTYPE
     ACCEPTED NOPRINT
     ACCEPTED_DSP AS 'Accepted'
     BUDGET AS 'Budget'
BY SORT_SQ NOPRINT
BY ACCTD2
ON TABLE SUBFOOT
"RIGHT TOTAL<+0><TOT.ACCEPTED<+0><TOT.BUDGET"
ON TABLE SUBTOTAL AS 'WRONG TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Legal',
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='COURIER',
     SIZE=9,
$
TYPE=GRANDTOTAL, COLOR='RED',$
-*TYPE=TABFOOTING, HEADALIGN=BODY, COLOR='BLUE',$
-*TYPE=TABFOOTING, OBJECT=TEXT, JUSTIFY=LEFT,$
TYPE=TABFOOTING, LINE=1, OBJECT=FIELD, ITEM=2, POSITION=3.5,$
ENDSTYLE
END

Rick,

Use HEADALIGN=BODY with spotmarkers -

DEFINE FILE CAR
  ACCTD2/A7 WITH BODYTYPE = IF BODYTYPE EQ 'SEDAN' THEN 'REVENUE' ELSE 'EXPENSE';
  SORT_SQ/I2 = IF ACCTD2 EQ 'REVENUE' THEN 1 ELSE 2;
  BUDGET/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN SALES*200 ELSE (-1 * SALES*200);
  ACCEPTED/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN RETAIL_COST ELSE (-1 * RETAIL_COST);
  ACCEPTED_DSP/D12.2 = ABS(ACCEPTED);
END
TABLE FILE CAR
 SUM CAR
     MODEL
     BODYTYPE
     ACCEPTED     NOPRINT
     ACCEPTED_DSP AS 'Accepted'
     BUDGET       AS 'Budget'
BY SORT_SQ NOPRINT
BY ACCTD2
ON TABLE SUBFOOT
"RIGHT TOTAL <+0> <+0> <+0> <TOT.ACCEPTED<TOT.BUDGET"
ON TABLE SUBTOTAL AS 'WRONG TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  UNITS=IN, PAGESIZE='Legal', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=REPORT, GRID=OFF, FONT='COURIER', SIZE=9, $
TYPE=GRANDTOTAL, COLOR='RED',$
TYPE=TABFOOTING, HEADALIGN=BODY, JUSTIFY=RIGHT, $
ENDSTYLE
END

T

You guys are sharp. So it appears that this technique only works with PDF. Is there a way to also be able to output in Excel, EXL2K?

EXL2K look same as HTML to me. PDF have RIGHT TOTAL in side two cols.

Kofi

quote:

So it appears that this technique only works with PDF. Is there a way to also be able to output in Excel, EXL2K?

You probably did not read my reply on that yesterday (or maybe it was not that clear?):

quote:

Which output format are you using? If it's exclusively HTML or EXL2K then a HEADALIGN=BODY in the style sheet definition on TYPE=GRANDTOTAL and/or TYPE=SUBFOOT is usually sufficient (see my example which does exactly that).

If PDF, then you may play with POSITION={column} for each ITEM in your SUBFOOT or GRANDTOTAL.

In summary, use POSITION when dealing with PDF. Use HEADALIGN=... when HTML and EXL2K. In either case use spot markers (<+0>) to separate your SUBFOOT components so you can individually reference them in the style sheet definition via the ITEM keyword.

- Neftali.

Sorry, if I add CAR MODEL the 'Right' numbers don't fall under the correct columns.

You need spot markers in your subfoot logic to account for all columns being displayed in the report. See the code below:

DEFINE FILE CAR
ACCTD2/A7 WITH BODYTYPE = IF BODYTYPE EQ 'SEDAN' THEN 'REVENUE' ELSE 'EXPENSE';
SORT_SQ/I2 = IF ACCTD2 EQ 'REVENUE' THEN 1 ELSE 2;
BUDGET/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN SALES*200 ELSE (-1 * SALES*200);
ACCEPTED/D12.2 = IF ACCTD2 EQ 'REVENUE' THEN RETAIL_COST ELSE (-1 * RETAIL_COST);
ACCEPTED_DSP/D12.2 = ABS(ACCEPTED);
END
TABLE FILE CAR
SUM
     CAR
     MODEL
     BODYTYPE
     ACCEPTED NOPRINT
     ACCEPTED_DSP AS 'Accepted'
     BUDGET AS 'Budget'
BY SORT_SQ NOPRINT
BY ACCTD2
ON TABLE SUBFOOT
"RIGHT TOTAL<+0> <+0> <+0> <+0><TOT.ACCEPTED<+0><TOT.BUDGET"
ON TABLE SUBTOTAL AS 'WRONG TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Legal',
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='COURIER',
     SIZE=9,
$

TYPE=GRANDTOTAL, COLOR='RED',$
TYPE=TABFOOTING, HEADALIGN=BODY, COLOR='BLUE',$
-*TYPE=TABFOOTING, OBJECT=TEXT, JUSTIFY=LEFT,$
TYPE=TABFOOTING, LINE=1, OBJECT=FIELD, ITEM=2, POSITION=3.5,$
ENDSTYLE
END  

PAGE 1



ACCTD2 CAR MODEL BODYTYPE Accepted Budget
REVENUE PEUGEOT 504 4 DOOR SEDAN 114,086.00 36,604,000.00
EXPENSE ALFA ROMEO 2000 SPIDER VELOCE ROADSTER 59,118.00 -5,080,000.00

WRONG TOTAL 173,204.00 31,524,000.00
RIGHT TOTAL 54,968.00 31,524,000.00