I have the following define field: SORT/A1 MISSING ON NEEDS SOME = IF DPAY_PAY_RATE LT 7.50 THEN 'A' ELSE IF DPAY_PAY_RATE LT 9.49 THEN 'B' ELSE IF DPAY_PAY_RATE LT 11.99 THEN 'C' ELSE IF DPAY_PAY_RATE LT 15.24 THEN 'D' ELSE IF DPAY_PAY_RATE LT 19.24 THEN 'E' ELSE IF DPAY_PAY_RATE LT 24.49 THEN 'F' ELSE IF DPAY_PAY_RATE LT 30.99 THEN 'G' ELSE IF DPAY_PAY_RATE LT 39.24 THEN 'H' ELSE IF DPAY_PAY_RATE LT 49.74 THEN 'I' ELSE IF DPAY_PAY_RATE LT 63.24 THEN 'J' ELSE IF DPAY_PAY_RATE LT 79.99 THEN 'K' ELSE IF DPAY_PAY_RATE GE 80.00 THEN 'L' ELSE 'X';
Some customers only have employees in 2 or 3 of the grouping. However, when I print it out I need all of the groupings (A-L) to print. Any and all help will be appreciated.
Thanks, VictoriaThis message has been edited. Last edited by: Kerry,
February 12, 2007, 03:39 PM
TexasStingray
Try this
BY SORT ROWS 'A' OVER 'B' OVER 'C' OVER 'D' OVER ... ... 'X'
Scott
February 12, 2007, 03:41 PM
Alan B
If I remember correctly, use the syntax:
BY SORT ROWS A OVER B OVER C... etc.
Alan. WF 7.705/8.007
February 12, 2007, 03:47 PM
Victoria
TexasStingray,
Thanks for the reply. I'm actually holing this and using it as an ACROSS in the print statement. Any thougths there?
Thanks!
Victoria
February 12, 2007, 03:51 PM
Francis Mariani
With an ACROSS it's:
ACROSS SORT COLUMNS 'A' AND 'B' AND 'C' ... AND 'L'
Francis
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 12, 2007, 04:08 PM
Victoria
That was the first thing I tried, but it didn't work. Here is the entire piece of code:
DEFINE FILE PCKPAY SORT/A1 = IF DPAY_PAY_RATE LT 7.50 THEN 'A' ELSE IF DPAY_PAY_RATE LT 9.49 THEN 'B' ELSE IF DPAY_PAY_RATE LT 11.99 THEN 'C' ELSE IF DPAY_PAY_RATE LT 15.24 THEN 'D' ELSE IF DPAY_PAY_RATE LT 19.24 THEN 'E' ELSE IF DPAY_PAY_RATE LT 24.49 THEN 'F' ELSE IF DPAY_PAY_RATE LT 30.99 THEN 'G' ELSE IF DPAY_PAY_RATE LT 39.24 THEN 'H' ELSE IF DPAY_PAY_RATE LT 49.74 THEN 'I' ELSE IF DPAY_PAY_RATE LT 63.24 THEN 'J' ELSE IF DPAY_PAY_RATE LT 79.99 THEN 'K' ELSE IF DPAY_PAY_RATE GE 80.00 THEN 'L' ELSE 'X'; END
MATCH FILE JOBDATA SUM JOB_FUNCTION BY PARENT BY PERID RUN
FILE PCKPAY BY PARENT BY ID AS 'PERID' BY SORT WHERE PARENT IN (&parent) WHERE ((BEGDT LE '&REFDATE') AND (ENDDT GE '&REFDATE')) WHERE SUMGRP EQ 1 OR 8 AFTER MATCH HOLD AS FINAL OLD-AND-NEW RUN
FILE CLIENT_DETAIL SUM NAME BY PARENT_CLIENT_ID AS 'PARENT' WHERE PARENT_CLIENT_ID IN (&parent) WHERE CLIENT_STATUS_CD EQ 1 AFTER MATCH HOLD AS FINAL OLD-AND-NEW END -RUN
DEFINE FILE FINAL CNTX/I4 = 1; BLANK/A1 = ''; END
TABLE FILE FINAL HEADING "STATISTICS REPORT" "SUM CNTX BY PARENT NOPRINT BY BLANK NOPRINT BY JOB_FUNCTION AS 'JOB FUNCTION' ACROSS SORT AS '' COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'E' AND 'F' AND 'G' AND 'H' AND 'I' AND 'J' AND 'K' AND 'L' ON PARENT ROW-TOTAL ON BLANK SUBTOTAL AS 'TOTAL' ON TABLE NOTOTAL END
Victoria
February 13, 2007, 04:45 AM
Alan B
I could be wrong as I haven't used this syntax in a little while, but shouldn't it be
ACROSS SORT COLUMNS 'A' AND 'B'.. AS ' '
I have this feeling that the AS should be at the end.
Alan. WF 7.705/8.007
February 13, 2007, 09:28 AM
mgrackin
You should also move the third part of your MATCH so that it is the FIRST part since it only has one sort (BY) field. Usually each successive MATCH portion has the same or more sorts. Those with less sorts should be coded first.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
February 13, 2007, 09:30 AM
Victoria
That's how I had it originally, then I got this error: (FOC002) A WORD IS NOT RECOGNIZED: AS -- so I moved it. It runs with the AS '' right after the SORT, but I still only get the columns with data. The data that I'm running only has data in columns H and L. Regardless of that, I'd like every column to print.
Victoria
February 13, 2007, 10:04 AM
Alan B
Ah, I wonder. Mickey could have a point here.
Moving the 3rd Match to be the first and have increasing sort fields would be correct.
Yes, you're right, on checking the AS goes before the COLUMNS.
BUT, I tried a simple repro and cannot get the symptoms you get. This is 7.1.3 and I got all the columns I asked for, existing or not.
Really got me.
Alan. WF 7.705/8.007
February 13, 2007, 10:13 AM
Victoria
Alan,
Thanks for your help. I moved the 3rd match up, but it does nothing to the output. I usually pyramid the BYs, but I'm trying anything at this point!! I've asked a teammate here to take a look at it for me. I'll post a result if I find one.
Thanks!
Victoria
February 13, 2007, 10:59 AM
Francis Mariani
Victoria,
Could you please show us what the Master for the FINAL HOLD file looks like? It's quite hard to make definitive suggestions when we can't create and run code using the IBI provided sample files, so having the layout of FINAL may help.
Cheers,
Francis
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 13, 2007, 11:28 AM
Victoria
Well I figured out that if I comment out the second and third ACROSS fields, it works great and includes A - L. Now I just have to work those 2 back in somehow.
Victoria
February 13, 2007, 11:31 AM
Francis Mariani
quote:
TABLE FILE FINAL HEADING "STATISTICS REPORT" " SUM CNTX BY PARENT NOPRINT BY BLANK NOPRINT BY JOB_FUNCTION AS 'JOB FUNCTION' ACROSS SORT AS '' COLUMNS 'A' AND 'B' AND 'C' AND 'D' AND 'E' AND 'F' AND 'G' AND 'H' AND 'I' AND 'J' AND 'K' AND 'L' ON PARENT ROW-TOTAL ON BLANK SUBTOTAL AS 'TOTAL' ON TABLE NOTOTAL END
Sure looked like only one ACROSS statement and only one ACROSS field here.
Francis
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 13, 2007, 11:34 AM
Francis Mariani
Forcing columns to display with Multiple fields in one ACROSS statement appears to work as expected:
TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
BY COUNTRY
END
Forcing columns to display with more than one across statement does not work:
TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
ACROSS LENGTH
BY COUNTRY
END
Francis
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 13, 2007, 12:02 PM
Victoria
Sorry, I'm not sure how that posted without the other 2 ACROSS fields. At any rate, if anyone knows how to make that work it would be much appreciated.
Victoria
February 13, 2007, 12:05 PM
Alan B
However, if you use TABLE FILE CAR . . ACROSS LENGTH AROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5 . . END
I bet it will work. COLUMNS only works as the last in the ACROSS sort because of something to do with the grouping of the sort columns in an across phrase when building the internal matrix. Would need someone (Gerry/Noreen)to give a full explanation of this as I cannot remember.
Alan. WF 7.705/8.007
February 13, 2007, 12:50 PM
Francis Mariani
quote:
Sorry, I'm not sure how that posted without the other 2 ACROSS fields
Gosh, I bet you more than one person trying to help here thought there was only one ACROSS.
Meanwhile, as Alan suggests, making the ACROSS with the COLUMNS code as the last ACROSS will work:
TABLE FILE CAR
SUM
SALES
DCOST
RCOST
ACROSS LENGTH
ACROSS SEATS COLUMNS 1 AND 2 AND 3 AND 4 AND 5
BY COUNTRY
END
Francis
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 13, 2007, 01:44 PM
Victoria
Alan,
Thank you very much for your post. I'm going to try it. I apologize of not giving you the information on the other two across fields. It was of course not intential. I appreciate your help.
Victoria
February 13, 2007, 03:56 PM
Francis Mariani
Here's another idea:
DEFINE FILE FINAL
CNT_A/I4 = IF DPAY_PAY_RATE LT 7.50 THEN 1 ELSE 0;
CNT_B/I4 = IF DPAY_PAY_RATE GE 7.50 AND DPAY_PAY_RATE LT 9.49 THEN 1 ELSE 0;
CNT_C/I4 = IF DPAY_PAY_RATE GE 9.49 AND DPAY_PAY_RATE LT 11.99 THEN 1 ELSE 0;
CNT_D/I4 = IF DPAY_PAY_RATE GE 11.99 AND DPAY_PAY_RATE LT 15.24 THEN 1 ELSE 0;
CNT_E/I4 = IF DPAY_PAY_RATE GE 15.24 AND DPAY_PAY_RATE LT 19.24 THEN 1 ELSE 0;
CNT_F/I4 = IF DPAY_PAY_RATE GE 19.24 AND DPAY_PAY_RATE LT 24.49 THEN 1 ELSE 0;
CNT_G/I4 = IF DPAY_PAY_RATE GE 24.49 AND DPAY_PAY_RATE LT 30.99 THEN 1 ELSE 0;
CNT_H/I4 = IF DPAY_PAY_RATE GE 30.99 AND DPAY_PAY_RATE LT 39.24 THEN 1 ELSE 0;
CNT_I/I4 = IF DPAY_PAY_RATE GE 39.24 AND DPAY_PAY_RATE LT 49.74 THEN 1 ELSE 0;
CNT_J/I4 = IF DPAY_PAY_RATE GE 49.74 AND DPAY_PAY_RATE LT 63.24 THEN 1 ELSE 0;
CNT_K/I4 = IF DPAY_PAY_RATE GE 63.24 AND DPAY_PAY_RATE LT 79.99 THEN 1 ELSE 0;
CNT_L/I4 = IF DPAY_PAY_RATE GE 79.99 THEN 1 ELSE 0;
BLANK/A1 = '';
END
TABLE FILE FINAL
HEADING
"STATISTICS REPORT"
"
SUM
CNT_A
CNT_B
CNT_C
CNT_D
CNT_E
CNT_F
CNT_G
CNT_H
CNT_I
CNT_J
CNT_K
CNT_L
BY PARENT NOPRINT
BY BLANK NOPRINT
BY JOB_FUNCTION AS 'JOB FUNCTION'
ACROSS ...
ACROSS ...
ON PARENT ROW-TOTAL
ON BLANK SUBTOTAL AS 'TOTAL'
ON TABLE NOTOTAL
END
Francis
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 14, 2007, 10:27 AM
Francis Mariani
Victoria, did the last suggestion help at all? It should resolve trying to get all the columns to display and have multiple ACROSS statements.
Francis
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
March 06, 2013, 02:40 PM
bug
Francis, I liked the idea in your second last post. But what if you don't know the number and the value of ACROSS column? I'm stuck in a dilemma that
1. If I use ACROSS, some ACROSS column that do not have values won't show up;
2. If I use DEFINE field, the ACROSS columns are dynamic and I don't know all of them.
7.66 and 7.704 System: Windows / AIX / Linux Output: Mostly HTML, with some PDF, Excel and Lotus(!)
March 06, 2013, 03:29 PM
Francis Mariani
If you have a table where you can find all possible values of the ACROSS column, you could build the ACROSS COLUMNS statement and use that instead of hardcoding them:
-SET&ECHO='ON';
SET HOLDLIST = PRINTONLY
SET HOLDFORMAT = ALPHA
-RUN
TABLE FILE CAR
SUM
COMPUTE ROWCOUNT/P6 = ROWCOUNT + 1; NOPRINT
COMPUTE AND/A5 = IF ROWCOUNT EQ 1 THEN '' ELSE ' AND ';
COMPUTE ACROSSCOLVALUE/A30 = '''' || BODYTYPE || '''';
BY BODYTYPE NOPRINT
ON TABLE HOLD AS HACROSSCOLS
END
-RUN
TABLE FILE CAR
SUM
SALES
BY COUNTRY
ACROSS BODYTYPE AS ''
COLUMNS
-INCLUDE HACROSSCOLS
END
Francis
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