I have a requirement to display the Earliest Start Time and Latest End time for each office within each region. The Region contains multi offices. The input data contain only the StartTime and EndTime for each task performed in the office. I need to insert the Level 1 and Level 2 rows. I sorted the original data by Region #, Office code, and Start_DateTime field. I am running with App Studio 8.0 on Window.
The report should look like this:
Task START_TIME END_TIME Level
*Reg 1: 2013/01/02 07:25 2013/06/06 15:38 1
**Office-1 2013/01/02 07:25 2013/04/12 11:12 2
MDKT 2013/01/02 07:25 2013/01/02 10:51 3
UNWK 2013/01/02 10:53 2013/02/01 01:45 3
PRE 2013/02/01 01:45 2013/03/04 13:27 3
UNDW 2013/04/12 10:50 2013/04/12 11:12 3
**Office-2 2013/04/12 11:12 2013/06/06 15:38 2
UNDW 2013/04/12 11:12 2013/04/30 13:38 3
DWR 2013/04/30 13:38 2013/05/02 16:25 3
UNWK 2013/05/02 16:25 2013/06/06 15:38 3

I have tried several prefix methods without success. Please help, thanks in advance. They are:
1) Manipulating Summary values with Prefix Operators. But it did not work on Datetime field.
ON REGION SUBTOTAL MIN. START_TIME MAX. END_TIME AS ‘*Region:’
ON OFFICE SUBTOTAL MIN. START_TIME MAX. END_TIME AS ‘**Offcie-’

2) use “SUBHEAD or SUBFOOT with MAX command, but it returned me the value of first record.
ON REGION SUBHEAD
"ON OFFICE SUBHEAD
"
3) I have also use COMPUTE in table body, but it won’t display correct data when use SUBHEAD :

COMPUTE RGN_START_TIME/HYYMDI = IF(LAST RGN_NUM NE RGN_NUM)THEN START_TIME ELSE IF ( LAST RGN_START_TIME GT START_TIME) THEN START_TIME
ELSE LAST RGN_START_TIME;

ON REGION SUBHEAD
" 1 "
ON OFFICE SUBHEAD
" 2 "

This message has been edited. Last edited by: Emily Lee,

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

What prefixes have you used without success? Have you tried FST., LST., MIN., MAX.?

Doug Lee

I believe that the issue you are having is that the data has to be previously known for the SUBHEAD to display. So you should use a Multi-verb request like this example:

TABLE FILE GGSALES
SUM MIN.DOLLARS NOPRINT MAX.DOLLARS NOPRINT
BY REGION NOPRINT
SUM MIN.DOLLARS NOPRINT MAX.DOLLARS NOPRINT
BY REGION NOPRINT
BY CITY NOPRINT
SUM MIN.DOLLARS  MAX.DOLLARS 
BY REGION NOPRINT
BY CITY NOPRINT
BY CATEGORY 
ON REGION SUBHEAD
"<REGION - <MIN.DOLLARS  <MAX.DOLLARS"
ON CITY SUBHEAD
"<CITY - <C2 <C3"
END

Dough, I have try MAX, FST, LST, MIN.

Alan, it sounds like a good plan.
I will try the multi-verb request.

thanks.

Alan,
Thanks for the suggestion. But I still got some problem of the 2nd subhead line of the 2nd BY key:
ON CITY SUBHEAD
"It show the
Any suggestion? Thanks a million. Here is my code:


TABLE FILE HOLD_CASES
SUM MIN.START_TIME NOPRINT MAX.END_TIME NOPRINT
BY REGION NOPRINT
SUM MIN.START_TIME NOPRINT MAX.END_TIME NOPRINT
BY REGION NOPRINT
BY CITY NOPRINT
PRINT
STUS_CD
START_TIME
END_TIME
BY REGION NOPRINT
BY CITY NOPRINT
BY START_TIME NOPRINT
ON REGION SUBHEAD
"ON CITY SUBHEAD
"

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

At my 2nd SUBHEAD of CITY, it showed the MIN and MAX of REGION, not of the 2nd BY key CITY. Any suggestion for it? thanks.

TABLE FILE HOLD1
SUM MIN.START_TIME NOPRINT MAX.END_TIME NOPRINT
BY REGION NOPRINT
SUM MIN.START_TIME NOPRINT MAX.END_TIME NOPRINT
BY REGION NOPRINT
BY CITY NOPRINT
PRINT STUS_CD
START_TIME
END_TIME
BY REGION NOPRINT
BY CITY NOPRINT
BY START_TIME NOPRINT

ON REGION SUBHEAD
"ON CITY SUBHEAD
"

This message has been edited. Last edited by: Emily Lee,

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

There are some error with the POST.
My error was : the 2nd SUBHEAD line of the 2nd BY Key, did not show the

This message has been edited. Last edited by: Emily Lee,

WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel

Emily, please put your code between code tags, the red icon. Then it can be read correctly.

The 2nd SUBHEAD LINE Showed the MIN MAX of REGION, not CITY I expected. Thanks.
here is the code:

  TABLE FILE HOLD1_CASES
SUM MIN.START_TIME NOPRINT MAX.END_TIME NOPRINT 
	 BY REGION NOPRINT
SUM MIN.START_TIME  NOPRINT MAX.END_TIME  NOPRINT 
	 BY REGION NOPRINT
	 BY CITY NOPRINT
PRINT
	 STUS_CD     
	 START_TIME
	 END_TIME
	 
BY REGION NOPRINT
BY CITY NOPRINT
BY START_TIME NOPRINT

ON REGION SUBHEAD
"<REGION   <MIN.START_TIME    <MAX.END_TIME "
ON CITY  SUBHEAD
"<CITY     <MIN.START_TIME      <MAX.END_TIME "                                 

You must use Column Notation, in my example C2 and C3, for City level, otherwise you will get Region. Referring to the field names directly will bring in the first occurrence of this field in the request, which is BY REGION.

Alan,
Thank you for inspiration and suggestion.
I have a little trouble using the Column Notation. So I use two step method: first create a hold file which contains all the SUM MIN and MAX value with AS Name, and read the hold file to create final printout with SUBHEAD command. It works very well. Thanks a million.
Here is :

 
SET ASNAME = ON	
TABLE FILE  CASES
SUM MIN.START_TIME  AS ‘RGN_MIN_ST’
          MAX.END_TIME AS ‘RGN_MAX_ET’
	 BY REGION NOPRINT
SUM MIN.START_TIME    AS ‘CITY_NIN_ST’
         MAX.END_TIME   AS ‘CITY_MAX_ET’  
	 BY REGION NOPRINT
	 BY CITY NOPRINT
PRINT
          STUS_CD     
	 START_TIME
	 END_TIME	 
BY REGION  
BY CITY  
BY START_TIME NOPRINT
 ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD1
PRINT
	 STUS_CD     
	 START_TIME
	 END_TIME 
BY REGION NOPRINT
BY CITY NOPRINT
BY START_TIME NOPRINT

ON REGION SUBHEAD
"<REGION   <RGN_MIN_ST    <RGN_MAX_ET "
ON CITY  SUBHEAD
"<CITY     <CITY_MIN_ST      <CITY_MAX_ET "   
END
-RUN