I have a FOCEXEC that is working except for one last part...

On the primary control break subfoot, I want to print TWO lines -- one with the subtotals, and one with percentages based on these subtotals.

Layout is:

GROUPNAME1     CATEGORY1   DOLLARS
GROUPNAME1     CATEGORY2   DOLLARS
GROUPNAME1     CATEGORY3   DOLLARS
SUBTOTAL GROUPNAME1        <DOLLARS>
Percent to total:          ST.DOLLARS/TOT.DOLLARS

GRANDTOTAL                 <TOT.DOLLARS>

(error message: (FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ST.PDollars)

Here's what I've tried to do which results in an error telling me I don't have access to the ST.xxxx of the field, whether I reference it as ST.Dollars or ST.PDOLLARS -- but I can reference the TOT.Dollars or TOT.PDOLLARS:

SET ALL=ON, NODATA=''
-SET &ECHO=ALL;

SQL SQLMSS SET SERVER SERVERNAME
SQL SQLMSS                                                                      
EX DB.dbo.PROC '&variables';

TABLE FILE SQLOUT
PRINT *          
ON TABLE HOLD AS REPDATA
END

DEFINE FILE REPDATA
PDOLLARS/D12M = Dollars/1000;
-* has to display in thousands, not sure if 
-* that's significant
END

TABLE FILE REPDATA
PRINT 
         GROUPNAME
	PDOLLARS/D12M AS 'Dollar Amount'
COMPUTE PYEAR/D5% = (ST.Dollars / TOT.Dollars) * 100; NOPRINT

HEADING CENTER
"Report Date: &DATEMDYY"
" "

BY GROUPNAME NOPRINT
ON GROUPNAME SUBFOOT
" <+0> Total <+0> <GROUPNAME <+0> <ST.PDOLLARS "
"Percent to total year <+0> <PYEAR "

ON TABLE SET PAGE-NUM OFF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLEMODE FIXED
ON TABLE SUBFOOT
"<+0> Grand Total <TOT.PDOLLARS <+0> "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
	UNITS=IN,
	PAGESIZE='LETTER',
	SQUEEZE=ON,
	LEFTMARGIN=0.20,
	RIGHTMARGIN=0.20,
	TOPMARGIN=0.10,
	BOTTOMMARGIN=0.10,
	ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
	GRID=ON,
	FONT='ARIAL',
	SIZE=8,
	COLOR='BLACK',
	BACKCOLOR='NONE',
	STYLE=NORMAL,
	LINEBREAK='CRLF',
$
TYPE=TITLE,STYLE=BOLD,$
TYPE=HEADING, STYLE=BOLD, JUSTIFY=CENTER, BACKCOLOR=RGB(170 213 255),$
TYPE=SUBFOOT,STYLE=BOLD,BACKCOLOR=ORANGE,$

TYPE=SUBFOOT,STYLE=BOLD,LINE=1,ITEM=1,OBJECT=TEXT,JUSTIFY=LEFT,POSITION=GROUPNAME,$
TYPE=SUBFOOT,STYLE=BOLD,LINE=1,ITEM=2,OBJECT=FIELD,JUSTIFY=RIGHT,POSITION=PDOLLARS,$

TYPE=SUBFOOT,STYLE=BOLD,LINE=2,ITEM=1,OBJECT=FIELD,POSITION=PDOLLARS,$

TYPE=TABFOOTING,STYLE=BOLD,BACKCOLOR=LIGHT BLUE,$
TYPE=TABFOOTING,STYLE=BOLD,LINE=1,ITEM=1,OBJECT=FIELD,POSITION=PDOLLARS,$

ENDSTYLE

END

SET EMPTYREPORT=ON
-RUN
-IF &RECORDS EQ 0 GOTO NORPT;
-EXIT
-NORPT

This message has been edited. Last edited by: snoopmole,

Snoop,

Try, based on another recent post:

DEFINE FILE REPDATA
CTR/I5 WITH GROUPNAME = 1;
END
TABLE FILE REPDATA
SUM TOT.SALES NOPRINT
PRINT 
        GROUPNAME
	PDOLLARS/D12M AS 'Dollar Amount'
        CTR NOPRINT
COMPUTE PYEAR/D5% = (C3/(C1/C4)) * 100; NOPRINT
.
.
"Percent to total year <+0> <ST.PYEAR "

This uses column notation where C1 is the TOT.SALES, C3 is PDOLLARS and C4 is CTR.

But I don't want to calculate percentages off the current line. I want to calculate percentages on the subtotal line. How can I specify that my percentage is based on subtotal / total for a given figure?

I'm already doing something similar, actually right before this particular linein the focexec, where I compute two additional columns for the field that represent "current line / total value" to show percent for this record compared to the total dollars. What I'm trying to do is get the same type of computation with subtotal / total.

I don't understand how, in your example, C1 refers to total, C3 refers to the subtotal, and C4 refers to CTR. I'm not following that.

Have you tried this?

I tested this approach recently and it works.

Column notation is based on the order of the columns in the report.(If you are not showing the complete report, then the column notation may change)

This message has been edited. Last edited by: Alan B,

I did try this. I get: (FOC281) ALPHA ARGUMENTS IN PLACE WHERE NUMERIC ARE CALLED FOR

Perhaps my confusion is from the fact that I have a lot more columns in my report... I'm actually doing the same sort of subtotal/total percentage calculation across four dollar columns on the report, and there are about 5 other columns in the report.

so in my condensed exammple, I am printing 2 columns... GROUPNAME (C1 right?) and PDOLLARS (C2).

My report has 6 text columns, Four dollar columns, and 4 percentage columns calculated from the line dollar items. I don't think I'm understanding the column numbering scheme, and how you get that subtotal is C3, and which column is C4.

I am sorry for my confusion -- it's just not making sense to me.

For column notation, just count the PRINT/SUM columns as they would appear from left to right in the report. Ignore BY fields.

The COMPUTE calculates the %, right, so in the SUBFOOT you are doing a ST. on the COMPUTE field, which adds the % together to give the result.

Excuse my slowness. What is the purpose and meaning of CTR? Do I have to create this column in my DEFINE area? I don't understand how C1/C4 = total and C3 = Subtotal.

I have experimented with the various numbers and such. Let me see if I understand this. C* notation refers to the columns as they display on the screen, including NOPRINT.

If i have a report with columns text1, text2, dollar1, dollar2, calcperc1, dollar3, dollar4, calcperc2, calcperc3, calcperc4... which has two subtotal levels based on two different sorted column values...

text1 (1) text2 (2) dollar1 (3) dollar2 (4) calcperc1 (5) dollar3 (6) dollar4 (7) calcperc (8) calcperc3 (9) calcperc4 (10)

SUBTOTAL1 dollar1sub (11) dollar2sub (12) dollar3sub (13) dollar4sub (14)

SUBTOTAL2 dollar1sub (15) dollar2sub (16) dollar3sub (17) dollar4sub (18)

Is that the correct numbering? I'm still not clear what you did with the CTR and C1/C4 to get a subtotal / total percentage calculation. I only do the subtotal/total percentage calculation on the top level control break.

This message has been edited. Last edited by: snoopmole,

Okay.

CTR is a safety net to avoid multiple instances creating a summing effect on the total. If there is summing effect on the total amount, it divides the total to bring it back to the original value. Should only be needed with the verb SUM and RECOMPUTE. As I am unsure of your exact code, I put it in for safety. You may not need it, but I show an example later.

You don't say if text1 and text2 are BY fields or PRINT fields. If they are PRINT they are C1 and C2, if BY they are not referenced by column notation and dollar1 is C1.

If you look at this example:

TABLE FILE CAR
SUM    TOT.SALES
PRINT      CAR
           SALES
COMPUTE PERCENT/D6.2% = (C3/C1)*100;
BY COUNTRY
ON COUNTRY SUBFOOT
"<ST.PERCENT"
END

the SUM TOT.SALES is C1, CAR is C2 and SALES is C3. COUNTRY is a BY field as has no column notation.
The PERCENT is a calculation at row level, and the SUBFOOT is an ST.PERCENT, a subtotal of the PERCENT at the COUNTRY level.
The output from this is correct.

If you wanted to recompute the percentage at a sort level:

TABLE FILE CAR
SUM    TOT.SALES
           CAR
           SALES
COMPUTE PERCENT/D6.2% = (C3/C1)*100;
BY COUNTRY RECOMPUTE
BY CAR
END

This would give correct row calculation, but subtotal would be incorrect, because the TOT.SALES would be multiplied by the number of instances. So CNTR is used to divide the TOT.SALES by the number of instances to get the percentage correct:

DEFINE FILE CAR
CNTR/I4 WITH COUNTRY =1;
END
TABLE FILE CAR
SUM    TOT.SALES
           CAR
           SALES
           CNTR
COMPUTE PERCENT/D6.2% = (C3/(C1/C4))*100;
BY COUNTRY RECOMPUTE
BY CAR
END

which is again correct.

Thanks for your patience. This all makes sense now. I have it working! I really appreciate it.