1 2 3 4 Previous Next 50 Replies Latest reply on Dec 14, 2018 11:29 AM by Stefan Hall Go to original post
      • 45. Re: Create report for count of open and closed tickets per month
        Abhay Bagalkoti

        Hello Karl,

         

        To show the data month wise you must map atleast 1 date field.

         

        For now what I tested and would suggest is -

        1. In the 3rd append sub-query i.e. for Open Count, just map or join the Submit Year to Submit Year.

        2. Remove the Submit Date or Last Resolved Date filter from 3rd sub-query if you are using it.

        3. Keep the Status filter as Not in list for Resolved\Closed\Cancelled.

        4. Now you will get the open counts for each month for that year. Use the Advance Function for Open Count and the select Accumulative Total which will add the previous month's count and then show the total at the end of last row.

         

        Let me know if this solution works, if not then will think through.

         

        Regards,

        Abhay

        • 46. Re: Create report for count of open and closed tickets per month
          Stefan Hall

          Maybe a bit off topic, but here the experts discuss this view.

           

          Do you also have performance problems if you run the report as a normal user (not administrator and not unrestricted access)? So a user with 3-4 support groups and some module permissions.

          With newRLS it takes me about 40 seconds, with noNewRLS about 20 seconds with only 76k tickets.

          Do you use cache functions for the view or reports?

          • 47. Re: Create report for count of open and closed tickets per month
            Karl-Anders Falk

            Stefan Hall

            Hi Stefan,

            similar experiences have been reported by some of my customers and the BMC support Staff are looking into it. I assume you have reported a support ticket for this already? If not please do so, together with detailed information on how to reproduce.

            Best Regards

            /KAF

            • 48. Re: Create report for count of open and closed tickets per month
              Karl-Anders Falk

              Abhay Bagalkoti

              Hi,

               

              the way I have calculated this before was to accumulate the number of submitted incidents from the first submitted incident, and the same for the first resolved incident, and the subtract these 2 numbers on a monthly basis.

               

              The result would be:

               

                                   Submitted Resolved Open(end of month)

              Jan 2018             10             6                    4

              Feb 2018             20           10                  14

              Mar2018                5            19                   0

              Apr  2018              1              0                   1

               

              I could not get what you suggested to deliver the above results.

              I have managed to accumulate submitted and resolved, but not subtract them

               

              Like this:

                                  ACC Submitted Acc Resolved            Open(end of month) Not Able to calculate

              Jan 2018               10                      6                                        10-6=       4

              Feb 2018               30                    16                                     30-16=     14

              Ma r2018               35                    35                                        35-0=       0

              Apr  2018              36                     35                                     36-35=        1

               

               

              Any other thoughts of other approaches/formulas to use?

               

              /KAF

              • 49. Re: Create report for count of open and closed tickets per month
                Abhay Bagalkoti

                Hi Karl,

                 

                Check the attached report that I have tested and it does provide correct results for all the counts.

                I am using accumulative total for only Open Counts and not for submitted and resolved.

                 

                The attachment contains only report and not the view, folders etc.

                While importing, the report need to be mapped to OOTB Incident Management view, Data Source and the folders that you want.

                The report has filters for a date range, company and assigned group.

                 

                NOTE: Once import completes and you refresh it, you might see some abnormal counts. However, check the SQL Query and all fields should be from "HPD:Help Desk" form name.

                You might see other form name like "HPD:WorkLog" for some fields. If this is the case, then you need to modify the report and the re-map the field by drag and drop or updating the calculated fields.

                 

                This happens because of a defect in Smart Reporting, where same name field mappings like Submit Date gets changed when importing which is not yet fixed\resolved.

                 

                Hopefully now I can say the report is complete.

                 

                Regards,

                Abhay

                2 of 2 people found this helpful
                • 50. Re: Create report for count of open and closed tickets per month
                  Stefan Hall

                  Thanks for your tip, if only it were that simple.

                  The support so far only answers with standard phrases and of course I used them all. The problem is in the AR core and how RLS is treated. So you see the OOb incident dashboard quickly displayed to a normal user?

                   

                  I will probably rather start my own thread again.

                  1 2 3 4 Previous Next