CA Service Management

  • 1.  Not able to schedule report in CA Boxi

    Posted Aug 24, 2018 06:40 AM

    We have created a new Universe and added two tables (call_req + act_log) from database to get the report.

     

    Report got generated and saved. We got the below sql query after generating the report.

     

    SELECT
    call_req.ref_num,
    ca_contact2.middle_name,
    ca_contact2.first_name,
    ca_location.location_name,
    ca_site.name,
    prob_ctg.sym,
    call_req.description,
    call_req.status,
    DATEADD(ss,call_req.open_date,'1970/01/01 05:30:00')

    ,
    DATEADD(ss,call_req.close_date,'1970/01/01 05:30:00')

    ,
    ca_contact.last_name+' , '+ ca_contact.first_name,
    act_log.type,
    act_log.description
    FROM
    call_req,
    ca_contact ca_contact2,
    ca_location,
    ca_site,
    prob_ctg,
    ca_contact,
    act_log
    WHERE
    ( act_log.call_req_id=call_req.persid )
    AND ( call_req.category=prob_ctg.persid )
    AND ( ca_contact.contact_uuid=call_req.assignee )
    AND ( call_req.customer=ca_contact2.contact_uuid )
    AND ( ca_contact2.location_uuid=ca_location.location_uuid )
    AND ( ca_location.site_id=ca_site.id )
    AND
    act_log.type IN ( 'CL','ST' )

    Now We want to schedule the daily report so we can get the previous day data. for that I have added below query in where clause;

    AND
    call_req.close_date > dateadd(day,datediff(day,1,getdate()),0)

    and validated the query and got no error in query.

     

    When again tried to run the query after adding the additional query got below error;

     

    Kindly help with the addition query to schedule the report to get previous day close tickets.

     

    Regards,

    Kripesh



  • 2.  Re: Not able to schedule report in CA Boxi

    Broadcom Employee
    Posted Aug 27, 2018 04:25 PM

    Hi Kripesh,

     

    Try to use SQL server studio and try to run the same query, if you get the same error try selecting only top N rows to see if it works.

    "Arithmetic Overflow" error occurs when the limits of the data types used are breached.

    The issue could be that you are exceeding the # of rows while creating the joins.

     

    Thanks,

    Scott



  • 3.  Re: Not able to schedule report in CA Boxi

    Posted Sep 03, 2018 11:34 AM

    I think this error could be eliminated if you use PDM-functions instead of your own.

     

    A sample working query to get data for just previous day:

     

    SELECT
      cr.ref_num, cr.group_name, cr.open_date
    FROM cr
    WHERE
      ( cr.open_date  BETWEEN  PdmToday(-1,-1) AND PdmToday(+1,-1)   AND cr.group_name  IN  ( 'My Group','Your Group'  )
      )

     

    Please try this and if it works we can further talk about pdm functions.

     

    Thanks

    ArunavaS



  • 4.  Re: Not able to schedule report in CA Boxi

    Broadcom Employee
    Posted Sep 07, 2018 12:44 PM

    cbscoreteam 

    Do you require any additional assistance on this topic?

    If not, please mark one of the provided responses as correct so that this thread can be closed.