I have a few reports on CABI 14.1 and i require the last activity log of the tickets in a colum
I used the Action description of Request activity folder but it berings me all the changes of each ticket and i one need the last one
Do you know how displays that field or what is the query that i must use ?
You could try to look for all Action Descriptions (of a given ticket) with the latest last_mod_dt or highest id (higest act_log.id or highest alg.id )
But I need to do it in CABI not in the SQL studio , by the moment displays all the action logs , i tried with a formula but dont show me the last description of active log
1) You'd definitely need a join between Request Activity class -> Call Request ID and Request Details -> ID
2) Then you'd need the a CABI function like maximum(Request Activity class -> ID) See this: (https://communities.ca.com/message/116975088?commentID=116975088#comment-116975088 )
The join between Request Activity class -> Call Request id and Request Details id need to do in SQL ? then i need to use the CABI function maximum?
No, it can be done in CABI report itself.. Something like this : How to avoid getting a cartesian product of two table records in a CA Service Desk Manager CA Business Intelligence repo…
Hi Raghu !
But i dont know what is the sintax of the condition of maximum or higest alg.iaction_desc for cr.id
Can you help me with that?
Thanks for your help
Now i have this Query
SELECTcr.type_symbol,cr.tenant_name,cr.ref_num,cr.request_area_symbol,cr.assignee_combo_name,cr.open_date,cr.summary,cr.status_symbol,cr.last_mod_dt,alg.action_descFROMcr,algWHERE(cr.active = 1ANDcr.id = alg.id
Sorry slapping on something really basic here if that helps -
select cr.ref_num, cr.type_symbol, alg.action_desc
from cr, alg
where alg.id=(select max(alg1.id) from alg alg1 where alg1.call_req_id = cr.persistent_id)
and alg.call_req_id = cr.persistent_id
Thaks for the help Raghu ,
Sorry for the lack of knowledge
If Raghu's previous post has helped, can you please just go ahead and mark that as the answer on this one?
oh no problem at all. I'm not sure about the performance of the query with huge amount of data though. You may need to tweak it a bit per need