When I run a report and use the excel formula option the calcultation is not working as it should

this is my code

DEFINE FILE EMPDATA
FCODE/A1=IF DIV EQ 'CE' OR  'CORP' THEN 'A' ELSE 'B';
PINI/D12=EDIT(PIN);
PIN2/I2=IF PINI LT 200 THEN 1 ELSE 2;
HIREMAAND/M=HIREDATE;
MANGROEP/A3=EDIT(JOBCLASS,'999');
END
TABLE FILE EMPDATA
-*ON HIREMAAND SUBTOTAL AS '*TOTAL'
SUM
     SALARY
BY HIGHEST FCODE NOPRINT
BY MANGROEP
BY TITLE
BY JOBCLASS
BY FIRSTNAME
BY LASTNAME
ACROSS PIN2

ON FCODE SUBHEAD
"<FCODE "
ON FCODE SUBTOTAL AS '*TOTAL FCODE'

ON MANGROEP SUBTOTAL AS '*TOTAL MANGROEP'
HEADING
"demo report excel formula "
FOOTING
"&DATEtMDYY <+0> &FOCFEXNAME"
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<PDF,PDF>,<Excel 2000,EXL2K>,<Excel Formle,EXL2K FORMULA>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=CM,
     SQUEEZE=ON,
     ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,
     COLOR='BLACK',
     BACKCOLOR='NONE',
     STYLE=NORMAL,
$
TYPE=DATA,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     BORDER-TOP-COLOR=RGB(22 47 97),
     BORDER-BOTTOM-COLOR=RGB(22 47 97),
     BORDER-LEFT-COLOR=RGB(22 47 97),
     BORDER-RIGHT-COLOR=RGB(22 47 97),
$
TYPE=TITLE,
     COLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=TABHEADING,
     COLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     COLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     COLOR=RGB(237 241 213),
     BACKCOLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=FOOTING,
     COLOR=RGB(237 241 213),
     BACKCOLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=12,
     COLOR=RGB(22 47 97),
     BACKCOLOR=RGB(225 225 255),
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR='WHITE',
     STYLE=BOLD,
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     COLOR=RGB(22 47 97),
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     STYLE=BOLD,
$
ENDSTYLE
END

I have tried some changes and the problem occurs when I use a hidden BY field (FCODE) and make that code also a SUBHEAD.

any idea???

Frank,

All appears OK when I run your code, values formulae etc., what problems are you having?

T

I see I forgot to point out shat is going wrong...

The problem is when you select the option Excel Fromula, the result of the calculation gives you zero, and EXCEL warns for a circular formula!

If you select one of the other options it works without a problem.

Not for me Frank, no problems on selecting "Excel Formle" at all.

T

I ran your code in my local 7.6.11 Dev.Studio environment and I am experiencing the same behaviour.

When analyzing the resulting Excel spreadsheet I noticed a "shift" in the formula used to calculate the subtotals.

In the first section of the report corresponding to FCODE = 'B', there are 5 summary lines for MANGROEP = '019' that go from row 7 to row 11 and therefore the subtotals on MANGROEP 019 should be calculated as:

F12 Cell:

=SUM(F7:F11)

G12 Cell:

=SUM(G7:G11)

The actual values Excel is receiving from WebFOCUS are =SUM(F8:F12) and =SUM(G8:G12) respectively.

The exact same situation occurs in all of the =SUM expressions on both FCODE and MANGROEP.

I don't have much experience with EXL2K FORMULA so it puzzles me why that is happening. I'll play around a bit to see what else I can find but just wanted to let you know that your case can indeed be replicated, at least in 7.6.11 and for what I can see from your signature, 7.6.10.

Tony, did you try that code in your local 7.6.11 environment or did you do it in 7.6.2 environment only? I am just trying do determine if this could be a "version thing" ... (you can tell I have no idea of what's going on )

Regards,
- Neftali.

quote:

The actual values Excel is receiving from WebFOCUS are =SUM(F8:F12) and =SUM(G8:G12) respectively.

At least we know why Excel is complaining about circular references as those values above are the ones it has in the F12 and G12 cells. Not that it solves anything ...

Interesting .... removing the SUBHEAD on FCODE makes the formula expressions work appropriately. That's partially good news I hope! I'll keep playing ...

Frank, unfortunately this seems to be an issue (feature?) in 7.6.10/7.6.11 and who knows which previous 7.6.x versions.

I just ran the exact same code with SUBHEAD and all in my production 5.3.4 environment and the Excel document was created and formula expressions OK.

Tony also reported that it had worked fine for him and I think his environment is 7.6.2 so the "damage" seems fairly recent.

I think this has merits for a case before IBI, no?

- Neftali.

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

Frank,

This runs correctly in v7.6.5 and v7.6.8. I guess there are problems with the latest (and greatest) versions.

Neftali, Tony, Francis

thanks for the effort so far...
yes it seems to occure in the latest release 7.6.10 and I have strumbled upon this problem just by coincidence...
I played also a bit more and when I remove this block of code

$
TYPE=DATA,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     BORDER-TOP-COLOR=RGB(22 47 97),
     BORDER-BOTTOM-COLOR=RGB(22 47 97),
     BORDER-LEFT-COLOR=RGB(22 47 97),
     BORDER-RIGHT-COLOR=RGB(22 47 97),

It works perfect...

So no I have made a work around in this special case where this border coding is skipped if the user chooses the formula output.

I will also send this issue to Information Builders

Frank,

Using your exact code, I am running this against a 7.6.2 server (as Neftali mentioned) but, in my output, the subtotal line to which Neftali refers is row 11 and not row 12. The formulae in each of F11 and G11 are respectively =SUM(F6:F10) and =SUM(G6:G10).

My local EDA server in DS 7.6.11 is not working currently (security problems on install )

T

quote:

I have made a work around in this special case where this border coding is skipped

That's a much better workaround than removing the SUBHEAD altogether as I had done during my tests. It just never occurred to me that the style sheet definitions would be messing around the Excel formula output in such a way ... well, I think I'll need to pay more attention to styling when facing some of those "unexplainables" in the future .

- Neftali.

quote:

the subtotal line to which Neftali refers is row 11 and not row 12

I also get the results in row 11 when testing against WF 5.3.4. However the same code running in 7.6.11 makes result appear one row shifted down

Taking the "easy" road, I first attempted to remove the title on the ACROSS field and sure enough now I had 1 less row to worry about but the circular reference was still there.

My next step was to comment the SUBHEAD out and this time the formula worked but the subheading was missing of course.

Frank's further testing showed that hiding the BORDER on DATA made the problem disappear ... this reminded me of some old cases reporting an extra blank line below the HEADING that appears magically when using BORDER with no known way to make it go. Well, I just re-run the code in 7.6.11 as originally posted and sure enough the blank line was there which explains why all of the results appear one row down when compared to Tony's results but seems like the guys at IBI "forgot" to account for that one line when producing the formula results.

This does not occur in my "old" version of WF anyway as BORDERs were not supported for EXL2K format until 7.6.9. I am thinking that that's exactly the release where this formula issue was introduced but not "discovered" until now. Francis just proved that the code works as expected up to 7.6.8.

It would be interesting to test the code in 7.6.9 at least to prove the theory ... not that it fixes the problem but it may help to further document the case to IBI.

- Neftali.

quote:

It would be interesting to test the code in 7.6.9

Is anyone out there with a 7.6.9 environment who would be willing to volunteer?

We have 7.69 and I recieved the same error.

Cool! Thanks Dan.

EXL2K BORDER were introduced in 7.6.9 and that seems to be the same release where the EXL2K FORMULA issue started to appear (circular reference due to shifted cells "thanks" to the magic blank line WebFOCUS gives us after a HEADING whenever BORDERs are in place).

It might be just a coincidence though

- Neftali.