1 2 Previous Next 19 Replies Latest reply on Aug 21, 2019 10:57 AM by Abhay Bagalkoti

    Last 12 months report in smart reporting

    Raju Mangali
      Share This:

      Hi All,

       

      I have a requirement to show reports for the last 12 months. I have tried multiple ways to get this output but I did not get exactly what I expected.

       

      I would need your valuable suggestions or inputs to sort out my requirement. Kindly put some light on this.

       

      Requirement: For example this month is Aug 2019 then I need the report for last 12 months like as below with sample data(data should be anything but I would need month should be as I showed below).

       

      Sep 2018,  Oct 2018, Nov 2018,  Dec 2018, Jan 2019, ........ and upto Aug 2019.

        34                    45          67               24               87      ......                        90

       

       

      I would like to thank you for the help in advance.

       

       

       

      Regards,

      Raju M

        • 1. Re: Last 12 months report in smart reporting
          Suresh Kumar Palivela

          Hi Raju,

           

          You can achieve this with ARJDBC Predefined functions.

          Create below calculated fields using ARJDBC functions.

          1. Submit date using 'Year ARJDBC ' function like below.

          2. Submit date month using 'Month ARJDBC' function like below

           

          3. Pull these fields in report along with other fields (number of incidents)

          4. Now, on submit date month field, apply advanced function -> select text -> Concatenate columns -> select Submit date from Add column list box  -> select Add space option button.

          5. Hide submitdate column.

          Output will be like below

          Hope this helps.

           

          Thanks,

          Suresh

          3 of 3 people found this helpful
          • 2. Re: Last 12 months report in smart reporting
            Raju Mangali

            Hi Suresh,

             

            First of all thank you for the update.

             

            Actually you are showing reports based on submit date for a specific year, I do not want like that instead I want the report like as I mentioned in my first discussion.

             

            For example if I click on the year 2019 then it has to show last 12 months data from Aug 2019 to Sep 2018(means it has to be go back and show it in ascending or descending order)

             

            Click on 2019...>

             

            Sep 2018, Oct 2018, Nov 2018, Dec 2018, Jan 2019, Feb 2019, Mar 2019, Apr 2019, May 2019, Jun 2019, July 2019, Aug 2019.

             

            Hope this will give clear instructions to get the output.

             

            Please let me know how do I achieve this.

             

             

            Regards,

            Raju M

            • 3. Re: Last 12 months report in smart reporting
              Bhushan Desai

              Hi Raju,

               

              I see your report requirements are matching with below thread which is also resolved by Abhay Bagalkoti

              Please check if this helps.

               

              Re: Grouping incidents by Month

               

               

              Thanks,

              Bhushan

              1 of 1 people found this helpful
              • 4. Re: Last 12 months report in smart reporting
                Ganesh Gore

                Looping in Abhay Bagalkoti .

                Abhay, do you have any idea how to do this?

                • 5. Re: Last 12 months report in smart reporting
                  Abhay Bagalkoti

                  Hello Raju,

                   

                  Yes you can achieve this. Create the report as given in the thread that Bhushan has shared.

                  When you pull the date field e.g. Submit Date in filter section, go to advance settings and select the pre-defined period as "Today minus 12 months".

                   

                  This way the report will not prompt for Date period and will always show the last 12 months depending on the day the report is refreshed.

                   

                  Regards,

                  Abhay

                  3 of 3 people found this helpful
                  • 6. Re: Last 12 months report in smart reporting
                    Bhushan Desai

                    Thanks Abhay

                     

                    Raju Mangali -  For quick reference putting the steps here

                     

                    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'

                    4.Add Submit Date in filter section, go to advance settings and select the pre-defined period as "Today minus 12 months"

                     

                    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.

                     

                    Your report sql will be like below -

                     

                    SELECT DISTINCT

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

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

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

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

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

                    WHERE (

                       `HPD:Help Desk`.`Submit Date` BETWEEN '08/13/2018 12:00:00 AM' AND '08/13/2019 11:59:59 PM'

                    )

                    GROUP BY

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

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

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

                     

                     

                    Thanks,

                    Bhushan

                    3 of 3 people found this helpful
                    • 7. Re: Last 12 months report in smart reporting
                      Raju Mangali

                      Hi Bhushan & Abhay,

                       

                      I will check suggested instructions and update you the status.

                       

                      Thank you.

                       

                       

                      Regards,

                      Raju M

                      • 8. Re: Last 12 months report in smart reporting
                        Raju Mangali

                        Hi Bushan & Abhay,

                         

                        I have tried to bring out the result as per your instructions but I have seen some different issues when I added the below calculated fields to Columns.

                         

                        SubmitMonthNumber, SubmitMonth, SubmitYear.

                         

                         

                        And also if I add the Submit Date field to Filter section then I could see below error message.

                         

                        Submit Date. Here in screen shot the TRANS_Date is nothing but it's a Submit date.

                         

                         

                        I would like to know if there is any defect in smart reporting, kindly let me know what is resolution for this to move further.

                         

                         

                        Note: I have tried to modify the format of date in different ways but no use for me, also tried in all possible ways to sort out this issue but still I did not get any positive.

                         

                        Regards,

                        Raju M

                        • 9. Re: Last 12 months report in smart reporting
                          Abhay Bagalkoti

                          Hi Raju,

                           

                          Can you show the calculation for SubmitMonthNumber field?

                          It is showing as dimension field whereas it should be a metric field with a pre-defined function as Month ARJDBC.

                           

                          Regards,

                          Abhay

                          • 10. Re: Last 12 months report in smart reporting
                            Raju Mangali

                            Hi Abhay,

                             

                            Thank you for the reply. As I said I have tried in all possible ways but I see each time different error message related to date format issue.

                             

                            As requested please find the below screen shot for reference.

                             

                             

                             

                            Regards,

                            Raju M

                            • 11. Re: Last 12 months report in smart reporting
                              Abhay Bagalkoti

                              For Submit Month Number, use the pre-defined function as shown below -

                               

                               

                              For month name, you can use either of "Month Name, ARJDBC" or "Month Short Name, ARJDBC" which will be a dimension field. Join this with Submit Year field using advance function.

                              Submit month number field is used for only sorting and you may hide it too.

                              • 12. Re: Last 12 months report in smart reporting
                                Raju Mangali

                                Hi Abhay,

                                 

                                Yes, I have used the same way but still I get the different types of error messages.

                                 

                                 

                                 

                                Regards,

                                Raju M

                                • 13. Re: Last 12 months report in smart reporting
                                  Abhay Bagalkoti

                                  Ok then might not be related to this field.

                                  As a test, can you remove other fields except year, month number, month name and see if you get any error. You may use submit date as filter to few months just to restrict some data.

                                  • 14. Re: Last 12 months report in smart reporting
                                    Raju Mangali

                                    After adding Submit Date field I see the below error.

                                     

                                    After removing Filter section then I could see another error message related to Year, Month.

                                     

                                     

                                    Regards,

                                    Raju M

                                    1 2 Previous Next