10 Replies Latest reply on Oct 18, 2012 6:16 AM by karenpeacock

    How to stop duplicates in % fixed within 15mins SLA

    Apprentice

      Hi All

       

      We have a Crystal report that was customised by Landesk for us a few years ago but we have found an error/oversight on it.

       

      Basically, if a call has been resolved but then unresolved and then resolved again and within the SLA time period, it shows up twice in the report.

       

      Is there a way to only calculate on the latest resolution?

       

      Thanks in Advance.

       

      Cheers

       

      Tony

        • 1. Re: How to stop duplicates in % fixed within 15mins SLA
          karenpeacock SupportEmployee

          Hi Tony

           

          In this article Time to Resolution report (replacement for Incident Duration By Days report) there is a database view called vw_max_resolution which you can use to bring back only the latest resolution record for each incident.  You need to run this script against your database to create the view.

           

          The problem will be then modifying your report to add this in and link it to your other tables within the report.  As a brief guide within Crystal you would:

           

          - Switch off Smart Linking in Crystal (if not already done).Turning off smart linking in Crystal Reports XI and Crystal 2008

           

          - Then go to Database - Database Expert and then add the vw_max_resolution view from your Data Source.

           

          - Then click on the Links tab to link this view into your report.

           

          - As a general rule this will link between the im_incident and im_incident_resolution table using the im_guid from im_incident and im_incident_resolution to the inc_guid in the view and then the im_id from the im_incident_resolution table to the Max_Res_SerialNo in the view.

           

          -  This should do the trick but the report may also need some further work depending on how it has been designed, in which case you may need to get some more assistance from the report Consultancy team.

           

          Regards

          Karen

          • 2. Re: How to stop duplicates in % fixed within 15mins SLA
            Apprentice

            Hi Karen

             

            Many thanks for the info, I had an inkling that it was something to do with upgrades and/or  views.

             

            The only problem is the im_guid does not exist in our im_incident table? I assume it should be pm_guid?

             

            The report still reports duplicate entries so I assume the report needs tweaking, but I can’t see where (from my limited knowledge of Crystal), do I need to remove any of the views that were already there? The only on I would imaging it to be is possibly vw_lastincidentresolution?

             

            Thanks again

             

            Tony

             

            • 3. Re: How to stop duplicates in % fixed within 15mins SLA
              Apprentice

              Hi,

               

              You should also be using Distinct Count and not just Count in your report, that will parse the duplicates...

              • 4. Re: How to stop duplicates in % fixed within 15mins SLA
                karenpeacock SupportEmployee

                Hi Tony

                 

                Yes apologies, it should be pm_guid not im_guid.  Another think to look at (or your DBA to look at) might be the syntax of the selection of records from the database.  You can do this via Database - Show SQL Query.

                 

                As Jenna mentioned, in terms of adjusting the totals on the report you could alter the formulas or summary fields which are giving you the totals to be distinct count on the incident pm_id.  This won't suppress any duplicate records, but will adjust the totals, if this is what you are after.

                 

                Lastly from what you say, it looks as if there was already a view in the report called vw_lastincident resolution which should already have been doing the job of the vw_max_resolution that I pointed you to.  I think that perhaps on this basis you might be able to go back to say that the report is not working as it should?  It's very difficult to say without seeing the report and the original report requirement but perhaps you could pursue this.

                 

                Regards

                Karen

                1 of 1 people found this helpful
                • 5. Re: How to stop duplicates in % fixed within 15mins SLA
                  Apprentice

                  Hi

                   

                  Going off on a slight tangent here, we have another report we created that shows our SLA performance etc, all works perfectly apart from we get duplicate entries if the call has been unresolved and then re-resolved within the reporting period.

                   

                  i've tried differnt linking etc and still get the duplicated call that have been unresolved and then re-resolved.

                   

                  Is there a way around this?

                   

                  Thanks

                   

                  See attached image of Linkss

                  • 6. Re: How to stop duplicates in % fixed within 15mins SLA
                    karenpeacock SupportEmployee

                    Hi

                     

                    It looks like you have im_incident_resolution linking to im_incident but it needs to go through vw_lastincidentresolution.  In other words, im_incident links to vw_lastincidentresolution and then vw_lastincidentresolution links to im_incident_resolution.  This should do the trick.

                     

                    Regards

                    Karen

                    • 7. Re: How to stop duplicates in % fixed within 15mins SLA
                      Apprentice

                      Hi Karen

                       

                      Thanks for the quick reply

                       

                      I've done the change but still does not work (I've more than likely done it wrong ). I've attached a better view of the links for you to see.

                       

                      Thanks

                       

                      Tony

                      • 8. Re: How to stop duplicates in % fixed within 15mins SLA
                        karenpeacock SupportEmployee

                        Hi

                         

                        I can't spot anything wrong from that linking I'm afraid so it must be something else selecting the duplicates.  You can see the SQL statement it is running by going to Database - Show SQL Query.  Perhaps your DBA would be able to spot something with this?

                         

                        Not being familiar with the report, my only other suggestion would be that you could instead group the report by incident id.  This should suppress any duplicates.  To do this:

                         

                        • Save a copy of the report before you start.
                        • Select a field on the details of the report - ideally incident id.
                        • Right-click and select Insert Summary.
                        • Select Distinct Count and click Insert Group.
                        • Then ok and ok.

                         

                        This should insert a group into the left hand margin of the report.  Delete the distinct count subtotal which it inserted for you into the group.  Move all of your fields within the details section into the group section and then suppress the details section (right-click on it in the margin and select suppress). 

                         

                        This may work but it all depends on the design of the report and if you already have some groups on it I'm afraid.

                         

                        I think this will probably be all I can advise on this report via the forums so if the report was written by us you may need to speak to Support for further assistance.

                         

                        Regards

                        Karen

                        • 9. Re: How to stop duplicates in % fixed within 15mins SLA
                          Apprentice

                          Hi Karen

                           

                          Thanks for the info and help, unfortunately I still get the duplicates. I might try and re-build it from scratch again and see what happens.

                           

                          Cheers

                           

                          Tony

                          • 10. Re: How to stop duplicates in % fixed within 15mins SLA
                            karenpeacock SupportEmployee

                            Hi

                             

                            I've just remembered an article I wrote which sort of covers your situation.  Good job I write things down - can't trust my brain!  In this article we've already covered off the first 2 suggestions but the last point might be helpful in tracking down which table is bringing in the duplication:

                             

                            Remove tables from the report

                             

                            If you have tried checking the linking between the tables and the join type of the tables, save a copy of your report and remove tables from it one by one.  You do this via  Database - Database Expert option from the menu and then select the Data tab.  You will need to also remove any fields on the report which reference this table too.  If you remove one table at a time and then try running the report again, at some stage you should find that you do now see all the records you want on the report.  It will be the table that you last removed from the report which wasn't linked correctly and so was stopping records from displaying on your report.  Revert back to your saved copy of the report and investigate the linking further...

                             

                            Regards

                            Karen