In my budgeting application a report with many columns is presented to the user as a very wide EXL2K spreadsheet.
- The first 16 columns after the row captions contain actual data for 12 months, arranged in 4 groups of columns each showing 3 months of historic values with a quarter total. [to parapharase into FOCUSese: "PRINT JAN FEB MAR COMPUTE Q1=JAN+FEB+MAR; ACROSS QUARTER" but I can't use that because the input doesn't have a QUARTER value, just 12 monthly numbers per table-row.]
- The next 16 columns are a generated place where the report/spreadsheet's receipiant is to return estimates for next year. The format is the same as the actuals: 4 quarterly groups of 4 columns each (3 months and the quarter's total). However, because there is no data to print, the monthly projection columns are DEFINEd as 0.00.
All eight quarterly totals, and the two yearly totals are COMPUTEd as summations of their respective FIELDs or DEFINEs. The computed 5 actuals (4 quarters and the year) display as numbers. All 17 forecasts (months, quarters, and year) display as '0.00'.

My issue is that when the output workbook is opened in EXCEL, all the quarterly values are stored as actual numbers (or 0.00). No EXCEL Formulas compute them. The forecast quarters were expressed as COMPUTEs with the expectation that the generated spreadsheet would automatically sum the receipiant's input monthly forecasts into their respective quarter and year totals by EXCEL Formulae. But, they come out as if 0.00 was actually entered for each. (I also expected the COMPUTEs calculating quarterly actuals and year would remain as EXCEL formulae. This isn't a problem as The users won't be changing the monthly data underlying the summations of history. All the actuals are ignored, anyway, when the completed spreadsheet is returned to the application.)

The "Creating Reports" manual suggests that DEFINEs and COMPUTEs become EXL2K Formulae when using commands *TOTAL, SUMMARIZE, RECOMPUTE, and RECAP. I'm not. Am I expecting more of FOCUS than it will give me? Are other solutions suggested?

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

Since the Quarterly Totals are the result of COMPUTE statements, they do not get transformed to Excel formulas. As you noted, only the SUBTOTALs and other totals generates Excel formulas. As well, I don't think EXL2K FORMULA existed for WF 5.2

At least in 7.1.3 COMPUTES do result in formulas, as long as the formula is "translateable". For example this formula will translate:

TABLE FILE CAR
PRINT DC RC COMPUTE MARGIN/D12=RC -DC;
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

So I would expect the quarter calculations to become formulas, since they would be addition of visible columns (as COMPUTES). However, DEFINES should never become formulae, and my copy of the manual explicitly says they won't.

This works in 5.3:


TABLE FILE CAR
PRINT DEALER_COST SALES
COMPUTE TOTAL_COST/D12.2=DEALER_COST *SALES;
BY MODEL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

I did have to print the fields DEALER_COST and SALES

Well, whaddaya know, it does work! (If you keep it simple, probably).

chris, formula only sorta worked in 5, if you computed a price*quantity, the ON TABLE SUMMARIZE total was frightening.
In 7 it got better, but its still , as all have said so far, only for the very simple formulae.
When we ask for more complex things, the answer is always 'use templates' and 'do it yourself'.
Wouldn't it be marvelous if we could write an actual excel function, as a text field written in excel code, that *would not* evaluate in focus , but *would* evaluate in excel... come down not as a text field, but rather as an evaluatable formula. hmmm.

Bingo! Susannah won an NFR!

Thanks, all, for the CAR hints. I was able to demonstrate that it does work as expected ... if it's simple. However, I need more than 'simple' to get the job done.

I'll work this out as I often do with FOCUS code, '-*' almost all the statements, then remove the '_*'s little by little to 1) get it working 'simple', and 2) bring it to the breaking point. Then, having exposed the failing code, I can puzzle through getting it to work again.

I'll post here whatever I learn.

PS: I'm only doing this on 7.x

I said I would post the results of my investigation into this behavior ... Here it is:

Two things were at work 1) Complexity and 2) Inconsistent versions of IBI products.

1) Complexity: I did not simplify the *.fex stepwise as I described above because I had a business need to restructure the other aspects of the report (WHEREs, BYs, ON TABLEs, STYLEs, etc). The resulting COMPUTEs were recoded in a slightly simpler manner: yearly totals were expressed as sums of 4 quarters instead of as sums of 12 months. This caused the FUNCTIONS issue described above to exhibit the remarkable property of "auto-repair".

2) Inconsistent versions: We upgraded the entire FOCUS environment, installing 7.6.4 everywhere: ReportingServer, RCaster, and WebServer on the hosting server; and DevStudio our workstations.
After completing the upgrades I re-tested the original *.fex reported above and found the COMPUTEs passed into Excel exactly as expected when the code was first written.

One part of the result surprised me: the 'coding style' of the excel formulae varied between the old mixed 713/762 environment and the new all 764 environment. Under the 'mixed' environment the COMPUTE of a quarter became the Excel formula "A1+A2+A3", while under the 'pure 764' environment it became "((A1+A2)+A3)". [You can imagine the complexity of the formula for the year-is-sum-of-12-months situation, "(((((((((((A1+...".]

Go figure!

chris, the formula deal has always had problems with parentheses; i'm not at all surprised that 764 still shows it.

Isn't it sad that EXL2K FORMULA was introduced in the last millennium and we're still having trouble with it in this one.

Anyone tried EXL2K FORMULA with Financial 'Modeling' Language (or whatever it's called in this day and age)?

agreed. understandable, if you see it as those who actually write the code don't actually produce excel models in their work life...so there's that classic disconnect between the talkers and the walkers.

February 2009 and this isn't really solved.

DEFINE FILE CAR
SALESD/D8 = SALES;
END
TABLE FILE CAR
SUM
SALES
COMPUTE PCT_SALES/D6% = SALESD/TOT.SALESD * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

Why is the formula on the data rows not specifying the cells, but the values in the cells?

E.G. Row 1 formula: =(12000 /208420 )*100 instead of =(B2/SUM(B2:B6))*100

The Total row is not correct either: =SUM(C2:C6)

Using RECOMPUTE or SUMMARIZE gives the wrong result:

=(88190 /208420 )*100 88190 is from the last data row and is not the total.

This is in v7.6.5.

TOT.SALESD is not a physical column in the report, therefore EXL2K(FORMULA) displays the value and not the formula.

Isn't it if I add TOT.SALES NOPRINT to the code?

DEFINE FILE CAR
SALESD/D8 = SALES;
END
TABLE FILE CAR
SUM
TOT.SALESD NOPRINT
SALES
COMPUTE PCT_SALES/D6% = SALESD/TOT.SALESD * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

Or as a multi-verb request:

DEFINE FILE CAR
SALESD/D8 = SALES;
TOT_SALESD/D8 = SALES;
END
TABLE FILE CAR
SUM
TOT_SALESD NOPRINT
SUM
SALES
COMPUTE PCT_SALES/D6% = SALESD/TOT_SALESD * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

Neither of these two examples generate proper EXCEL formulas.

Francis,

This is very interesting (read, weird).

I ran this code and got the correct answers for the percentages with HTML as the format:

TABLE FILE CAR
SUM
SALES
COMPUTE PCT_SALES/D6% = SALES/TOT.SALES * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
-*ON TABLE PCHOLD FORMAT EXL2K 
-*ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

I ran it again with just FORMAT EXL2K and the answers were again correct.

I then ran it with the FORMULA and I saw formulas in the cells but the answers where all 100% except for the ones with zero.

I'm going to play around with a multi-verb request after lunch to see if I have any better success.

about all i've ever trusted FORMULA to do is perform a noncomplicated multiplication of columnA * columnB (like price * quantity) and SUM that (or any other) column.
anything outside that box?? nope.

 DEFINE FILE CAR
SALESD/D8 = SALES;
END
TABLE FILE CAR
SUM SALESD 
SUM
SALES
COMPUTE PCT_SALES/D6% = SALES/TOT.SALESD * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

This is the only way I could get the formula to look right. Unfortunately, the answers are wrong. Setting BYDISPLAY on did not solve the problem. The denominator winds up being zero on anything but the first line of the report.

I tried a couple of other things with hold files and such but none of them produced the formulas that Francis wants.

Boo hoo.

DEFINE FILE CAR
SALESD/D8 = SALES;
END
TABLE FILE CAR
SUM SALESD 
SUM
SALESD
COMPUTE PCT_SALES/D6% = SALESD/TOT.SALESD * 100;
BY COUNTRY
ON TABLE SUBTOTAL
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

This makes formulas appear but calculated incorrectly. Because France has zero sales an error is displayed for France and the total.

This is a tiny bit better:

DEFINE FILE CAR
SALEST/D8 = IF COUNTRY EQ 'FRANCE' THEN 0.01 ELSE SALES;
SALESD/D8 = IF COUNTRY EQ 'FRANCE' THEN 0.01 ELSE SALES;
END
TABLE FILE CAR
SUM
SALEST NOPRINT
SUM
SALESD
COMPUTE PCT_SALES/D6% = SALESD/SALEST * 100;
BY COUNTRY
ON TABLE RECOMPUTE
ON TABLE SET STYLESHEET *
BORDER=1, FONT='ARIAL', SIZE=8, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K FORMULA
END

Correction!

I ran your code and am still seeing a constant for the denominator instead of a cell reference. Is that what you wanted?

Ginny,

Sorry - you're right.

Strangely, what's displayed in my Excel 2003 is:
=($B2 /208420 )*100

So there is a formula but the total is a value.

A 'NOPRINT' field is not a physical column in the report. For Excel formula to display correctly all referenced columns need to be PRINTED in the report.
Hence the observed value
=($B2 /208420 )*100

Susannah,
Add the following COMPUTE to the report and test. The Formula is a logical expression.

COMPUTE CHANGE_FRA/D12.2 = IF ( ( SALESD * SALEST ) LT 1 ) THEN 0 ELSE ( ( SALESD / SALEST ) * 100 );

EXL2K FORMULA doesn't work with RECAP values that are displayed in a SUBFOOT in 7.6.6 Bummer!