Skip to Content
author's profile photo Former Member
Former Member

CRM BW Service request analysis Reports

Dear All,

I am using the data source '0CRM_SRV_REQ_INCI_H' to extract the service requests created. And based on which i need to create SLA report in BI. I have extracted the data from the mentioned DS and loaded to DSO '0SRQ_DS10' and then to cube '0SRQ_C10'.

My requirement is to populate the report with the number of ticket/request open, inprocess, close as on given date, like below.

Service ticket '123456' is created on 1st april and it is loaded to BI and now my report gives all the ticket no with status where user have entered the selelction value 2nd april. Am picking all ticket less than user entered date.

Now the service requet is closed in CRM and if it comes to BI with new status on 3rd april and if i pull out the report in 4th april am getting two entries for ticket '123456' but i need to show only 1 record with closed status which is recent one.

And if the user enters 2nd or 3rd april i should display one record with status 'Open'.

How to acheive this?

Thanks

Prem

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Aug 29, 2011 at 02:45 PM

    can you write under cmod to delete duplicate records or

    better create a DSo in Bw load all the data in their and do a delta from that dso to your current dso, use same fields it should use delta dtp and will show only single recards.

    did you try to use handle duplicate records in dtp?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 29, 2011 at 10:15 PM

    Exception Aggregation should be helpful in your case. Check if exception aggregation maximum can be put on some fields like status or changed date.

    You need to identify a field which can you give the latest entry for a ticket. Exception aggreation on that kind of a field should work if you have ticked number in rows. but if report can be drilled up, you will need use exception aggreation at tickt no level, I am not sure if you can achive that in the query by putting exception aggreation twice (CKF1 and then CKF2).

    If not, you can create a conactenated characteristics and load it with ticket number + the field you will identify to give you the latest entry. Then use this characteristics to put exception aggregation upon.

    I hope it helps.

    Regards,

    Gaurav

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi,

      Thank all of you for the reply.

      I have addressed the same by changing the date field (ticket changed date) of the record in transformation(DSO to Cube) for pending and closed ticket to the changed record date.

      i.e., when the new status come for service request the existing the record is knocked off since all the characteristics update to cube is the same to knowoff. Hence i modified the date field to the date of the new status date.

  • Posted on Sep 04, 2011 at 11:03 AM

    Hi,

    In BEx Query designer,

    Create a Formula Variable (CKF)> properties of this CKF>Under Aggregation Tab> exception Aggregation> Select the Last Value>Reference Char>Select Status type( Open, Closed, Processing etc..).

    So, whatever the latest value of that Service Ticket Characteristic as per the different Status Types will be displayed.

    Regards,

    Suman

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 09, 2011 at 03:52 AM

    Self Answered and find the my discussion. My requirement is not show the recent status of the ticket, but to give the status as on given date what was the status. So i have done some changes in while loading the data from DSO to Cube, as discussed above.

    Thanks

    Prem

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.