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,

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

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!

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,

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,

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.

Thank you very much for all your help! The MATCH worked perfectly!

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 ... )