CA Service Management

Hi Experts,

I want to create a report in which, i want to take count of all ticket which are opened two days ago along with assignee_combo_name and category_symbol

format should be like this

Assignee_Name    Category_symbol  Count_of Ticket_opened_2_days_ago

Please help.

Regards,

Ritu

Hi Experts,

as of now my below is working 

Select assignee_last_name,category_symbol,
sum(case when active = 1 and open_date >= PdmAddDays(-2) then 1 else 0 end) as T1
from cr
WHERE type = 'I'
group by assignee_last_name,category_symbol

but it is not working for assignee_combo_name field, getting below error

Please help, TIA.

Any quick help would be must appreciated. 

Hi I belive that answer to you problem is described in this document

"AHD12019:Cannot use sync_fetch for a query" error - CA Knowledge  

you can try this query instead

Select (cnt.last_name + ' ' + cnt.first_name) as combo_name1,category_symbol,
sum(case when active = 1 and open_date >= PdmAddDays(-2) then 1 else 0 end) as T1
from cr
inner join cnt  on  cr.assignee_userid =cnt.userid
WHERE cr.type = 'I'
group by   (cnt.last_name + ' ' + cnt.first_name)  ,category_symbol

Thanks @Aleksandar_Stancic , let me try suggested input and get back to you.

@Aleksandar_Stancic in combo_name1 field, it is not  giving combo_name field value, please check below screenshot

and giving error in query "no viable alternative at input '('" , what could be the issue, please help

try this

Select CONCAT(cnt.last_name,', ' , cnt.first_name) as combo_name,category_symbol,
sum(case when active = 1 and open_date >= PdmAddDays(-2) then 1 else 0 end) as T1
from cr
inner join cnt  on  cr.assignee_userid =cnt.userid
WHERE cr.type = 'I'
group by  category_symbol, cnt.last_name,cnt.first_name

Very Nice.  Yesterday  I found CONCAT via GOOGLE (Good friend of mine)  but query still falied and I couldn't determine why.  Come to find out it was the group by clause.  Never tried  

group by  category_symbol, cnt.last_name,cnt.first_name. 

Glad I read this post before going further.  

it is not working, SteveTroy , is it working for you?,  can you give me your whole query.

I want to get assignee_combo_name along with category_symbol and count of ticket

This query works

Select CONCAT(cnt.last_name,', ' , cnt.first_name) as combo_name, category_symbol,
sum(case when active = 1 then 1 else 0 end) as T1
from cr
inner join cnt on cr.assignee_userid = cnt.userid
WHERE cr.type = 'I'
and cnt.last_name = cr.assignee_last_name
group by category_symbol, cnt.last_name,cnt.first_name

Thanks SteveTroy for sharing the query.

Have you encountered any performance issue during running this query, i dont know exact reason but this query is taking very long time to even show 10 records.

Its Working now after making changes in where clause 

Select CONCAT(cnt.last_name,', ' , cnt.first_name) as combo_name, category_symbol,
sum (case when cr.open_date > PdmAddDays (-3) and (cr.resolve_date != PdmAddDays (-2) and cr.resolve_date != PdmAddDays (-2)) then 1 else 0 end) as T1
from cr
inner join cnt on cr.assignee_userid = cnt.userid
WHERE cr.open_date >PdmAddDays(-10) and cr.type = 'I'
and cnt.last_name = cr.assignee_last_name
group by cnt.last_name,cnt.first_name,category_symbol

Thanks SteveTroy and @Aleksandar_Stancic  for help and support.

Now coming back to original question, how i can show count of tickets which are active two days ago, any idea ?

Is it possible that jaspersoft can store today's count of active ticket which are having open date > PdmAddDays (-3) and add yesterday resolve ticket of open date > PdmAddDays (-3) and again add resolve ticket count of two day ago ticket having open date > PdmAddDays (-3).

Please help, how this can be done in jaspersoft.

TIA

Hi SteveTroy,

Can below query help me to get the desired result, in which i want to get count of ticket which were open and active two days ago , not sure if it is logically correct ?

Select CONCAT(cnt.last_name,', ' , cnt.first_name) as combo_name, category_symbol,
sum (case when (cr.open_date > PdmAddDays (-3) and active = 1) and ((cr.resolve_date = PdmAddDays (-2) and cr.open_date > PdmAddDays (-3)) and (cr.resolve_date = PdmAddDays (-1) and cr.open_date > PdmAddDays (-3) ) ) then 1 else 0 end) as T1
from cr
inner join cnt on cr.assignee_userid = cnt.userid
WHERE cr.open_date >PdmAddDays(-60) and cr.type = 'I'
and cnt.userid = cr.assignee_userid
group by cnt.last_name,cnt.first_name,category_symbol

I am not sure if it is logically correct either.  I suggest breaking the SUM SQL query into multiple SUM SQL and confirming the counts individually.  

Thanks SteveTroy

SteveTroy , how i can eliminate/ignore/hide one row which is having all zero value in multiple SUM SQL query ?

Currently when i am doing group by assignee and category it also producing combination for old record because i am not applying any condition in where clause, so any luck if i can hide those row from final output of multiple SUM SQL

SteveTroy , i tried  T0 <> 0 or T1 <> 0 or T2 <> 0  in where clause but still not working , any clue would must appreciated here. :-) 

In Jasper add a print when condition and suppress the row of the values are 0.  

Thanks SteveTroy , it worked but giving blank page now.

if previous query is correct use this