Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] printing out ALL parts of a define

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] printing out ALL parts of a define
 Login/Join
 
Member
posted
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,
Victoria

This message has been edited. Last edited by: Kerry,
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Master
posted Hide Post
Try this

BY SORT ROWS 'A' OVER
'B' OVER
'C' OVER
'D' OVER
...
...
'X'




Scott

 
Posts: 865 | Registered: May 24, 2004Report This Post
Virtuoso
posted Hide Post
If I remember correctly, use the syntax:

BY SORT ROWS A OVER
B OVER
C... etc.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Member
posted Hide Post
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
 
Posts: 25 | Location: Kingwood, Tx | Registered: May 06, 2003Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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(!)
 
Posts: 147 | Location: Toronto (GTA) | Registered: May 25, 2005Report This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] printing out ALL parts of a define

Copyright © 1996-2020 Information Builders