3 Replies Latest reply on Apr 12, 2017 9:53 AM by Mike90

    Custom Dashboard Reporting

    Mike90 Apprentice

      I'm positive what i am looking for is likely not supported as the reporting module is fully capable of this type of reporting, but digging into the records from the report is not so easy as you cant just click a metric to dig in.

       

      I would like to recreate a report that management uses regularly as a dashboard. Any Idea how i can create a dashboard that would look something like this?

       

       

      Parameters: Tasks by team (Incident OR Service Request), count of active tasks aggregated into the column groups above. 

       

      I imaging i would need to use the SLA clock for each task but im not sure how to do that right now.

       

      Any ideas?

        • 1. Re: Custom Dashboard Reporting
          daveb1 Apprentice

          I tried adding a new field and then creating a calculated field business rule with an expression similar to:


          $(if DateDiffSQL("day",  CurrentDateTime(),  CreatedDateTime) >= -2
            then "   Less Than 2"
            else if DateDiffSQL("day",  CurrentDateTime(),  CreatedDateTime) < -2 &&
                    DateDiffSQL("day",  CurrentDateTime(),  CreatedDateTime) >= -5
                 then " 3 to 5 Days"
                 else "More than 5 Days")

           

          And then attempted to use the new field in a pivot table as the Top Axis 1.  However in my version (2015.2.2) I received a runtime error in the preview that the function DateDiffSQL is not valid (Error was "Data access error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.DateDiffSQL", or the name is ambiguous..").  This appears to be a bug or a limitation in pivot tables -- Because if you add the field as a column to a list, it works as expected when the list is displayed.  You may not need to do this next step as it might be fixed in a newer version.  I created a scalar UDF in the database called DateDiffSQL with the same function prototype (varchar, datetime, datetime, varchar).  This allowed the query to run and generate a pivot table similar to what you were looking for.  I had to put spaces before the strings in the then clauses to get the top axis to sort in the correct order.

           

          Image of pivot table showing task age ranges with count by team

          2 of 2 people found this helpful
          • 2. Re: Custom Dashboard Reporting
            Mike90 Apprentice

            Ok, this gives me an idea on how i can accomplish this. Ill try this out in a few days once we get some bugs worked out. Thank you for your input. 

            • 3. Re: Custom Dashboard Reporting
              Mike90 Apprentice

              I tried this with a few minor tweaks and it seems to work for me in 2016.1.1 

              DateDiffSQL will soon be deprecated and might be why you ran into a run-time error if you have a newer DB version. 

               

              $(if DiffDays(CurrentDateTime(), CreatedDateTime) >= -2
                 then "0-2 Days"
                 else if DiffDays(CurrentDateTime(), CreatedDateTime) < -2 &&
                            DiffDays(CurrentDateTime(), CreatedDateTime) >= -5
                          then "3-5 Days"
                          else if DiffDays(CurrentDateTime(), CreatedDateTime) < -6 &&
                                     DiffDays(CurrentDateTime(), CreatedDateTime) >= -10
                          then "6-10 Days"
                          else if DiffDays(CurrentDateTime(), CreatedDateTime) < -11 &&
                                     DiffDays(CurrentDateTime(), CreatedDateTime) >= -30
                                  then "11-30 Days"
                                  else "Over 30 Days")