Is there anyway to muliple the row-total by 12 for the Budget. Is there a method or format to use instead of creating a new column.

TABLE FILE EXPENSES
SUM
TOTALAMOUNT/D15.2M/C AS 'Total'
MAX.BUDGET/C AS 'Budget'
MAX.VARIANCE/C AS 'Variance'
BY JREGION NOPRINT
BY REGION AS 'Region'
ACROSS EXPDATE_MMYY AS ' ' ROW-TOTAL
-*WHERE EXPENSEDATE GE '20071101';
WHERE REGION NE ' ';

You want magic??

not easy....

What do you expect to see and where?
not an extra column?

Can you be a bit more specific.

GG, you can use column reference notation in your formulas.
I cheat; here's how:

TABLE FILE CAR 
SUM SALES   NOPRINT BY COUNTRY
SUM SALES BY COUNTRY ACROSS SEATS  
   COMPUTE TOTSALES/I8=C1; 
   COMPUTE MYNEWFIELD/I8=C1* 1.5; 
END

Susannah thanks for your help but I keep getting an error that says ERROR: MR_UNKNOWN_ERROR -12: Pcb2.: java.net.SocketException: Connection reset

This is my code:

TABLE FILE EXPENSES
SUM
TOTALAMOUNT/D15.2M/C AS 'Total' NOPRINT
MAX.BUDGET/C AS 'Budget' NOPRINT
MAX.VARIANCE/C AS 'Variance' NOPRINT
BY JREGION
-*BY REGION AS 'Region'
SUM
TOTALAMOUNT/D15.2M/C AS 'Total'
MAX.BUDGET/C AS 'Budget'
MAX.VARIANCE/C AS 'Variance'
ACROSS EXPDATE_MMYY AS ' ' ROW-TOTAL
COMPUTE TBUDGET/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET ELSE &TBUDGET;
BY JREGION NOPRINT
BY REGION AS 'Region'
WHERE REGION NE ' ';
ON TABLE COLUMN-TOTAL
END

If I do something similar on the CAR database it works fine

TABLE FILE CAR
SUM
     SALES
	 MAX.SEATS
BY COUNTRY
SUM
SALES
MAX.SEATS
ACROSS MODEL ROW-TOTAL
COMPUTE XXX= IF COUNTRY EQ 'ITALY' THEN 1 ELSE 2;
BY COUNTRY
BY CAR

ON TABLE COLUMN-TOTAL
END

Although the rowtotal result is not correct, it sums the values from the across statement and the first sum together, so the result is the double of the expected.

It doesn't work for me. The problem comes from ON TABLE COLUMN-TOTAL.

comment out the compute statement
run it
does it work?
if so, then move your compute to a DEFINEd field. You're in version 5 so i can't test, but FrankieMartini has version 5 up and running somaybe he can test for you.
Sometimes in 7 you get funny errors when you don't handle your &vars properly. So if it runs w/o the compute, then rethink both the way you calculate that variable, and the way you position it...

I commented out a few things and the problem isn't the compute. The problem is the column-total.

Goff,

I think that your code should have additional BY phrases within it for correct syntax? By the replies that you've had it would appear that this is a 5.3.2 issue as most replies are from those with 7.n.n - Just goes to show the usefulness of the release and platform info!!

TABLE FILE EXPENSES
SUM TOTALAMOUNT/D15.2M/C AS 'Total' NOPRINT
    MAX.BUDGET/C         AS 'Budget' NOPRINT
    MAX.VARIANCE/C       AS 'Variance' NOPRINT
 BY JREGION NOPRINT
 BY REGION  NOPRINT
SUM TOTALAMOUNT/D15.2M/C AS 'Total'
    MAX.BUDGET/C         AS 'Budget'
    MAX.VARIANCE/C       AS 'Variance'
 BY JREGION NOPRINT
 BY REGION  NOPRINT
ACROSS EXPDATE_MMYY      AS ' ' ROW-TOTAL
SUM COMPUTE TBUDGET/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET ELSE &TBUDGET;
 BY JREGION NOPRINT
 BY REGION               AS 'Region'
WHERE REGION NE ' ';
ON TABLE COLUMN-TOTAL
END

I have a version against the GGSALES demo data base which mimics yours as much as I can and will post it tomorrow. It works on 7.6.2 so see if it works on your 5.3.2.

T

Thanks Tony A that works. However there is one problem. In my output I see the row-total twice!
I know that it's because of the second SUM.

This is the code:

DEFINE FILE EXPENSES
EXPDATE_MMYY/MtYY = EXPENSEDATE;
JREGION/A50 = IF REGION EQ 'John Boeckh' THEN 'ZZZZZ' ELSE REGION;
TBUDGET/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET ELSE &TBUDGET;
TVARIANCE/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET - TOTALAMOUNT ELSE &TBUDGET - TOTALAMOUNT;
END

TABLE FILE EXPENSES
SUM
TOTALAMOUNT/D15.2M/C AS 'Total' NOPRINT
MAX.BUDGET/C AS 'Budget' NOPRINT
MAX.VARIANCE/C AS 'Variance' NOPRINT
BY JREGION NOPRINT
BY REGION AS 'Region'
SUM
TOTALAMOUNT/D15.2M/C AS 'Total'
MAX.BUDGET/C AS 'Budget'
MAX.VARIANCE/C AS 'Variance'
BY JREGION NOPRINT
BY REGION AS 'Region'
ACROSS EXPDATE_MMYY AS ' ' ROW-TOTAL
SUM
TOTALAMOUNT/D15.2M/C AS 'Total'
MAX.TBUDGET/C AS 'Budget'
MAX.TVARIANCE/C AS 'Variance'
BY JREGION NOPRINT
BY REGION AS 'Region'
WHERE REGION NE ' ';
ON TABLE COLUMN-TOTAL
END

GGof,

Are you not over complicating this? The same result would be given by -

DEFINE FILE EXPENSES
  EXPDATE_MMYY/MtYY = EXPENSEDATE; 
  JREGION/A50       = IF REGION  EQ 'John Boeckh' THEN 'ZZZZZ' ELSE REGION;
  TBUDGET/D15.2M    = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET ELSE &TBUDGET;
  TVARIANCE/D15.2M  = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET - TOTALAMOUNT ELSE &TBUDGET - TOTALAMOUNT;
END

TABLE FILE EXPENSES
SUM TOTALAMOUNT/D15.2M/C AS 'Total'
    MAX.BUDGET/C         AS 'Budget'
    MAX.VARIANCE/C       AS 'Variance'
 BY JREGION              NOPRINT
 BY REGION               AS 'Region'
ACROSS EXPDATE_MMYY      AS ' ' ROW-TOTAL
WHERE REGION NE ' ';
ON TABLE COLUMN-TOTAL
END

If I have got the wrong interpretation then apologies, but please write what you want in words (a mini spec!) and I'll try and assist you.

T

Tony, that was my original code and yes that does work but I think you're misunderstanding the issue.

I have my dates going across and underneath each date there's the Actual, Budget and Variance and at the end of each year there's a FYTD Total for Actual, Budget and Variance. The budget is always $5000 and at the end of the year it should $60000. Now the data gets added every month so let's say I have data for Nov and Dec only the FYTD Total shouldn't be $10000, it should be $60,000.

Right now what I'm getting is I have Nov data with the budget at $5000 and then I have a FYTD data for $60,000 but that gets repeated twice, so I see the same set twice (Total, Budget, Variance with the same information twice (the $60000 info)).

Often, if you can't squeeze out the report using BY and ACROSS and FOR and RECAP and ROW-TOTAL and COLUMN-TOTAL and all the other lovely commands, it is simpler to prepare the report columns and rows in HOLD files and then report on one or more than one HOLD file.

In your situation, it might be easier to prepare the column total in a HOLD file by summarizing the data by Year and making the month 13. Then use the MORE command in the report table file to add this 13th month to your other 12 and don't use the COLUMN-TOTAL command.

Sometimes you have to (and luckily can) get creative.

G

the problem you face, was mentioned in my earlier reply.

this might help

DEFINE FILE CAR
SALESD/D15 = SALES;
SALESX/D15=SALES;
END
TABLE FILE CAR
SUM
     SALESX NOPRINT
     PCT.SALESX/D6.2% NOPRINT
BY COUNTRY
SUM
     SALESD AS 'SALES'
     PCT.SALESD/D6.2% AS '%'
BY COUNTRY
ACROSS SEATS AS ''
     COMPUTE
          TOTSALES/D12.2 = SALESX;
     COMPUTE
          TOTPERC/D6.2% = PCT.SALESX;

END

In te last computes you point to the first fields.

you have to play with this in your report.

The other (better??) solution is to do this with FML. You have more control over your fields and you can make intercolumn calcultations like in excel.

FrankDutch that doesn't work. I'm gonna try Francis's method and see what happens.

Francis, I don't quite understand your solution.
Webfocus doesn't allow 13 as the month, am I correct? So how can I use the 13 month to add to the other 12 months. I don't understand how the coding would work!

It won't allow 13 as a month if the field you're ACROSSing with is a date field - it would if it was an alphanumeric field. I suggest manipulating the data before the final report because you would like unorthodox things to happen, like multiplying the monthly Budget number by 12 to get a Total Budget for the year. Envision what you want the report columns and rows to be, then prepare the data to suit the report if COLUMN-TOTAL, ROW-TOTAL etc, won't do the job. I have reports where I have to prepare five or six HOLD files because the user wants total rows and total columns that cannot be automatically generated by WEBFOCUS. I prepare the data, then use MORE FILE to combine all the independent HOLD files, then create the report.

Ok this thing is drive me mental! lol
I've munipulated the data so that it will give me the 5000 budget and the total 60000. However, now I can't get the dates to go across as the column headings and at the end show FYTD Total as the column heading.

DEFINE FILE EXPENSES
EXPDATE/MtYY = EXPENSEDATE;
JREGION/A50 = IF EXPENSEREGION EQ 'John Boeckh' THEN 'ZZZZZ' ELSE EXPENSEREGION;
TOTAL/D15.2M = TOTALAMOUNT;
BUDGET/D15.2M = IF JREGION EQ 'John Boeckh' THEN &JBUDGET ELSE &BUDGET;
VARIANCE/D15.2M = IF JREGION EQ 'John Boeckh' THEN &JBUDGET - TOTALAMOUNT ELSE &BUDGET - TOTALAMOUNT;
END

TABLE FILE EXPENSES
SUM
TOTAL/C AS 'Actual'
MAX.BUDGET/C AS 'Budget'
MAX.VARIANCE/C AS 'Variance'
EXPENSEDATE
BY JREGION NOPRINT
BY EXPENSEREGION
ACROSS EXPDATE AS ' '
WHERE EXPENSEREGION NE ' ';
ON TABLE HOLD AS TOTEXPENSES
END
-RUN

DEFINE FILE EXPENSES
EXPDATE/MtYY = EXPENSEDATE;
JREGION/A50 = IF EXPENSEREGION EQ 'John Boeckh' THEN 'ZZZZZ' ELSE EXPENSEREGION;
TOTALYTD/D15.2M = TOTALAMOUNT;
BUDGETYTD/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET ELSE &TBUDGET;
VARIANCEYTD/D15.2M = IF JREGION EQ 'John Boeckh' THEN &TJBUDGET - TOTALAMOUNT ELSE &TBUDGET - TOTALAMOUNT;
FYTD/A20 = ' ';
END

TABLE FILE EXPENSES
SUM
TOTALYTD/C AS 'FYTD Actual'
MAX.BUDGETYTD/C AS 'Budget'
MAX.VARIANCEYTD/C AS 'Variance'
EXPENSEDATE
BY JREGION NOPRINT
BY EXPENSEREGION
ACROSS FYTD AS 'FYTD Total'
WHERE EXPENSEREGION NE ' ';
WHERE EXPENSEDATE FROM '20071101' TO '20081031';
ON TABLE HOLD AS ALLEXPENSES
END
-RUN

JOIN EXPENSEREGION IN TOTEXPENSES TO EXPENSEREGION IN ALLEXPENSES AS J2

TABLE FILE TOTEXPENSES
PRINT
TOTAL
BUDGET
VARIANCE
TOTALYTD
BUDGETYTD
VARIANCEYTD
BY EXPENSEREGION
ON TABLE COLUMN-TOTAL
END

After creating the HOLD file that contains the ACROSS, add this command ?FF hold-file-name

You will get a list of column names that are in the HOLD file - you probably won't like what you see! ACROSS creates column names made up of the original column-name and the data value of the across field, so you may get something like BUD200704 as a column-name. If you make the across column just month, you would get more predictable names, like BUDGET04, then in your report, you would PRINT the column-names:

PRINT
BUDGET01
BUDGET02
...
BY...

My columns don't have a weird name. They're like this.

EXPENSEREGION E01 A50
TOTAL E02 D15.2M
BUDGET E03 D15.2M
VARIANCE E04 D15.2M
EXPENSEDATE E05 YYMD
TOTAL E06 D15.2M
BUDGET E07 D15.2M
VARIANCE E08 D15.2M
EXPENSEDATE E09 YYMD
FILENAME= ALLEXPENSES
EXPENSEREGION E01 A50
TOTALYTD E02 D15.2M
BUDGETYTD E03 D15.2M
VARIANCEYTD E04 D15.2M
EXPENSEDATE E05 YYMD

GGof,

I see what you mean now (I think ), you have to have all the dates showing irrespective of whether you have any data for that period (in your case - month).

If that is the case then I would suggest a touch of McGyver to force all the periods through followed by an extract of all the possible REGION values, reading each REGION into a virtual array and then using these variables to forces rows using either the BY REGION ROWS &var1 OVER &var2 etc. or using them in FRL (FML or what else it is currently called).

I shall have to add the code in another post as I have written it on another machine since starting this reply

T

I hope that you can follow this -

FILEDEF prdmas DISK prddata.mas
-RUN
-WRITE prdmas FILE=keyvalue, SUFFIX=FOC
-WRITE prdmas SEGNAME=ROOT_SEG, SEGTYPE=S1, $
-WRITE prdmas FIELD=PRD_YYM, ALIAS=PERIOD, FORMAT=YYM, $
-*-WRITE prdmas FIELD=BUDGET, ALIAS=BUDGET, FORMAT=D12.2, $
-RUN
CREATE FILE prddata
MODIFY FILE prddata
COMPUTE BUDGET/D12.2 = 5000;
FIXFORM PRD_YYM/A6
DATA
199605
199606
199607
199608
199609
199610
199611
199612
199701
199702
199703
199704
END
-RUN
DEFINE FILE GGSALES
  DATE_YYM/YYM = DATE;
  YEAR/YY      = DATE;
END
TABLE FILE GGSALES
BY REGION
ON TABLE SAVE AS GGOFRGNS
ON TABLE SET HOLDLIST PRINTONLY
END
-RUN
-SET &Iter = &LINES;
-SET &Cnt = 1;
-REPEAT Loop1 &Iter TIMES;
-READ GGOFRGNS &Region&Cnt.EVAL.A11.
-SET &Cnt = &Cnt + 1;
-Loop1
TABLE FILE GGSALES
SUM COMPUTE AMOUNT/D12.2 = DOLLARS / 100;
 BY DATE_YYM
 BY REGION
WHERE YEAR EQ 1996
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS GGOFDATA FORMAT XFOCUS INDEX DATE_YYM
END
-RUN
JOIN CLEAR *
JOIN LEFT_OUTER PRD_YYM IN prddata TO MULTIPLE DATE_YYM IN GGOFDATA AS J1
TABLE FILE prddata
SUM AMOUNT
    COMPUTE BUDGET/D12.2 = IF REGION EQ 'West' THEN 5100 ELSE 5000;
    COMPUTE VARIANCE/D12.2 = AMOUNT - BUDGET;
ACROSS PRD_YYM ROW-TOTAL AS 'FYTD Total'
FOR REGION
-SET &Cnt = 1;
-REPEAT Loop2 &Iter TIMES;
'&Region&Cnt.EVAL' LABEL LAB&Cnt.EVAL OVER
-* Here we ensure that any missing values in the budgets are corrected
RECAP LAB&Cnt.EVAL(7,47,4) = LAB&Cnt.EVAL(3); OVER
-* This will get the FYTD budget correct
RECAP LAB&Cnt.EVAL(51) = 12 * LAB&Cnt.EVAL(3); OVER
-* Now we recalculate all the variance values
RECAP LAB&Cnt.EVAL(8,52,4) = E(*,*-3) - E(*,*-1); OVER
-SET &Cnt = &Cnt + 1;
-Loop2
""
END
-RUN

The first bit creates a temporary master file, creates the FOCUS file to which it relates and populates it with data.
Next I get all the possible Region values into a "virtual" variable array and keep track of the number of uniques.
The next bit builds some test data from the GGSALES table ensuring that the date range is incomplete as far as the date range is concerned (only 1996 data for a range of May 1996 to Apr 1997).
The JOIN ensures all periods are included in the report in a McGyveresque manner.
The report uses FML to build the grid of data. Because the data is incomplete, I perform RECAPs to populate the necessary columns of BUDGET and VARIANCE in all twelve periods plus the row totals!

Over to you .....

T

Tony to be honest with you this is all new to me. I'm new to webfocus.
But I ran your program and what i notices is that the budget for the FYTD is still totaling $5000 and the variance is empty for all of them except 1 month.

Tony A, I just realized that in your previous message you said I have to have all the dates showing irrespective of whether I have data for that period. That's not the case. I just need the FYTD Total Budget to show 60,000 for the whole year even if I only have Nov, Dec, Jan data which is 3 months in that case. So regardless the FYTD Total Budget should be 60,000.