Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Display fields in same report but with different sort phrases
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Display fields in same report but with different sort phrases
 Login/Join
 
Member
posted
I have to build a report to print 4 fields sorted by ID and DETAIL_CODE, and next to it need to print 3 more fields that are sorted by ID and COURSE NUMBER. When I ran it I get error message "LIST OR PRINT MUST BE THE LAST VERB IN A MULTI-VERB REQUEST". Is there a different way or solution to print this report ?

My program:

TABLE FILE HOLD2
SUM
LST.HOLD2.HOLD2.HOU_AUTH/D20.2CSM AS 'AUTHORIZED ,CHARGES,HOUSING/MEALS:'
LST.HOLD2.HOLD2.TUI_AUTH/D20.2CSM AS 'AUTHORIZED ,CHARGES,TUITION/FEES:'
BY LOWEST HOLD2.HOLD2.STU_ZNUMBER NOPRINT

PRINT
LST.TBBDETC_DESC AS 'CHARGES, DESCRIPION'
LST.TBRACCD_AMOUNT/D20.2CSM AS 'CHARGES, AMOUNT '
TBRACCD_CROSSREF_DETAIL_CODE NOPRINT
BY LOWEST HOLD2.HOLD2.STU_ZNUMBER NOPRINT
BY TBRACCD_CROSSREF_DETAIL_CODE NOPRINT

PRINT
LST.SCBCRSE_CRSE_NUMB AS 'COURSE, NUMBER'
LST.SCBCRSE_TITLE AS 'COURSE,TITLE'
LST.SFRSTCR_CREDIT_HR AS 'CREDIT ,HOURS'
BY LOWEST HOLD2.HOLD2.STU_ZNUMBER NOPRINT
BY SCBCRSE_CRSE_NUMB NOPRINT
END

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


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: November 21, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I can see two things

First, you cannot have twice the verb PRINT. Since you put LST at almost all field, use SUM instead.

Second, your BY fields are inconsistent. The BY in first level must be repeated in all three, the BY in the second level repeated in the third and one more BY in the third level:
TABLE FILE CAR
SUM LST.SEATS
BY COUNTRY

SUM LST.RETAIL_COST
BY COUNTRY
BY MODEL

SUM DEALER_COST
BY COUNTRY
BY MODEL
BY CAR
END


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2159 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
Thanks Martin for your quickly replay. That is my problem, I need to list fields in same report that have different sorts:

My first 3 columns are sorted by ZNUMBER and DETAIL_CODE

My last 3 columns are sorted by ZNUMBER and CLASS_NBR

If I include the DETAIL_CODE in second sort I will have repeated lines...

I am wondering if there is any way around in webfocus to retrieve this data.

Thanks again!


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: November 21, 2016Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Hi Roberta,

There might be a problem with your report if the number of lines generated for the first 4 fields is not the same than the number of lines generated for the 3 other fields.

If they are the same, and since both parts are sorted by the same first field (ID in your example), here is a solution based on the CAR file:
  
-* File roberta01.fex
TABLE FILE CAR
LIST RETAIL DEALER
BY COUNTRY
BY CAR
ON TABLE HOLD AS R1
END
TABLE FILE CAR
LIST SEATS
BY COUNTRY 
BY BODYTYPE
ON TABLE HOLD AS R2
END

MATCH FILE R1
PRINT CAR RETAIL DEALER
BY COUNTRY BY LIST
RUN
FILE R2
PRINT BODYTYPE SEATS
BY COUNTRY BY LIST
AFTER MATCH HOLD AS ROBERTA OLD-OR-NEW
END

TABLE FILE ROBERTA
PRINT COUNTRY CAR RETAIL DEALER COUNTRY BODYTYPE SEATS
END


However if they are not the same then it is a bit trickier:
  
-* File roberta02.fex
TABLE FILE CAR
SUM RETAIL DEALER
BY COUNTRY
RANKED BY CAR
ON TABLE HOLD AS R1
END
-RUN
TABLE FILE CAR
PRINT 
COMPUTE RANK/I5=IF COUNTRY EQ LAST COUNTRY THEN RANK + 1 ELSE 1;
SEATS
BY COUNTRY 
BY BODYTYPE
ON TABLE HOLD AS R2
END
-RUN
MATCH FILE R1
PRINT CAR RETAIL DEALER
BY COUNTRY BY RANK
RUN
FILE R2
PRINT BODYTYPE SEATS
BY COUNTRY
BY RANK
AFTER MATCH HOLD AS ROBERTA OLD-OR-NEW
END

TABLE FILE ROBERTA
PRINT COUNTRY CAR RETAIL DEALER COUNTRY BODYTYPE SEATS
END

This message has been edited. Last edited by: Danny-SRL,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Martin is correct. In a multi-verb request you need to repeat the BY phrases for any subsequent SUM or PRINT after the first one. What you need is a single sort field that is common to both sets of data, namely DETAIL_CODE and CLASS_NBR.

One way to approach this would be to first of all create a new field called COUNTER. Then do two passes: The first will collect the DETAIL_CODE info with the COUNTER being incremented for each line. HOLD that data as HOLD1. The second will collect the CLASS_NBR with the counter being incremented for each line. HOLD that data as HOLD2.

DEFINE FILE ABC
 [STRIKE]COUNTER/D7=COUNTER+1;[/STRIKE] 
COUNTER/D7=IF ZNUMBER EQ LAST ZNUMBER THEN COUNTER+1 ELSE 1;
END

TABLE FILE ABC
PRINT
COUNTER
TBBDETC_DESC
TBRACCD_AMOUNT
TBRACCD_CROSSREF_DETAIL_CODE
BY STU_ZNUMBER
BY TBRACCD_CROSSREF_DETAIL_CODE
ON TABLE HOLD AS HOLD1
END

DEFINE FILE DEF
 [STRIKE]COUNTER/D7=COUNTER+1;[/STRIKE] 
COUNTER/D7=IF ZNUMBER EQ LAST ZNUMBER THEN COUNTER+1 ELSE 1;
END

TABLE FILE DEF
PRINT
COUNTER
SCBCRSE_CRSE_NUMB
SCBCRSE_TITLE
SFRSTCR_CREDIT_HR
BY STU_ZNUMBER
BY SCBCRSE_CRSE_NUMB
ON TABLE HOLD AS HOLD2
END


You now have two files with the data sorted correctly for each one and a common field, COUNTER, that you can use to merge the information. USE the MATCH FILE command to do the merge:

MATCH FILE XYZ
SUM HOU_AUTH
TUI_AUTH
BY ZNUMBER
RUN
FILE HOLD1
PRINT DETAIL_CODE (other fields ......)
BY ZNUMBER
BY COUNTER
RUN
AFTER MATCH HOLD
FILE HOLD2
PRINT CLASS_NBR  (other fields ....)
BY ZNUMBER
BY COUNTER
RUN
AFTER MATCH HOLD AS HOLD3
END

TABLE FILE HOLD3
SUM HOU_AUTH
TUI_AUTH
BY ZNUMBER
PRINT DETAIL_CODE (other fields) CLASS_NBR (other fields)
BY ZNUMBER
BY COUNTER NOPRINT
END


I may not have the syntax exactly right and you may have to use SUM instead of PRINT in the MATCH FILE command to get exactly the matrix you need, but this approach, though clumsy, should work. Someone else may have a more elegant approach. Note that in MATCH FILE I didn't use the COUNTER in the first pass. The rule is that if a BY phrase is used higher up it must be repeated lower down, but there is no requirement to use it in the first instance if it isn't relevant.

Note also that you shouldn't have to use LOWEST. WF will sort from low to high automatically. It's the other way around where you have to specify.

This message has been edited. Last edited by: George Patton,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
George,
Your solution looks like mine.
However you are producing a numbering system that does not take into account the outer sort field. There the numbering should reset to 1 when the sort field value changes.
Hence the use of LIST in my first example and of RANK in the second.
The little niceties of the FOCUS language.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thank you very much for all your help! The MATCH worked perfectly!


WebFOCUS 8
Windows, All Outputs
 
Posts: 3 | Registered: November 21, 2016Reply With QuoteReport This Post
Master
posted Hide Post
Danny, You are right. I didn't initially think it was necessary to reset the counter with each ZNUMBER but
I now appreciate that it is necessary.

That can be fixed in my example by modifying the two DEFINE FILE statements as follows:

COUNTER/D7=IF ZNUMBER EQ LAST ZNUMBER THEN COUNTER+1 ELSE 1;

(Incidentally, I didn't create my method as a response to yours - we were both typing at the same time and you managed to save before me ... )


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Display fields in same report but with different sort phrases

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.