We have a report that runs fine unless a user tries to use OLAP sorting on a particular column "Gross_Commission" in which case they get this error.


0 ERROR AT OR NEAR LINE 149 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC016) THE TRUNCATED FIELDNAME IS NOT UNIQUE: GROSS_COMMISSION
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT


I've searched focal point on this, and added a
-SET FIELDNAME = NOTRUNC; to my report with no difference.

here's a snippet from the .fex
TABLE FILE MATRIX_COMM_TBL
SUM
EXT_SALES_DOLLARS_COMM/P11BM AS 'Sales,TY'
P1EXT_SALES_DOLLARS_COMM/P11BM AS 'Sales,LY MTD'
COMPUTE LYSALES_PERC/D12.2B% = ((EXT_SALES_DOLLARS_COMM- P1EXT_SALES_DOLLARS_COMM) / EXT_SALES_DOLLARS_COMM) * 100; AS '% of LY,Sales'
NET_COMMISSIONS/P11BM AS 'Net,Comm'
P1NET_COMMISSIONS/P11BM AS 'LY,Net,Comm'
GROSS_COMMISSION/P11BM AS 'Gross,Comm'
-*P1GROSS_COMMISSION/P11BM AS 'LY,Gross,Comm'
COMPUTE MARGIN_ACT_PERC/D12.2B% = ((EXT_SALES_DOLLARS_COMM- EXT_ACT_COST_COMM)/EXT_SALES_DOLLARS_COMM) * 100; AS 'Margin,Act %'
COMPUTE MARGIN_ACT/P11BM = EXT_SALES_DOLLARS_COMM- EXT_ACT_COST_COMM; AS 'Margin,Act $'
COMPUTE LY_MARGIN_ACT/P11BM = P1EXT_SALES_DOLLARS_COMM- P1EXT_ACT_COST_COMM; AS 'LYMargin,Act $'
COMPUTE MARGIN_PERCOFCHG/D12.2B% = (((EXT_SALES_DOLLARS_COMM-EXT_ACT_COST_COMM)- (P1EXT_SALES_DOLLARS_COMM-P1EXT_ACT_COST_COMM)) / (EXT_SALES_DOLLARS_COMM-EXT_ACT_COST_COMM)) * 100; AS 'Margin,% of,LY Margin'
-IF &CHR EQ 'BYMONTH' THEN GOTO BS_BYMONTH ELSE GOTO MY_ONE;
-BS_BYMONTH
BY HIGHEST YEARMONTH AS 'Month'

Has anyone else seen this before?

quote:

GROSS_COMMISSION

Is it possible your master has a field that starts with the characters GROSS_COMMISSION, that's usually when I get that type of error message when passing a define or compute.

quote:

-SET FIELDNAME = NOTRUNC;

This should be
SET FIELDNAME=NOTRUNC


The '-SET' and ';' are used for amper variables. You are trying to change an environmental setting.

Hi Jason,

I think this error happens because you are using column reformatting:

quote:

GROSS_COMMISSION/P11BM

This causes WebFocus to actually 'code' the following behind the scenes:

GROSS_COMMISSION NOPRINT
COMPUTE GROSS_COMMISSION/P11BM = GROSS_COMMISSION;

Therefore you will have 2 columns with the same name and I guess this causes OLAP to get in trouble. Looks like a bug in OLAP.

Roland,
You're right. I took the formatting off the field and it's running. Unfortunately,it's not really a professional report with a column of 4 decimal places while all others are integers, set with Money format.

I also find this interesting. The Fex has been set up this whole time with two columns for both Net and Gross comissions.

SUM
NET_COMMISSIONS/P11BM AS 'Net,Comm'
P1NET_COMMISSIONS/P11BM AS 'LY,Net,Comm'
GROSS_COMMISSION/P11BM AS 'Gross,Comm'
P1GROSS_COMMISSION/P11BM AS 'LY,Gross,Comm'

when I sort on the Net Comissions fields, there is no problem. The gross commission created the problem. It seems like the problem with "GROSS_COMMISSION" is deeper than this level, or the fact that it's the third field?

Any ideas?

Jason

If you do the reformating of that field in a define like

DEFINE FILE XXX
GCOM/P11BM=GROSS_COMMISSION;
END

you can use the already reformatted field in your report

You might also put the calculated fields first into an hold file and then report from that holdfile.

before the creation of the holdfile say

SET HOLDLIST=PRINTONLY

Frank,
That's a Great idea! Thank you.
I'm not sure if I'm doing everything properly, however, as I'm getting another error of
THE FIELDNAME IS NOT RECOGNIZED: GCOM when I use OLAP to sort the field. The report runs just fine initially, again the problem is only with olap.

The fex looks like this now:
DEFINE FILE MATRIX_COMM_TBL
GCOM/P11BM=GROSS_COMMISSION;
END
....
TABLE FILE MATRIX_COMM_TBL
SUM
NET_COMMISSIONS/P11BM AS 'Net,Comm'
P1NET_COMMISSIONS/P11BM AS 'LY,Net,Comm'
GCOM AS 'Gross,Comm'
P1GROSS_COMMISSION/P11BM AS 'LY,Gross,Comm'

Am I doing this correctly?

I've seen this happen before, but it was in some older version of webfocus (think it was a 5-x release).
When a request is run in OLAP mode, the entire request gets put into a javascript array and is manipulated from the java applet. It happened, that this translation into java sometimes did not produce correct code, resulting in strings being truncated. I found this by looking at the 'view source' of the htm page. In it you can see what is being put into this java applet. Maybe something happens in this case along the lines of the older problem?
If that is the case, then it may help to shorten the code in the table by putting all reformatting columns into define and using the shorter syntax to create your report.
Hope this helps.

turns out that I was missing a letter on my fieldname.

Instead of gross_commission, the database field name was gross_commissionS. I changed it and it works fine now.

I expected an error of "could not find field" or soemthing of that nature, so "truncated fieldname..blah blah" through me a loop, especially since the report ran with the wrong fieldname initially and only errored on OLAP.

Oh, the joys of FOCUS trying to make things easy for you and trying to match a truncated fieldname to something within the meta data you specify.

You start using a new facility ....

And then it bites you!!!!!!

T