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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
SUMMING TIME
 Login/Join
 
Platinum Member
posted
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


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
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.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
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


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
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



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
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,


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Platinum Member
posted Hide Post
Sorry, Unable to show code.


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
put your code within the
[code]
[/code] markers and it will display correctly


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Platinum Member
posted Hide Post
Thanks,
Any good examples on running stored procedure variables.


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Platinum Member
posted Hide Post
Thanks, I'll start on new thread on using stored procedure.


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders