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?