| Complex algorithm for generating a BIRT table [message #657061] |
Tue, 01 March 2011 08:57  |
Eclipse User |
|
|
|
I have a birt dataset containing the following columns, sorted by 'time':
devicename String
operatorname String
time Date Time
action String
info String
'action' can have one of four possible values:
MAINTENANCE_START
DEVICE_DEFECT
DEVICE_REPAIRED
MAINTENANCE_STOP
I want to generate a table in my report using this data. I should have a table entry for each row of the dataset where action has the value 'MAINTENANCE_STOP'. My table should have the following entries:
Time
Operator
Status
Duration
Maintenance_Info
* 'Time' should take its value from the 'time' column of the last 'MAINTENANCE_START' before the current 'MAINTENANCE_STOP'
* 'Operator' should be the operator who performed the 'MAINTENANCE_START'
* 'Status' - if between the last 'MAINTENANCE_START' and the current 'MAINTENANCE_STOP' there was either a 'DEVICE_DEFECT' or 'DEVICE_REPAIRED' event, this should have the value 'Device not available' else it should be 'Device available'.
* 'Duration' - should be the difference (interval) between the 'MAINTENANCE_STOP' and the last 'MAINTENANCE_START'
* 'Maintenance_Info' - should be a concatenation of all the 'info' strings between the last 'MAINTENANCE_START' and the current 'MAINTENANCE_STOP'.
This was originally done in a java bean for a web application. In java it wasn't so hard to do, however, in BIRT I am not sure it is even possible. Can you give me any advice on how I can do something like this?
|
|
|
|
| Re: Complex algorithm for generating a BIRT table [message #657148 is a reply to message #657101] |
Tue, 01 March 2011 13:06  |
| Eclipse User |
|
|
|
Thanks for the reply. Unfortunately I am not allowed to add stored procedures to the database. However I did write an sql query (over a period of several days) which does all the logic. I am not proud of the monster I created though:
SELECT TABLE1.DeviceName, StartTime, Status, Duration, MaintenanceInfo, TABLE2.action_name, table2.time FROM
( SELECT ad.device_name AS DeviceName,
COALESCE((SELECT ada1.time FROM acc_device_action as ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59' ORDER BY (ada.time-ada1.time) limit 1), '2011-02-21 11:40:31') AS StartTime,
CASE WHEN COALESCE((SELECT max(ada1.time) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59'), '2011-02-21 11:40:31')
<= COALESCE((SELECT max(ada1.time) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE (ada1.action_name = 'DEVICE_DEFECT' OR ada1.action_name = 'DEVICE_REPAIRED') AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59'), '2011-02-21 11:40:31'::timestamp-'00:01'::interval)
THEN
'Device NOT Available'
ELSE
'Device Available'
END AS Status,
(SELECT COALESCE(min(ada.time - ada1.time), ada.time - '2011-02-21 11:40:31') FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ada1.action_name = 'MAINTENANCE_START' AND ada1.time < ada.time AND ad1.device_name = ad.device_name AND ada1.TIME > '2011-02-21 11:40:31' AND ada1.TIME < '2011-02-22 23:59') AS Duration,
ada.ID AS ID
FROM acc_device_action AS ada
JOIN acc_device AS ad ON ada.acc_device_id = ad.id
JOIN acc_user AS au ON ada.acc_user_id = au.id
WHERE ada.TIME > '2011-02-21 11:40:31'
AND ada.TIME < '2011-02-22 23:59'
AND ada.action_name = 'MAINTENANCE_STOP'
ORDER BY ad.device_name, ada.TIME ASC ) AS TABLE1
JOIN
(SELECT ada.action_info AS MaintenanceInfo,
ad.device_name AS DeviceName,
ada.action_name,
ada.time AS time,
COALESCE((SELECT min(ada1.ID) FROM acc_device_action AS ada1 JOIN acc_device AS ad1 ON ada1.acc_device_id = ad1.id WHERE ad1.device_name = ad.device_name AND ada1.action_name = 'MAINTENANCE_STOP' AND ada1.time >= ada.time), 0) AS ID
FROM acc_device_action AS ada
JOIN acc_device AS ad ON ada.acc_device_id = ad.id
WHERE ada.TIME > '2011-02-21 11:40:31'
AND ada.TIME < '2011-02-22 23:59'
AND (ada.action_name='MAINTENANCE_START' OR ada.action_name='DEVICE_DEFECT' OR ada.action_name='DEVICE_REPAIRED' OR ada.action_name='MAINTENANCE_STOP' ) ) AS TABLE2
ON TABLE1.ID = TABLE2.ID AND TABLE1.DeviceName = TABLE2.DeviceName
(The hard-coded dates should be replaced by report parameters)
This is likely very inefficient, as it performs a large number of queries on the database. I would have preferred doing just a basic query, and then doing all the logic within birt. However, if this is the only way that it can be done can you at least give me any advice on optimizing the query (if what I did there is at least a bit understandable).
[Updated on: Tue, 01 March 2011 17:03] by Moderator |
|
|
|
Powered by
FUDForum. Page generated in 0.05176 seconds
Home