Help - hoping I can do this in BIRT

Need some help with what appears to be a complicated report request.

Table Data:

User StartTime EndTime Activity
Al 5:05 AM 5:15 AM Loading
Al 5:28 AM 5:40 AM Shipping
Al 5:48 AM 5:55 AM Loading

I need the difference between the EndTime and the next StartTime. Like diff between 5:15 (endtime of loading) and 5:28 AM (startttime of shipping).

This is in Infomix which doesn't appear to have a rownum(Oracle) equivalent.

Any help is GREATLY appreciated.

Thanks,

Al

Find more posts tagged with

Comments

johnw

Thats fairly easy. You define a global variable called lastEndTime. Then you create a computed column in your dataset called timeDifference. You would set it either in the onFetch event, or in the expression, with the following script (using the Event Handler method) 
 
//////////////////////////// 
var lastEndTime = reportContext.getGlobalVariable("lastEndTime"); 
 
if (lastEndTime == null) 
{ 
row["differenceInMinutes"] = 0; 
row["differenceInHours"] = 0; 
} 
else 
{ 
row["differenceInMinutes"] = DateTimeSpan.hours(lastEndTime, row["StartTime']); 
row["differenceInHours"] = DateTimeSpan.minutes(lastEndTime, row["StartTime"]); 
} 
 
reportContext.setGlobalVariable("lastEndTime", row["EndTime"]); 
 
//////////////////////////////// 
 
It will take some playing around with, but thats the general idea. 
 
John 
 
 
<blockquote class='ipsBlockquote' data-author="'al91206'" data-cid="66557" data-time="1279565177" data-date="19 July 2010 - 11:46 AM">
Need some help with what appears to be a complicated report request. 
 
Table Data: 
 
User StartTime EndTime Activity 
Al 5:05 AM 5:15 AM Loading 
Al 5:28 AM 5:40 AM Shipping 
Al 5:48 AM 5:55 AM Loading 
 
I need the difference between the EndTime and the next StartTime. Like diff between 5:15 (endtime of loading) and 5:28 AM (startttime of shipping). 
 
This is in Infomix which doesn't appear to have a rownum(Oracle) equivalent. 
 
Any help is GREATLY appreciated. 
 
Thanks, 
 
Al </blockquote>

thuston

Actually from one row to the next? Or over a larger span.

You could sum the times used in each row end-start then subtract that from a work day to get the total idle time.
Al 5:05 AM 5:15 AM Loading
Al 5:28 AM 5:40 AM Shipping
Al 5:48 AM 5:55 AM Loading
==
1hour(60min) - ( 10min + 12min + 7min ) = 31min Idle from 5-6am

If you have to go from one row to the next, you'll need to ensure your sorting is never altered and then you'll have to have a variable to carry the previous row's end time.

create it in initialize and for the control's OnCreate
<control value> = row["StartTime"] - varEndTime;
varEndTime = row["EndTime"];

al91206

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="66558" data-time="1279575824" data-date="19 July 2010 - 02:43 PM">
Thats fairly easy. You define a global variable called lastEndTime. Then you create a computed column in your dataset called timeDifference. You would set it either in the onFetch event, or in the expression, with the following script (using the Event Handler method) 
 
//////////////////////////// 
var lastEndTime = reportContext.getGlobalVariable("lastEndTime"); 
 
if (lastEndTime == null) 
{ 
row["differenceInMinutes"] = 0; 
row["differenceInHours"] = 0; 
} 
else 
{ 
row["differenceInMinutes"] = DateTimeSpan.hours(lastEndTime, row["StartTime']); 
row["differenceInHours"] = DateTimeSpan.minutes(lastEndTime, row["StartTime"]); 
} 
 
reportContext.setGlobalVariable("lastEndTime", row["EndTime"]); 
 
//////////////////////////////// 
 
It will take some playing around with, but thats the general idea. 
 
John </blockquote>
 
 
I got this to run successfully 2 different ways. 
 
1. I added the script above into a computed column (expression editor). 
2. I added the script to the onFetch script for my data set 
 
Question for both methods: How do I display: row["differenceInMinutes"] and row["differenceInHours"]? 
 
Thanks! 
 
Al

johnw

Drop a Data element into your table, and set the expression to something like: 
 
row["differenceInHours"] + ":" + row["differenceInMinutes"] 
 
John 
 
<blockquote class='ipsBlockquote' data-author="'al91206'" data-cid="66561" data-time="1279582617" data-date="19 July 2010 - 04:36 PM">
I got this to run successfully 2 different ways. 
 
1. I added the script above into a computed column (expression editor). 
2. I added the script to the onFetch script for my data set 
 
Question for both methods: How do I display: row["differenceInMinutes"] and row["differenceInHours"]? 
 
Thanks! 
 
Al </blockquote>

thuston

<blockquote class='ipsBlockquote' data-author="'al91206'" data-cid="66561" data-time="1279582617" data-date="19 July 2010 - 04:36 PM">
Question for both methods: How do I display: row["differenceInMinutes"] and row["differenceInHours"]? 
 
Thanks! 
 
Al </blockquote>
 
You probably just need to select the Table and go to Properties - Binding and refresh to bind the new columns to the Table.

al91206

<blockquote class='ipsBlockquote' data-author="'johnw'" data-cid="66562" data-time="1279592330" data-date="19 July 2010 - 07:18 PM">
Drop a Data element into your table, and set the expression to something like: 
 
row["differenceInHours"] + ":" + row["differenceInMinutes"] 
 
John </blockquote>
 
That's exactly what I thought. I named my data element MinDiff and I got an error saying "Column binding "MinDiff" has referred to a binding "differenceInMinutes" which does not exist" 
 
I've attached a similar sample from ClassicModels to see if anyone can get this to work. The sample uses dates instead of times but the method is the same. The script is on the onFetch script for the dataset.

thuston

Just create a computed column that looks like this. It worked in my quick test. 
DiffInHours: 
<pre class='_prettyXprint _lang-auto _linenums:0'>var mylastEndTime = reportContext.getGlobalVariable("lastEndTime");
reportContext.setGlobalVariable("lastEndTime", row["ENDDT"]);

if (mylastEndTime == null)
0;
else
DateTimeSpan.hours(mylastEndTime, row["BEGDT"]);</pre>

johnw

Try using thustons example below, its the same logic, but as a computed column instead of being handled in script. 
 
The example you posted isn't opening, it looks like a HTML file embedded in XML, not an actual BIRT report design file. 
 
John 
 
<blockquote class='ipsBlockquote' data-author="'al91206'" data-cid="66580" data-time="1279645527" data-date="20 July 2010 - 10:05 AM">
That's exactly what I thought. I named my data element MinDiff and I got an error saying "Column binding "MinDiff" has referred to a binding "differenceInMinutes" which does not exist" 
 
I've attached a similar sample from ClassicModels to see if anyone can get this to work. The sample uses dates instead of times but the method is the same. The script is on the onFetch script for the dataset. </blockquote>

al91206

<blockquote class='ipsBlockquote' data-author="'thuston'" data-cid="66581" data-time="1279651009" data-date="20 July 2010 - 11:36 AM">
Just create a computed column that looks like this. It worked in my quick test. 
DiffInHours: 
<pre class='_prettyXprint _lang-auto _linenums:0'>var mylastEndTime = reportContext.getGlobalVariable("lastEndTime");
reportContext.setGlobalVariable("lastEndTime", row["ENDDT"]);

if (mylastEndTime == null)
0;
else
DateTimeSpan.hours(mylastEndTime, row["BEGDT"]);</pre></blockquote>
 
Totally worked!!!!! 
 
Thanks very much - and for anyone else looking I've uploaded another copy of my example - now with the thuston's computed column calculation (using days though instead of hours). 
 
Thanks everyone!