Hi. Would like some advice on if it is possible to send out an email with information rather than a report when the percent change between the current run time and the previous rundate is not equal to zero.

Email would be something like:

Between 22-MAY-2012 6:00 and 22-MAY-2012 13:00 the following took place:

  
Status     6:00       13:00  Diff    Percent Change
---------------------------------------------
Applied    150         153      3       2
Accepted    75          92     17      22
Deposit     20          20      0       0 
---------------------------------------------

If can't be done in an email, how would I do something like this in a report?

The data are refreshed 3 times a day at 6 am, 1 pm and 5 pm, and the output would show the most recent two time periods. Data would be at a summary level by Date_Updated, STU_POP, TERM, STATUS. Perhaps future capability could be added to drill down by college (if done on a report rather than email).

Data are stored in an oracle table. Sample Data:

  
DATE_UPDATED	STU_POP	TERM	COLLEGE_SORT	COLLEGE	STATUS	DISPLAY_STATUS	        TOT
5/21/2012 17:00	F	201230_45	3	BN	10.Applied	Applied	        2087
5/21/2012 17:00	F	201230_45	3	BN	11.Accepted	Accepted	1224
5/21/2012 17:00	F	201230_45	3	BN	17.Deposit	Deposit	        295
5/22/2012 6:00	F	201230_45	3	BN	10.Applied	Applied	        2087
5/22/2012 6:00	F	201230_45	3	BN	11.Accepted	Accepted	1230
5/22/2012 6:00	F	201230_45	3	BN	17.Deposit	Deposit	        294
5/22/2012 13:44	F	201230_45	3	BN	10.Applied	Applied	        2087
5/22/2012 13:44	F	201230_45	3	BN	11.Accepted	Accepted	1230
5/22/2012 13:44	F	201230_45	3	BN	17.Deposit	Deposit	        294

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

Are you saying that you want the information to appear in the Body of the email message and not as an attachment to an email?

Hi Don,

Was wondering about if that were possible to have in the email body. Otherwise attachment will suffice. In either case would like to know how to set up the report to be able to compare the various time frames.

search Focus Forum on EDAMAIL. A couple good posts exist.

- ABT

ABT, Thanks for the search term. I'll look at that.

Any idea how I can prepare the data to do the calculations from the sample data from the initial posting as without that, the email is irrelevant.

Hi sxschech,

From our technicals: you may want to try searching the Focal Point and techsupport site as there are many examples and techniques like this: Introduction to Date-Time Functions


Cheers,

Kerry

Hi Kerry,

Thanks for the link. I may not have been clear in what I was trying to do. For this request, I'm not trying to figure out the differences between dates and times, rather the difference between totals that are stored in the table. For example, if the total yesterday was 95 and the total today is 100, the difference would be 5. Since the data are stored vertically rather than in a col, I can't simply subtract 100 - 95. So am looking for how to transorm (pivot?) the data or does webfocus use another method for being able to compare data by dates?

Date Total
YesterdayAfternoon 95
YesterdayEvening 95
TodayMorning 100
TodayAfternoon 100
TodayEvening 102

If I ran the report yesterday difference = 0
If I ran the report this morning difference = 5
If I run the report tonight difference = 2

The calculations would be done by groupings for the different colleges and status (Applied, Accepted)

One way to approach this is to create a DEFINE or COMPUTE and step through the data to perform the calculation by using the LAST function. If the data is already sorted by college, status, and date, then you can use a DEFINE; if not, sort the data and use a COMPUTE.

DEFINE FILE xxx
 DIFF/I5 = IF (COLLEGE NE LAST COLLEGE) OR (STATUS NE LAST STATUS) THEN 0 ELSE (TOTAL - LAST TOTAL);
END
-*
TABLE FILE xxx
 PRINT COLLEGE STATUS DATE TOTAL DIFF
END

or...

TABLE FILE xxx
 PRINT TOTAL
 COMPUTE DIFF/I5 = IF (COLLEGE NE LAST COLLEGE) OR (STATUS NE LAST STATUS) THEN 0 ELSE (TOTAL - LAST TOTAL);
 BY COLLEGE
 BY STATUS
 BY DATE
END

This message has been edited. Last edited by: Dan Satchell,

Hi Dan,

Thank you for your reply. Your suggestion seems to be nearly working. The HTML output is only showing the date one time, which is odd considering that each row is unique. Below is the listing from SQLOUT

 
DATE_UPDATED	TERM	STATUS	TTL
5/15/2012 5:30	201230_45	10.Applied	9,633
5/16/2012 5:30	201230_45	10.Applied	9,636
5/17/2012 5:30	201230_45	10.Applied	9,638
5/18/2012 5:30	201230_45	10.Applied	9,639

 

This is what shows up in the WebFocus HTML output, what might I have missed in getting the dates to show up.

 
DATE_UPDATED	TERM	STATUS	TTL	DIFF
5/15/2012 5:30	201230_45	10.Applied	9633	0
	        201230_45	10.Applied	9636	3
        	201230_45	10.Applied	9638	2
	        201230_45	10.Applied	9639	1

 

 
TABLE FILE SQLOUT
PRINT 
     TERM
     STATUS
     TTL
     COMPUTE DIFF/I5 = IF STATUS NE LAST STATUS THEN 0 ELSE ( TTL - LAST TTL );
BY  STATUS NOPRINT 
BY  DATE_UPDATED
 

Use -

ON TABLE SET BYDISPLAY ON

or SET BYDISPLAY = ON

to see repeating by field values.

T

Hi Tony A,

I tried both your suggestions (one at a time and also tried both together) and still the report only displays one date per status. The dates are unique, so shouldn't they show up. Each row represents a different date and time period.

5/15/2012 5:30
5/16/2012 5:30
5/17/2012 5:30

Try this and see what output you get:

TABLE FILE SQLOUT
 PRINT 
     DATE_UPDATED
     TERM
     STATUS
     TTL
     COMPUTE DIFF/I5 = IF STATUS NE LAST STATUS THEN 0 ELSE ( TTL - LAST TTL );
 BY  STATUS        NOPRINT 
 BY  DATE_UPDATED  NOPRINT
.
.
.
END

Tried your last post and still the same result.

If you specified DATE_UPDATED as a PRINT field, there should be a date on each line. Are you saying this is not the case?

Hi Dan,

DATE_UPDATED is specified as a PRINT field, but is only showing the first date subsequent dates aren't displayed (they are not repeated values). Here is the code as it now stands:

 
SET BYDISPLAY = ON
ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select date_updated, term, status, sum(tot) as ttl
from HIST_ADM_DAILY
where stu_pop = 'F'
group by date_updated, term, status
order by term, status, date_updated
END
TABLE FILE SQLOUT
PRINT
     DATE_UPDATED
     TERM
     STATUS
     TTL
     COMPUTE DIFF/I5 = IF STATUS NE LAST STATUS THEN 0 ELSE ( TTL - LAST TTL );
BY  STATUS NOPRINT
BY  DATE_UPDATED NOPRINT
ON TABLE SET BYDISPLAY ON
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END

 

Do you have records with missing date values? Otherwise, what you describe seems impossible. If you are certain you are running the code you displayed (and not a copy of your program in another folder) and do not have missing date values and are not seeing a date value on each line, I would open a case with IBI because this is abnormal behavior.

There are values for all the fields, no nulls or missing values. I'm running the code I posted as is and it is not located in any other folder. Date_updated is Date Format, Tot is Number Format all others are Varchar2.

I'll open a case per your recommendation. Thanks for your assistance.