CA Service Management

  • 1.  Jasper Studio Query Input Control

    Posted Feb 23, 2018 12:59 PM

    I created a jasper studio report and I want to filter the results by category name.

    I created a query based input control with this query:

    select p.sym as sym from prob_ctg p
    inner join ca_tenant t
    on p.TENANT = t.id
    where t.name = '005-CONSTRUDECOR'
    and p.DEL = 0
    order by p.sym

     

    And the query that I using in my graph is that:

    select decode(type,'R','Request','I','Incident','P','Problem') as type, TotTipo, Viol from (
    SELECT type, count (type) as TotTipo, sum (sla_violation) as Viol from (
    select t.name, s.sym as status,
    enduser.last_name as usuario_final,
    analista.last_name, grupo.last_name AS GRUPO,
    ctg.sym as categoria, requested_by, cr.sla_violation,
    ((timestamp '1970-01-01 00:00:00 GMT' + numtodsinterval(cr.open_date, 'SECOND')) at time zone 'America/Sao_Paulo') open_date,
    TO_CHAR(TO_DATE('19700101000000','YYYYMMDDHH24MISS') + (cr.open_date/24/60/60),'DD/MM/YYYY HH24:MI:SS') DataAbertura,
    cr.active_flag, cr.summary, cr.ref_num, cr.type
    from call_req cr
    inner join ca_tenant t on t.id = cr.tenant
    inner join cr_stat s on s.code = cr.status
    inner join ca_contact enduser on cr.customer = enduser.contact_uuid
    left join ca_contact analista on cr.assignee = analista.contact_uuid
    left join ca_contact grupo on cr.group_id = grupo.contact_uuid
    left join prob_ctg ctg on cr.category = ctg.persid
    where t.name like '005%' and $X{IN, ctg.sym, catsym})
    where grupo not in ('N1-CommandCenter', '000BAY - N1 GMC', '000BAY - N2 ITSM',
    '000BAY - N2 GMC', '000BAY - N1 ITSM','Juridico')
    and open_date between $P{startDate} and $P{endDate}
    group by type
    order by TotTipo desc)
    where rownum < 4

    But the report is ignoring the category filter.

    Someone know how to configure this to work?



  • 2.  Re: Jasper Studio Query Input Control

    Posted Feb 23, 2018 01:10 PM

    Paulo, if you had jasper studio configured against the object layer (as per the video instructions in the documentation) the objects are already joined so you wont need to do much there.  If you are running it directly against the MDB I am not sure why it would ignore the filter.  SteveTroy may have an idea if he has come across that before.

    Jon I.



  • 3.  Re: Jasper Studio Query Input Control

    Posted Feb 26, 2018 01:58 PM
      |   view attached

    Paulo,

     

    Attached is an example of a jrxml that filters on category.  Give it a try and see if it works for you.

     

    Regards,

     

    Steve

    Attachment(s)

    zip
    Paulo.zip   1 KB 1 version


  • 4.  Re: Jasper Studio Query Input Control

    Posted Feb 26, 2018 01:03 PM

    Paulo, 

     

    A\ttach your jrxml and I will take a look. 

     

    Regards,

     

    Steve



  • 5.  Re: Jasper Studio Query Input Control

    Posted Mar 04, 2019 05:24 PM

    Just an FYI I don't think Jasper is honoring the LEFT JOIN command. It applies it as an INNER JOIN.