7 Replies Latest reply on Jul 17, 2013 8:04 AM by jasoncadman

    Query to show our top 10 customers last month

    Apprentice

      I've got as far as a query that shows Incidents grouped by raise user, but then I'd like to be able to sort by the count and show the top 10.

       

      Any ideas are gratefully recieved!

      top10.png

        • 1. Re: Query to show our top 10 customers last month
          MarcelloCentineo Specialist

          Hi Rachael,

           

          In my point of view that will be not possible cause a query can't "count". It's just a (stupid) query...

          I think you need a crystal report for this...

           

          But if I'm wrong please correct me someone ;-)

           

          Regards from Germany

          Marcello

          • 2. Re: Query to show our top 10 customers last month
            Apprentice

            Thanks but i've no Crystal training to do that and not really keen to spend more money on getting them written. I've been just creating 'Reporting Dashboards' for now like the one for 'Review of last month' below.

            Its a shame as I was hoping to have this showing in the bottom right so IT Managers & Trainers could see it when they wanted to.

             

            Maybe the upgrade to 7.5 will have something...

             

            last month.png

            • 3. Re: Query to show our top 10 customers last month
              MarcelloCentineo Specialist

              Hi,

               

              7.5 will not have more query options to count / show the “Top 10”

              But it will have “Management Information / MI” where you can display “Trends”…

              Ask LANDesk for a small MI demo and you can decide if it will makes sense to buy it in 7.5

               

              Marcello

              • 4. Re: Query to show our top 10 customers last month
                Specialist

                Maybe it could help to get the data from an SQL view.

                 

                You should be able to create a data connection to your SQL view and then run a query on the data connection.

                 

                Just an idea...

                • 5. Re: Query to show our top 10 customers last month
                  jasoncadman Expert

                  That's a great idea Thomas!!

                  • 6. Re: Query to show our top 10 customers last month
                    Expert

                    The reporting capabilities of Service Desk are pretty good if your a Crystal Reports expert; otherwise their pretty poor.  I have taken the trip down the dark side to learn a bit of Crystal Reports.  I can't think of a harder to use piece of software.  There is zero intuitive navigation and useaility.  There are a billion features but it seems like their all thrown in and burried 3 windows deep.  The help forums are a maze of interrelated forums that never seem to have the answer in the version you want it in.

                     

                    However, if you do figure out their madness and how to do things, there is a ton of power and flexability in the tool.  If you can dream it, they can do it.

                     

                    Like everyone else I do find it hard to produce meaningful management reports with just the basic capabilities in the standard query.

                     

                    In your query you can limit the results to of the SQL view to 10 per page, that should give you your top 10.

                    • 7. Re: Query to show our top 10 customers last month
                      jasoncadman Expert

                      I've just put this together to create a view for the top 10 locations based on number of incidents (with a few additional criteria)

                       

                      select

                                top 10

                       

                                 l.usr_location as Site

                                 , COUNT(*) as SiteCount

                                from im_incident i

                                          join usr_location l

                                          on i.usr_location=l.usr_guid

                                          join pm_process p

                                          on p.pm_guid=i.pm_guid

                                          join usr_systemcategory s

                                          on s.usr_guid=i.usr_systemcategory

                                          join im_incident_category c

                                          on i.im_category_guid=c.im_guid

                                          join lc_lifecycle_description lc

                                          on p.pm_lifecycle_guid=lc.lc_guid

                                WHERE

                                          p.pm_creation_date >=dateadd(day,datediff(day,0,GetDate())- 3,0)

                                          and s.usr_title <>'Active Directory'

                                          and c.im_title <> 'Password & PIN Reset'

                                          and s.usr_title <> 'RA'

                                          and lc.lc_title <> 'LHIS Stock Order'

                                          and lc.lc_title <> 'LHIS Standard Order'

                                          and lc.lc_title <> 'Password Reset'

                                GROUP BY l.usr_location

                                order by COUNT(*) desc

                       

                       

                      Which shows up in the dash like this:

                       

                      Capture.PNG

                       

                      Hope that Helps

                       

                      Jason