CA Service Management

Expand all | Collapse all

Jaspersoft Report Question

  • 1.  Jaspersoft Report Question

    Broadcom Employee
    Posted 02-19-2019 02:22 AM

    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



  • 2.  Re: Jaspersoft Report Question

    Broadcom Employee
    Posted 02-19-2019 03:10 AM

    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



  • 3.  Re: Jaspersoft Report Question

    Broadcom Employee
    Posted 02-19-2019 05:20 AM

    Hi

    thank you for your quick response.

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

    regards 



  • 4.  Re: Jaspersoft Report Question

    Broadcom Employee
    Posted 02-19-2019 10:41 PM

    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