How to show timestamp in CA Boxi when Group is escalated and Assignee is assigned in SDM request ?

[Manirul_Hak]

Hi Folks,

 

I have a requirement to show the timestamp of Group escalation and  assignee assignment in boxi report.

 

The requests in service desk gets escalated from Group A to  Group B/Group C/Group D etc. and I need the timestamp for that group escalation. When group is escalated, Assignee is assigned after few hours or few days. So I need to fetch the timestamp of Assignee assignment as well

So In a nutshell, I need to show timestamp of Group escalation as well as Assignee Assignment.

 

Someone please help me how can I do this.

 

Thanks in advance

Manirul

[Manirul_Hak]

I believe this could be done through Activity Log but I  am not getting idea how to achieve that. If someone has some code please help me.

 

My report columns are  something like this:

 

Ref Num| Group|Category|Open Date|Group Escalation Date| Assignee Assignment Date| Close Date| Duration(Close Date-Assignee Assignment Date)

 

 

Thanks

Manirul

[Manirul_Hak]

Someone please help.

 

Thanks in advance

Manirul

[Manirul_Hak]

We are using SDM 14.1, Oracle Database 11g, CA BI 4.1 Sp3

 

Thanks

Manirul

[Manirul_Hak]

Can anyone please help me on this report?

 

Thanks

Manirul

[Raghu Rudraraju]

Manirul,

 

Checkout these two threads,  you may have to follow a similar approach for your report (Request Activity class)

 

time_stamp

 

Activity Log

 

 

_R

[Manirul_Hak]

Hi Raghu.Rudraraju

 

The links you provided are really helpful.

So I tried using the following custom query script in Query panel

 

SELECT

call_req.ref_num,act_log.action_desc,act_log.time_stamp

FROM

call_req,act_log

WHERE

act_log.call_req_id=call_req.persid

AND act_log.description LIKE ('%Transfer Group from "Group A" to "Group B"%')

 

But when I am validating the script, I am getting the following error:

 

Database Error:[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]AHD12005:Objet call_req in Majic  SQL query is unknown to dmsrvr at call_req, (IES 10901)

 

 

Please let me know if I am doing any mistake. We are using SDM 14.1, Oracle 11g and CA BI 4.1 sp3

[Manirul_Hak]

Hi Raghu.Rudraraju

One more thing client just clarified today:-

 

Group Assignment could be multiple times like Group A->Group B  and Group B->Group C or  Group C-> Group D or Group D-> Group C anything  that is possible.Same is applicable for assignee as well. Assignee could be assigned multiple times. So they need to show the timestamp for latest group transfer  and latest assignee assignment.

 

Is there any way we could do this?

 

 

Thanks

Manirul

[Raghu Rudraraju]

The query in reference below is not a query that you can use in WEBI/Crystal reports against SDM Universe.  Because that's using object layer.

 

SELECT

call_req.ref_num,act_log.action_desc,act_log.time_stamp

 

 

call_req e is a real table name in MDB

 

cr a is a table name that report can use  (if you are using the CA SDM universe or a derived universe based on that)

 

 

Checkout the  Request Activity  class in the universe,  you are looking for  Type Symbol = Transfer    and it should show all act_log entries which have a transfer activity.  Unless you have any extra filters, it should show all Transfers on a given ticket.

 

 

_R

[Manirul_Hak]

Hi Raghu.Rudraraju

Thanks.

So what I am planning right now is to fetch data from KPI as they want  timestamp  for more particularly three field  changes(Status,Group and Assignee) and I believe KPI ticket data covers these fields.

 

So I tried to get the values using the following fields in query panel and the result was not satisfactory.

 

Only I could see the group changes and the whole report was full of  group changes  and that is also for only one group change, I did not see anything related to assignee changes or anything else.

 

I used the following fields in the report query panel:-

 

Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

 

But I dont get the change details of three fields in any of the report data.

 

I was expecting something like this:

 

Report Output:-

Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

100|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

100|Assignee|Assignee A|Assignee B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

101|Group| Group C|Group D|20/04/2016 hh:mm:ss|20/04/2016 hh:mm:ss|xxxx

101|Assignee| Assignee P|Assignee Q|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

102|Group| Group E|Group F|20/04/2016 hh:mm:ss|20/04/2016 hh:mm:ss|xxxx

102|Status| Open|Acknowledge|21/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

103|Group| Group G|Group H|16/04/2016 hh:mm:ss|17/04/2016 hh:mm:ss|xxxx

103|Assignee| Assignee M|Assignee N|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

103|Status| Open|In Progress|18/04/2016 hh:mm:ss|19/04/2016 hh:mm:ss|xxxx

 

How can I achieve this?

 

 

All I am getting in report right now is:-

 

100|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

101|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

102|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

103|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

104|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

105|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

106|Group| Group A|Group B|20/04/2016 hh:mm:ss|21/04/2016 hh:mm:ss|xxxx

 

 

Someone please help. I badly require this kind of report.

 

Thanks

Manirul

[Manirul_Hak]

Hi All,

 

Any help for the same? someone please help.

 

 

Thanks

Manirul

[Allan Salas]

Hi Manirul_Hak,

 

I'm using MS SQL, but I think this can be usefull, please check the output for this querys on your environment:

 

• select * from audit_log where attr_name like 'assignee'
• select * from audit_log where attr_name like 'group'

 

I recomend you to use a custom universe pointing to some SQL view (in my case) this way you can easily get the data or try to make the joins at universe level with this object.

 

Regards,

[Manirul_Hak]

Thanks asalas Raghu.Rudraraju for valuable replies.

 

Now I could see the following output:-

 

Ref Num| Field Name|Field Value|Next Value|Previous Time|End Time|KPI Ticket Data Duration

 

100|Status | Acknowledged | In Progress | 21/04/2016 8:05:00 pm| 22/04/2016 11:21:00 am |xxxx

100|Status | Open| Acknowledged | 21/04/2016 07:21:00 pm | 21/04/2016 8:05:00 pm |xxxx

100|Group | Group B | Group C | 19/04/2016 9:44:45 am | 21/04/2016 05:34:16 pm |xxxx

100|Group | Group A |Group B | 18/04/2016 8:55:04 am | 19/04/2016 9:44:45 am |xxxx

 

But I want to show like this(The latest changes in Status,Group or any other KPI fields):-

 

100 |Status | Acknowledged | In Progress | 21/04/2016 8:05:00 pm| 22/04/2016 11:21:00 am |xxxx

100 |Group | Group A |Group B | 18/04/2016 8:55:04 am | 19/04/2016 9:44:45 am |xxxx

 

How can I show the latest changes on status,group,assignee or any other kpi fields? Please help.

 

Thanks

Manirul

[Manirul_Hak]

Hi Raghu.Rudraraju asalas

 

Any help for the same?

I want to show only the latest changes of the three particular fields (status,assignee and group) not all the changes in these fields. How can I do that?

 

 

Thanks

Manirul

[Manirul_Hak]

Anybody please share ideas how this can be done.

 

Thanks

Manirul

[Manirul_Hak]

Any help for the same?

 

Thanks

Manirul

[Leonio Alves]

Hi,

Try this way:

The result is:

I think that's what you want.

[Manirul_Hak]

Hi Lrralves,

Thanks for the reply. Actually we need to fetch the latest timestamp for these three changes(status,group,assignee).

We are fetching this from ktd table using Max(end_time)  and ktd.field_name  IN  ( 'status') and same is for  'group' and 'assignee'.

So we have created 3 derived tables for getting latest timestamp on 'status', 'group','assignee' changes(The scenario is ,these three fields can be changed multiple times and we need to show latest timestamp for that change) and we are getting correct result.

 

But the only problem is  the report is showing those records only where all the three fields('assignee','group','status') been modified but its not showing the records where only 'status' is changed or 'group' is changed or 'assignee' is changed or any combination of these three fields change.

 

We need to show all the records where any of these three fields is modified. So there is a need to join the derived tables as well so that we can show all the records where  any of the one, two or three fields been modified.

 

Please help to join these derived tables so that we can achieve the scenario.

 

Raghu.Rudraraju please look into this scenario.

 

Thanks

Manirul

[Allan Salas]

Hi,

 

Did you try making a SQL query and later using a new universe pointing to these results (use a SQL view)?

 

On my experience this would be easier than try to get this data at default universe level,

 

Regards,

[Manirul_Hak]

Hi asalas,

 

We have created these derived tables in custom universe not in default universe.

 

The report is also showing the correct result but the  only problem is  the report is showing those records only where all the three fields('assignee','group','status') been modified but its not showing the records where only 'status' is only changed or 'group' is only changed or 'assignee' is only changed or any combination of these three fields change.

 

We we need to  write the custom sql in report level in such a way that it  should show all the records  where only one field is changed (status/group/assignee), any of the two fields are changed (status,group/group,assignee/assignee,status) ,all the three fields are modified (status,group,assignee)

 

 

How could we achieve that?

 

Thanks

Manirul

[Manirul_Hak]

Hi All,

 

Any help for the same?

 

Thanks

Manirul

[Raghu Rudraraju]

Maybe the joins have to be left outer join or something similar to see the type of data you are expecting to see. 

 

Unfortunately, I'm a bit lost on this whole requirement. Maybe others have some ideas to offer here.

 

_R

 

 

 

[Manirul_Hak]

Hi Raghu.Rudraraju,

I can explain the scenario in brief.

I need to show all the tickets where status,group or assignee gets changed or any combination of these three fields get changed and latest timestamp of those change(These three fields can be modified multiple times so need to pick the latest timestamp).

Ticket number|Status Change Date| Assignee Change Date|Group Change Date

I have created three derived table which is picking the latest timestamp for these change. Whenever I am using these in query panel I can see only those records where all the three fields get changed.but I need to show all other  records too where one or two fields got modified.

 

How can we achieve that. If left outer join can help, how can I do this on derive table or do I have to join inside those derive table?

If you please give some example ,it would be really helpful.

 

Thanks

Manirul

[Manirul_Hak]

Hi Raghu.Rudraraju,

Hope the requirement is understood.Please let me know one thing , is it possible to join these three derived table in universe level or in report level as we could only join two  tables(group and assignee). We excluded the status related derived table.Please suggest any other easier way to join these three tables.

 

Meanwhile, Please share your ideas whether this can be achievable in any other way except  using derived table or any other easier way.

 

 

Thanks

Manirul

[Leonio Alves]

Manirul_Hak,

 

Have you tried to make a view in SQL database and create a universe from it?

I think it might be easier to manipulate the data in SQL rather than doing it in BOXI.