Col1 Col2 Col3 Col4 Col5 Col6
A T E1 Paper V1 10
B Z F1 Metals V2 20
B Z F1 Metals V3 20
C K D1 Food V4 100
but the requirement is to display the report in this format :-
Col1 Col2 Col3 Col4 Col5 Col6
A T E1 Paper V1 10
B Z F1 Metals V2 20
V3
C K D1 Food V4 100
Please advise on how this can be done. Row 3 data is same as row 2 except for one field Col5. This datset returned from the database could have any number of rows. Output format is EXL2K.
I have tried searching the documentation and focal point posts, but couldn't find any.
Thank you.
This message has been edited. Last edited by: Kerry,
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 12:29 PM
GinnyJakes
TABLE FILE filename
PRINT COL6
BY COL1
BY COL2
BY COL3
BY COL4
BY COL5
ON TABLE PCHOLD FORMAT EXL2K
END
Do you have SET BYDISPLAY=ON in the program because that will cause the report to display in the first format you showed in your post.
If the data is in the correct order, you could DEFINE the report columns using LAST (the previous row to the current row), e.g.:
DEFINE FILE XXX
COL1X/A1 = IF COL1 EQ LAST COL1 THEN '' ELSE COL1;
COL2X/A1 = IF COL2 EQ LAST COL2 THEN '' ELSE COL2;
...
END
TABLE FILE XXX
PRINT
COL1X AS 'COL1'
COL2X AS 'COL2'
...
BY COL1 NOPRINT
BY COL2 NOPRINT
...
END
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
February 22, 2010, 12:47 PM
KK
Thank you for your suggestion Ginny, I tried that and set bydispplay=off, but its not giving me the correct result. Here is the code
TABLE FILE HOLDEU
PRINT
COMPUTE T/F8C = YTD_SALES/BUSINESS_PLAN_AMOUNT * 100; AS ''
COMMENTS AS ''
BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
BY EUNAME
BY SALES_REP_NAME AS ''
BY REGDESC AS ''
BY CUSTOMER_NAME AS ''
BY MARKET_SEGMENT_DESC AS ''
BY BUSINESS_PLAN_AMOUNT AS ''
BY YTD_SALES AS ''
HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Segment<+0>Channel<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
-- Resulted Output
Col1 Col2 Col3 Col4 Col5 Col6
A T E1 Paper V1 10
B Z F1 Metals V2 20
V3 20
C K D1 Food V4 100
The columns after Col5 are repeating. Col 5 in the code is Customer_Name.
Thank you. KK
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 12:52 PM
njsden
KK, is this perhaps what you need ?
DEFINE FILE TESTDATA
COL1_N/A1 MISSING ON = IF COL1 NE LAST COL1 THEN COL1 ELSE MISSING;
COL2_N/A1 MISSING ON = IF COL2 NE LAST COL2 THEN COL2 ELSE MISSING;
COL3_N/A2 MISSING ON = IF COL3 NE LAST COL3 THEN COL3 ELSE MISSING;
COL4_N/A10 MISSING ON = IF COL4 NE LAST COL4 THEN COL4 ELSE MISSING;
COL5_N/A2 MISSING ON = IF COL5 NE LAST COL5 THEN COL5 ELSE MISSING;
COL6_N/I4 MISSING ON = IF COL6 NE LAST COL6 THEN COL6 ELSE MISSING;
END
SET NODATA = ' '
TABLE FILE TESTDATA
PRINT
COL1_N
COL2_N
COL3_N
COL4_N
COL5_N
COL6_N
BY COL1 NOPRINT
BY COL2 NOPRINT
BY COL3 NOPRINT
BY COL4 NOPRINT
BY COL5 NOPRINT
BY COL6 NOPRINT
END
That way, you get to "hide" duplicate values regardless of which column they appear in.
It might have to do with this line. Uncomment it and see what the report looks like. An option would be to 'hold' the output sorted in that order and then to a TABLEF on the hold file.
TABLE FILE HOLDEU
SUM
COMPUTE MKT_SEG_DESC/A?? = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
LAST REGDESC EQ REGDESC THEN '' ELSE MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/F?? = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
LAST REGDESC EQ REGDESC THEN '' ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/F?? = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES ; AS ''
COMPUTE T/F8C = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
COMMENTS AS ''
BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
BY EUNAME
BY SALES_REP_NAME AS ''
BY REGDESC AS ''
BY CUSTOMER_NAME AS ''
ON TABLE SET NODATA ' '
ON TABLE SET BYDISPLAY OFF
END
This message has been edited. Last edited by: Dan Satchell,
WebFOCUS 7.7.05
February 22, 2010, 01:59 PM
KK
Hi All,
Thank you for your suggestions.
I have tried couple solutions, but I am getting a "Result of expression is not compatible" error on business plan amount field as it is a numeric field.
Dan, I have tried yours and I am getting this error 0 NUMBER OF RECORDS IN TABLE= 13 LINES= 13 (BEFORE TOTAL TESTS) (FOC3259) EXL2K FORMULA: UNRECOGNIZED OPERAND OR OPERATOR (FOC3263) EXL2K FORMULA: CANNOT CONSTRUCT EXPRESSION FOR FIELD MKT_SEG_DESC (FOC3298) ERROR FOUND IN A COMPOUND REPORT
Am I missing something ? Here is the code
TABLE FILE HOLDEU
SUM
COMPUTE MKT_SEG_DESC/A50V = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/I4 = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN 0 ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I4 = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN 0 ELSE YTD_SALES ; AS ''
COMPUTE T/F8C = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN 0 ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
COMMENTS AS ''
BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
BY EUNAME
BY SALES_REP_NAME AS ''
BY REGDESC AS ''
BY CUSTOMER_NAME AS ''
HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Channel<+0>Segment<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
ON TABLE SET NODATA ' '
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK
Please advise.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 02:10 PM
Dan Satchell
I did forget to set the COMPUTEd fields as nullable.
COMPUTE MKT_SEG_DESC/A50V MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/I4 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I4 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES ; AS ''
COMPUTE T/F8C MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
WebFOCUS 7.7.05
February 22, 2010, 02:23 PM
KK
Hi Ginny,
Your solution works, but there is a small problem
Col1 Col2 Col3 Col4 Col5 Col6
A T E1 Paper V1 10
B Z F1 Metals V2
V3 20
C K D1 Food V4 100
the col6 data is displayed on the last row (see above), is it possible to display on the first row instead :-
Col1 Col2 Col3 Col4 Col5 Col6
A T E1 Paper V1 10
B Z F1 Metals V2 20
V3
C K D1 Food V4 100
Thank you.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 02:49 PM
Dan Satchell
Sorry, I have one more correction.
COMPUTE MKT_SEG_DESC/A50V MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/I4 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I4 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES ; AS ''
COMPUTE T/F8C MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
WebFOCUS 7.7.05
February 22, 2010, 03:13 PM
KK
Hi Dan,
I have tried that with right syntax, but still gives me the same error message
(BEFORE TOTAL TESTS) (FOC3259) EXL2K FORMULA: UNRECOGNIZED OPERAND OR OPERATOR (FOC3263) EXL2K FORMULA: CANNOT CONSTRUCT EXPRESSION FOR FIELD MKT_SEG_DESC (FOC3298) ERROR FOUND IN A COMPOUND REPORT
Thank you, KK
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 03:26 PM
Dan Satchell
quote:
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK
If you are using the above statement in your code, it is probably the cause of the error message. Try this:
ON TABLE PCHOLD FORMAT EXL2K FORMULA
WebFOCUS 7.7.05
February 22, 2010, 03:56 PM
KK
Dan,
Its a Compound Excel Report, it will not work without OPEN NOBREAK. Please correct me if I am wrong.
Thanks.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 04:01 PM
Dan Satchell
quote:
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK
The statement below is fine, but the one above has an error in the syntax (OBREAK).
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN NOBREAK
WebFOCUS 7.7.05
February 22, 2010, 04:12 PM
KK
Dan,
Thank you for working on this issue, but I verified the report the OBREAK, probably that was typo when posting the message.
I moved the first sum field to see if that's causing any problem, but now the error message is on BUS_PLN_AMT line.
TABLE FILE HOLDEU
SUM
COMPUTE BUS_PLN_AMT/I11 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I11 MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES ; AS ''
COMPUTE T/F8C MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
COMMENTS AS ''
BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
BY EUNAME
BY SALES_REP_NAME AS ''
BY REGDESC AS ''
BY NEO_MARKET_SEGMENT_DESC AS ''
BY CUSTOMER_NAME AS ''
HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Segment<+0>Channel<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
ON TABLE SET NODATA ' '
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN NOBREAK
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 22, 2010, 04:58 PM
Dan Satchell
KK,
I don't see any errors with the COMPUTEs. I tested similar code using the CAR file and had no problems.
WebFOCUS 7.7.05
February 23, 2010, 08:59 AM
PBrightwell
You are getting an invalide operand or operator. That means WF is not recognising something in the line. Try turning your code around so that you are comparing the current field to the last one and not the other way around and with parenthesis around the components.
COMPUTE BUS_PLN_AMT/I11 MISSING ON = IF ((EUNAME EQ LAST EUNAME) AND (SALES_REP_NAME EQ LAST SALES_REP_NAME) AND (REGDESC EQ LAST REGDESC)) THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
February 23, 2010, 11:46 AM
KK
Thank you all. This is resolved.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
February 23, 2010, 08:07 PM
Doug
I know you said that this was resolved (although it was not marked as such in the title) But, I didn't see this result, which I think is the simpilist of all: Please check out this solution:
TABLE FILE MyFile
SUM
COMPUTE NEW_COL6/A3 = IF COL6 EQ LAST COL6 THEN ' ' ELSE COL6 ; AS 'COL6'
BY COL1
BY COL2
BY COL3
BY COL4
BY COL5
-*BY COL6 (not needed as I superceded it with the COMPUTEd field using the LAST funtion.)
END
Results:
COL1 COL2 COL3 COL4 COL5 COL6
A T E1 Paper V1 10
B Z F1 Metals V2 20
V3
C K D1 Food V4 100
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206