Hi,
I need a report to sum time by two date parameters. I tried to first define the time fields to have the correct format and place it in the file with the sum. It's not working correctly. It works when I do not include the define fields. I gues I'm asking how do I format and display the sum time fields.

-* File v2adhere.fex
DEFINE FILE TBLARCH
NUMSEC/I4=START;
HOUR/I2=(NUMSEC/60);
MIN/I2=((NUMSEC/60 - (HOUR *60)));
HHMMSS/I6=INT( NUMSEC/3600 ) * 10000 + INT( IMOD(NUMSEC,3600,'I4')/60 ) * 100 + IMOD(NUMSEC,60,'I2');
TIME/A9=IF START LT 0 THEN '-' | EDIT(HHMMSS,'99:99:99') ELSE EDIT(HHMMSS,'99:99:99');
SNUMSEC/I4=STOP;
SHOUR/I2=(SNUMSEC/60);
SMIN/I2=((SNUMSEC/60 - (SHOUR *60)));
SHHMMSS/I6=INT( SNUMSEC/3600 ) * 10000 + INT( IMOD(SNUMSEC,3600,'I4')/60 ) * 100 + IMOD(SNUMSEC,60,'I2');
STIME/A9=IF STOP LT 0 THEN '-' | EDIT(SHHMMSS,'99:99:99') ELSE EDIT(SHHMMSS,'99:99:99');
DNUMSEC/I4=SCHEDULED;
DHOUR/I2=(DNUMSEC/60);
DMIN/I2=((DNUMSEC/60 - (DHOUR *60)));
DHHMMSS/I6=INT( DNUMSEC/3600 ) * 10000 + INT( IMOD(DNUMSEC,3600,'I4')/60 ) * 100 + IMOD(DNUMSEC,60,'I2');
DTIME/A9=EDIT(DHHMMSS,'99:99:99');
END
TABLE FILE TBLARCH
SUM
START
STOP
SCHEDULED
COMPUTE SIGN/D5.1% = ( SIGNED_IN / SCHEDULED ) * 100; AS 'SIGN IN %'
COMPUTE COMPPER/D5.1% = ( COMPLIANCE / SCHEDULED ) * 100;
AS 'COMPLIANCE %'
BY TOTAL HIGHEST COMPUTE COMPPER/D5.1% = ( COMPLIANCE / SCHEDULED ) * 100; NOPRINT
BY FULLNAME AS 'NAME'
ON TABLE SUBHEAD
"Adherence Report"
" "
"WHERE ( TEAMNAME EQ '&TEAMNAME.(FIND TEAMNAME IN TBLARCH).Please Select Team.' );
WHERE NOM_DATE GE '&dte' AND NOM_DATE LE '&edte';
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE TBLARCH.TBLARCH.START TBLARCH.TBLARCH.STOP TBLARCH.TBLARCH.SCHEDULED SIGN COMPPER AS 'ROLL-UP'
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
end

Joe

I am having a problem understanding what input you are giving and what output you want. Can you try a simple example on one of the demo files please.

Joe,

I guess you are trying to add some time data together to give a total time expended value?
e.g. 3 hrs 25 mins 35 secs + 4 hrs 42 mins 15 secs = 8 hrs 7 mins and 50 secs.

If this is the case then you would be better off getting the time values into seconds, producing the sum and then converting into HH:MM:SS maybe?

OR, is the data you are using an intimation of the start seconds and the stop seconds?

If this is the case then you are probably close to your goal but you will have problems. This is because within your define you are trying to perform an EDIT mask on integer data. To do this you would have to do a "double EDIT" e.g EDIT(EDIT(integerfield),'99:99:99'). The "inner" EDIT tranforms the integer field to an alpha equivalent, the "outer" EDIT applies the mask.

If it is neither of these two possibilities then please explain clearly - as Alan suggest - using one of the sample files.

T

I'm sorry. I'm trying to format time that is summed through a date parameter query. The raw data for the time field is already in seconds. The define fields I created does provide the correct format I need. What I want to do, is display the time format's new defined field when the report is queried for a specific data range instead of the actual field which is in seconds. It took alot of define coding to get it to the way I want it to display. In MS Access, this can be done very easily through the expression builder. Any Ideas?

Thanks,

Joe

Try this type of code. The first part is creating sample data. The important bit is from the DEFINE onwards -

APP FI TIMEDATA DISK TIMEDATA.MAS (LRECL 80
-RUN
-WRITE TIMEDATA
-WRITE TIMEDATA FILE=TIMEDATA,SUFFIX=FOC
-WRITE TIMEDATA SEGNAME=SEG1
-WRITE TIMEDATA FIELD=TIMEDATA_SECS,   ,I9   ,I9   ,$
-RUN
CREATE FILE TIMEDATA
MODIFY FILE TIMEDATA
FIXFORM TIMEDATA_SECS/A9
DATA
    13658
    12345
    58258
END
-RUN
DEFINE FILE TIMEDATA
  HRS/I2    = INT(TIMEDATA_SECS / (60 * 60));
  MINS/I2   = INT((TIMEDATA_SECS - (HRS * 60 * 60)) / 60);
  SECS/I2   = INT(TIMEDATA_SECS - ((HRS * 60 * 60) + (MINS * 60)));
  HHMMSS/A8 = EDIT(HRS) | ':' | EDIT(MINS) | ':' | EDIT(SECS);
END
TABLE FILE TIMEDATA
PRINT HHMMSS
   BY TIMEDATA_SECS
END

T

Looking a bit further and all you really need is this (I think) -

DEFINE FILE TBLARCH
  HHMMSS/I6  = INT(START/3600 ) * 10000 + INT(IMOD(START,3600,'I4')/60) * 100 + IMOD(START,60,'I2');
  TIME/A9    = IF START LT 0 THEN '-' | EDIT(HHMMSS,'99:99:99') ELSE EDIT(HHMMSS,'99:99:99');
  SHHMMSS/I6 = INT(STOP/3600 ) * 10000 + INT(IMOD(STOP,3600,'I4')/60 ) * 100 + IMOD(STOP,60,'I2');
  STIME/A9   = IF STOP LT 0 THEN '-' | EDIT(SHHMMSS,'99:99:99') ELSE EDIT(SHHMMSS,'99:99:99');
  DHHMMSS/I6 = INT(SCHEDULED/3600 ) * 10000 + INT(IMOD(SCHEDULED,3600,'I4')/60 ) * 100 + IMOD(SCHEDULED,60,'I2');
  DTIME/A9   = EDIT(DHHMMSS,'99:99:99');
END
TABLE FILE TBLARCH
SUM TIME
    STIME
    DTIME
COMPUTE SIGN/D5.1% = ( SIGNED_IN / SCHEDULED ) * 100; AS 'SIGN IN %'
COMPUTE COMPPER/D5.1% = ( COMPLIANCE / SCHEDULED ) * 100;
AS 'COMPLIANCE %'
BY TOTAL HIGHEST COMPUTE COMPPER/D5.1% = ( COMPLIANCE / SCHEDULED ) * 100; NOPRINT
BY FULLNAME AS 'NAME' ....

T

Thanks,
I tried something similar. It only shows the format for the first date. I was thinking of using a stored procedure from sql server. I can get it to work through the autoparameter form, however when I link it to an html form it does not work error (FOC205 THE DESCRIPTION CANNOT BE FOUND FOR SQLOUT AND THE DUMP FILE...)CODE BELOW:

< !-- Generated by Report Layout Painter -->
<HTML>
<HEAD>
<TITLE>HtmlPage</TITLE>
<BASE href=HTTP://ppnta369:8080>
<META content="MSHTML 6.00.2900.3243" name=GENERATOR>
<script id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
UpdateData();
// TODO: Add your event handler code here
}
//End function window_onload
</SCRIPT>

<script for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
</HEAD>
<BODY style="OVERFLOW: auto">
<iframe id=report1 style="Z-INDEX: 1; LEFT: 20px; WIDTH: 570px; POSITION: absolute; TOP: 180px; HEIGHT: 190px" tabIndex=1 name=report1 executebuttonId="form2Submit" requests_list="0" autoExecute="False" elementtype="2"></IFRAME>
<FORM id=form2 style="Z-INDEX: 2; LEFT: 30px; WIDTH: 420px; POSITION: absolute; TOP: 30px; HEIGHT: 105px" tabIndex=2 name=form2 onsubmit="OnExecute[this);return false;" method=post requests_list="0" fexlist_list="report1" default_slider_type="4" form_prompt_location="1" form_number_of_visible_rows="4" form_number_of_columns="4" vert_dist_between_controls="10" form_hor_dist_between_controls="10" form_dist_between_desc_and_input="10">
<INPUT id=form2Submit style="Z-INDEX: 3; LEFT: 10px; BACKGROUND-IMAGE: url[/ibi_html/javaassist/ibi/html/describe/run16.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 75px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=3 type=submit value=" " name=ITEM1> 
<INPUT id=form2Reset style="Z-INDEX: 4; LEFT: 60px; BACKGROUND-IMAGE: url[/ibi_html/javaassist/ibi/html/describe/reset.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 75px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=4 type=reset value=" " name=reset1> 
<SELECT id=combobox1 style="Z-INDEX: 5; LEFT: 10px; WIDTH: 110px; POSITION: absolute; TOP: 30px" tabIndex=5 name=dte ibiformat="MDYY" ibiapp_app="joetest/VFHTML" ibic_server="EDASERVE" datafield="NOM_DATE" displayfield datasource="TBLARCH.mas" datatype="1" sourcetype="typeMaster" operation></SELECT> 
<SELECT id=combobox2 style="Z-INDEX: 6; LEFT: 140px; WIDTH: 140px; POSITION: absolute; TOP: 35px" tabIndex=6 name=edte ibiformat="MDYY" ibiapp_app="joetest/VFHTML" ibic_server="EDASERVE" datafield="NOM_DATE" displayfield datasource="TBLARCH.mas" datatype="1" sourcetype="typeMaster" operation></SELECT></FORM>
<xml id=ibi_requests>
<requests>
	
<request requestid="0" targetname="report1" ibif_ex="report1" targettype="0" sourcetype="typeAdhocfex" ibiapp_app="" paramremovedbyuser="dte&edte">
		
< ![CDATA[-* File sqlstoredad.fex
SQL SQLMSS EX wf_adherence '&dte','&edte';

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DUMP
END


TABLE FILE DUMP
PRINT
     FULLNAME
     NStart AS 'START'
     SSTOP AS 'STOP'
     SSCHEDULED AS 'SCHEDULED'
     SignedInPerc AS 'Signed In %'
     CompPerc AS 'Comp %'
BY TEAMNAME
HEADING
"<TEAMNAME"
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=DATA,
     COLUMN=N2,
     FOCEXEC=v1adhere_datetest(NAME=N2),
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N2,
     FOCEXEC=v1adhere_datetest(NAME=N2),
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END]]>
		
<variables>
			
<variable field="dte" file="sqlout.mas" desc="dte" datatype="0" operation="" default="" name="dte" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="edte" file="sqlout.mas" desc="edte" datatype="0" operation="" default="" name="edte" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable></variables></request></requests>
</xml></BODY></HTML>

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

Sorry, Unable to show code.

put your code within the

[code]

[/code] markers and it will display correctly

Thanks,
Any good examples on running stored procedure variables.

Thanks, I'll start on new thread on using stored procedure.