1 2 Previous Next 16 Replies Latest reply on Aug 19, 2019 6:29 AM by Abhay Bagalkoti

    Grouping incidents by Month

    Omar Latif
      Share This:

      Hello

       

      Can i ask how to group incident data by month. I have two fields in my report: 'Submit date' and 'Number of incidents'. I have formatted the submit date as per the screenshot below:

      However, when i run the report, each month appears on multiple lines as per the screenshot below:

      Could you please explain how i could aggregate the data so that the total incidents are grouped by month, i.e.

      Jan-19   23

      Feb-19   44

      Mar-19   37

      and so on...

       

      Also, could you explain how to sort the months in calendar order.

       

      kind regards

      Omar Latif

        • 1. Re: Grouping incidents by Month
          Stefan Hall

          You have configured the display property of the date, not the data behind it. However, these are used for groupings. Means you have groups on a daily basis.

           

          Create an additional column in the view, where you format the date exactly like this yyyymm. You use this column for sorting and grouping

          1 of 1 people found this helpful
          • 2. Re: Grouping incidents by Month
            Abhay Bagalkoti

            Hello Omar,

             

            Create 3 calculated fields based on Submit Date field i.e -

            1. Submit Year using 'Year, ARJDBC' pre-defined function

            2. Submit Month Number using 'Month, ARJDBC', and

            3. Submit Month Name using 'Month Name, ARJDBC'

             

            Pull all these fields along with Number of Incidents. Make sure to follow sequence as year, month number and then month name and last number of incidents.

            Format the year and month number and remove decimals and thousand separator.

            From the drop down option select Advance Function for Submit Month Name field, and then select text, concatenate columns and select Submit Year.

            Rename the field using format as required.

            Hide the calculated fields year and month num, as these are used for sorting.

             

            The output and query will be as follows -

             

            SELECT DISTINCT

               YEAR(`HPD:Help Desk`.`Submit Date`),

               MONTH(`HPD:Help Desk`.`Submit Date`),

               datename('mm',`HPD:Help Desk`.`Submit Date`),

               COUNT(`HPD:Help Desk`.`Incident Number`)

            FROM `AR System Schema`.`HPD:Help Desk`

            WHERE (

               `HPD:Help Desk`.`Incident Number` IS NOT NULL

            )

            AND (

               `HPD:Help Desk`.`Status` NOT IN ('Resolved', 'Closed', 'Cancelled')

            )

            GROUP BY

               MONTH(`HPD:Help Desk`.`Submit Date`),

               YEAR(`HPD:Help Desk`.`Submit Date`),

               datename('mm',`HPD:Help Desk`.`Submit Date`)

             

             

            Hope this helps.

             

            Regards,

            Abhay

            2 of 2 people found this helpful
            • 3. Re: Grouping incidents by Month
              Suresh Kumar Palivela

              Hi Omar,

               

              We can achieve sorting using Org reference Code.

              Create the Org reference code using below steps.

              Navigate to Administration -> Admin Console -> Reference Codes -> Click on Add -> Create like below

              Use this above org reference code in the report like below.

              Edit the report -> Click on field on which we need to apply custom sort -> Format -> Edit -> Select 'Reference Code' option from Format list box and select Reference Code from Reference Type list box.

               

              Hope this helps.

               

              Thanks,

              Suresh

              1 of 1 people found this helpful
              • 4. Re: Grouping incidents by Month
                Omar Latif

                Hi Stefan

                 

                I tried your method but it only formats the timestamp as a month and does not group the data in the final report.

                • 5. Re: Grouping incidents by Month
                  Omar Latif

                  Many thanks Abhay.

                   

                  Your solution was very helpful and it does the job really well.

                   

                  Can I ask you another quick question based on this solution?

                   

                  Is it possible to drill down from your 'monthly' data table above into a daily or hourly table. So for example if I click on  the total incidents raised for Feb-19, it should drill down into all the days for Feb and show the corresponding totals for those days.

                  01   25

                  02   43

                  03   53

                  ...

                   

                  I know how to do this by creating hierarchies in dimensions, i.e. link product cat tier 1 to product cat tier 2, and then to tier 3. And then drill into each.

                  But can the same hierarchy be created using month > Day > hour for incidents created, using the submit date?

                  • 6. Re: Grouping incidents by Month
                    Abhay Bagalkoti

                    Hello Omar,

                     

                    What I can think as of now is -

                     

                    1. Create a copy of the Monthly report. Use all 3 calculated fields in the filter. This is help in mapping the filters from monthly report to daily report for drill through. When you click on Feb-19 or the number of inc for this month, the month and year value will be passed to the daily\child report.

                    2. In the daily report, create another calculated field using 'Date, ARJDBC' per-defined function. Use this field in the report which will display records date wise. Don't remove any fields and in fact you can hide the month-year field. So, we now have Date for that month i.e. Feb-19 and number of inc's for that month.

                    3. If you want to drill to further level i.e hour level, then create 2nd child report, with the 'Date, ARJDBC' per-defined function as filter (which is created in 1st child report) and in report canvas level use Submit Date with timestamp and Incident ID to get the list.

                     

                    Example -  (have not tried but just a thought)

                     

                    AFAIK, Date hierarchy in smart reporting is not supported.

                     

                    Regards,

                    Abhay                                                                                                                                                                                                                                     

                    • 7. Re: Grouping incidents by Month
                      Omar Latif

                      Hi Abhay

                       

                      I have tried but for some reason I cannot get the parent/child link to work.

                      I have created a child report with the same fields as the parent report. I have placed these 3 fields in the filter section as well.

                      In addition, I have created a calculated field called 'Submit day' using Date, ARJDBC. Please see screenshot below of Data tab of child report.

                      Also, on the chart tab, Submit Day is on the horizontal axis and number of incidents in the vertical axis.

                      Now, on the parent report, I have also placed the 3 existing calculated fields in the filter section, and chosen a 'Drill Through' report as per the screenshot below:

                      On the 'Related Reports' tab, I have placed the child report on the Drill Through section, and in the links on the right hand side, mapped all the fields from the master field to the child filter, and clicked update. See screenshot below:

                      But when I run the report, the drill down does not seem to work. Is there is step that I am missing?

                      Your help would be much appreciated.

                      • 8. Re: Grouping incidents by Month
                        Abhay Bagalkoti

                        Hello Omar,

                         

                        No, you are not missing any step. In fact, I was wrong for 1 step i.e. I forgot that the advance function which concatenates month and year field, will not pass the same values to drill through report link. It will pass as only month name and not month-year.

                        And other things is that, for the drill through mappings, hidden fields are not available to map to child report filters.

                         

                        So to fix this, both fields must be kept separate which will pass year and month name separately. The master report will have 3 columns year, month and number of incidents.

                        If you are OK with just a little compromise on the display, then drill through from month > day > hour is possible.

                        Make a not that, don't use pop-up window for hyperlink when you setup drill through. Pop-up window works for only one level.

                         

                        Let me know in case any questions.

                         

                        Regards,

                        Abhay

                        • 9. Re: Grouping incidents by Month
                          Omar Latif

                          Hello Abhay

                           

                          Still no joy. I cannot seem to drill down into the child report correctly.

                           

                          In the child report I have Submit Year, and Submit Month Number in the filter area.

                          In the table area I have Submit Day, and Number of Incidents.

                          In the chart area, Submit day in the x-axis, and Number of Incidents in the y-axis.

                           

                          On the parent report I have Submit Year, and Submit Month Number in the filter area.

                          In the table area I have Submit Year, Submit Month Name, and Number of Incidents

                          In the chart area, Submit Month Name is in the x-axis, and Number of Incidents in the y-axis.

                           

                          The reports are joined using drill through. The Submit Year and Submit Month Name are joined on both reports as filters.

                          But when I click on Feb-19 in the parent report, it brings back all the days in the year in the child report (instead of only the days in Feb). Also, on the parent report, the months are in alphabetical order and not in calendar order since I took out the Month Number Field.

                          • 10. Re: Grouping incidents by Month
                            Abhay Bagalkoti

                            Hello Omar,

                             

                            I attached a zip file that has all 3 reports which are linked and works properly. I didnt do any charting or formatting, and in last report I used OOB Submit Date field which is Date Timestamp where you will get hourly incidents, you can then format it as per requirement.

                             

                            Hope this helps now.

                             

                            Regards,

                            Abhay

                            1 of 1 people found this helpful
                            • 11. Re: Grouping incidents by Month
                              Omar Latif

                              Hi Abhay

                               

                              Is there a way to open this report in remedy smart reporting console? The file itself has hundreds of lines of xml code.

                              • 12. Re: Grouping incidents by Month
                                Abhay Bagalkoti

                                Hello Omar,

                                 

                                If you have admin \ advanced permission then you can import the XML file into Smart Reporting console.

                                This XML has only reports. While importing, you need to specify the data source which will default ARSystem, for view use the OOB Incident Management View and you can choose any category and sub-category.

                                Once imported, you will be then able to refresh the report.

                                Start with INC Monthly report.

                                 

                                For more details on importing the contents - follow these steps Export and import repository - Documentation for Remedy IT Service Management Suite 9.1 - BMC Documentation

                                 

                                Regards,

                                Abhay

                                1 of 1 people found this helpful
                                • 13. Re: Grouping incidents by Month
                                  Omar Latif

                                  Hi Abhay

                                   

                                  With regards to your message on 08-Jul-2019 in the chain above for grouping incidents by month and year. I have tried to create a cross tab report by bringing in the operational category Tier 1 field. However, when I create a stacked column chart with the month-year on x-axis and ops category on the y-axis, then the month-year is sorted in alphabetical order, rather than actual calendar month order. I think this is because you formatted the month as text by concatenating month name and year.

                                  Is there a way to show the months in the correct order on the x-axis?

                                  • 14. Re: Grouping incidents by Month
                                    Abhay Bagalkoti

                                    Hello Omar,

                                     

                                    Yes this is the defect until 9.1 SP4 version.

                                    But its resolved in 1902 version.

                                     

                                     

                                    However, if you are on lower than 1902 version, then a quick workaround - If you don't want the table to be displayed below chart, then you can use the default Submit Date field and format it as MMM YYYY with Month as granularity, and use that in chart and then sort it as per table order. This will sort the chart correctly.

                                     

                                    Let me if this works.

                                     

                                    Regards,

                                    Abhay

                                    1 2 Previous Next