1 Reply Latest reply on Feb 6, 2018 3:05 PM by Christopher.Bakken

    Customized Reporting Issues

    SSmith14 Rookie

      I need to create a customized report, but everything I try is not even remotely close or generates an error. I need to pull data in a single report selectable for day/week/month/quarter/year.

       

      What I'm looking for is a report that shows which specific technician (by name) logged how may minutes in journal duration overall (as a sum only) for the time period requested - as well as how much time was logged for specific programs (by name) in Service Requests and Incidents, for the same time requested period.

       

      dbo.Journal  - CreatedBy (*The creator of the journal)
      dbo.Journal  - Duration

       

      dbo.ServiceReq - Program (*This is a custom field)
      dbo.ServiceReq - TotalTimeSpent


      dbo.Incident - Program (*This is a custom field)
      dbo.Incident - TotalTimeSpent

       

      I'm at a loss. This is not my specialty. Can we bring back Crystal Reports now? :\


      Can anyone provide assistance? It is greatly appreciated.

        • 1. Re: Customized Reporting Issues
          Christopher.Bakken SupportEmployee

          It would be helpful if you included some of the queries you have tried and the errors you ended up seeing.

           

          This is going to be pretty straight forward in terms of SQL, but requires an understanding of how the data is being calculated and entered.

           

          ----

          You pull in the parameters in the query so you have your dates:

          declare @st_date datetime:

          declare @en_date datetime;

           

          set @en_date = (@en_datein);

          set @st_date = (@st_datein);

           

          You can then use those parameters in the queries

           

          Which specific technician (by name) logged how may minutes in journal duration overall (as a sum only) for the time period requested

           

          Select sum(Journal.TimeSpent) as TimeSum,

          from Journal

          Where Journal.Owner = @user_param

          AND Journal.CreatedDateTime >= @st_date

          AND Journal.CreatedDateTime <= @en_date

           

          You can see all of them by dropping the owner clause and add some grouping.

           

          select Owner, sum(Journal.TimeSpent) as TimeSum from Journal group by owner

           

           

          as well as how much time was logged for specific programs (by name) in Service Requests and Incidents, for the same time requested period.

          This is going to be a bit different as you have to apply some grouping

           

          Select Program, sum(Incident.TotalTimeSpent) as TimeSum from Incident

          Where Incident.CreatedDateTime >= @st_date

          AND Incident.CreatedDateTime <= @en_date

          group by Program