Eclipse Community Forums: BIRT » Complex algorithm for generating a BIRT table

Help

Home

Home » Archived » BIRT » Complex algorithm for generating a BIRT table

Show: Today's Messages :: Show Polls :: Message Navigator

Complex algorithm for generating a BIRT table [message #657061]

Tue, 01 March 2011 13:57

Mircea S

Messages: 2
Registered: March 2011

Junior Member

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?

Report message to a moderator

Re: Complex algorithm for generating a BIRT table [message #657101 is a reply to message #657061]

Tue, 01 March 2011 15:35

Richard A. Polunsky

Messages: 212
Registered: July 2009
Location: Houston TX

Senior Member

Where is that dataset coming from? Whatever is generating that dataset ought to be modified to generate the dataset that meets your table needs.

I assume that the data is supposed to be grouped by devicename.

What you have in your requirement is basically a row-over-row comparison. You can do all of that logic within BIRT in the fetch (I think) event of the dataset. If you try to push this to the back end that currently provides your data, you'll find that you have to use a stored procedure or something that allows for SQL cursor operations - your transform is not something that maps to normal SQL set-based logic.

I would think that you can take the code from the java bean and translate it - but I don't have bandwidth right now to try a proof of concept.

Report message to a moderator

Re: Complex algorithm for generating a BIRT table [message #657148 is a reply to message #657101]

Tue, 01 March 2011 18:06

Mircea S

Messages: 2
Registered: March 2011

Junior Member

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 22:03]

Report message to a moderator