Jaspersoft Report Question

[Muhammad Arif]

Hi 

I  created a report who calculate the time of incident from Open to Resolve.

 

the report is working fine but now the customer have a below queries which need to be address:

 

1- Report should calculate business hours only.

2- weekend time should not be calculated in report.

 

Example

If Incident open on Thursday@ 10:00 AM (Last working day) and close on Sunday(First Working day) 10:00 AM

then the report should show the total time 9 hours instead of 64 hours.

 

any idea how can we create the query.

 

regards

[Legacy User]

Hi,

the 'pdmdowntime' function does this - see the section labelled 'PDM Functions for Business Reports' at Write CA Business Intelligence Reports - CA Service Management - 17.1 - CA Technologies Documentation.  It can only be used in the select list and using the SDM ODBC connector - here is an example:

select cr.ref_num
, cr.open_date
, cr.close_date
, pdmseconds(pdmdowntime('abc','Mon - Fri { 8:00 am - 5:00 pm }',cr.open_date, cr.close_date)) 
as downtime
from cr
where cr.close_date is not null
  and cr.ref_num not like 'AM%'
  and cr_ref_num not like 'SD%'
  and cr.ref_num not like 'UAPM%'

The first argument to 'pdmdowntime' apparently has no effect, but it is a required field. The field receiving the value from 'pdmseconds' should be of type Long.  I find the name of the function somewhat misleading, as it calculates the elapsed time between the two dates, minus any 'downtime' from the workshift.  In the example below ticket '43' is opened on July 2 and closed on July 3 with 2h59m elapsed after deducting the 'downtime' between 5pm and 8am:

Regards,

James

[Muhammad Arif]

Hi

thank you for your quick response.

it is possible in jaspersoft because I am using sql level query not object level.

regards 

[Legacy User]

You would have to code your own equivalent of the 'pdmdowntime' calculation.  It would be easier just to create an SDM ODBC datasource so you can use the 'pdm' functions, if you have the option to do that.

 

Regards,

James