This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Scheduled report

Our customer is looking to email daily its Technicians with a list of incidents, which are open 14 days or more. Could not find a predefined report, and using Builder I'm unable to use relative date values for "Open time".

Can this be done or do we have to look into Reporting server ?

  • Verified Answer

    +1

    There is a diamond in the rough called Report builder.
    Once you understand the process you can quickly create custom reports.
    You can make a simple Report in Report Builder to display Requests Open prior to X amount of days.

    This will allow you to select the day where Incidents are still open before that date.

    Please reply whether or not this is a solution you are looking for...

    Let's Begin

    Log in as a Technician/Supervisor with Report Builder permissions.

    REPORTS > Builder

    • Name: Incidents Open before Date x
    • Location: Request | Incident
    • Sharing: Role | Supervisor, Technician

    Since Request Incident was selected tables related will be in the Available Entities
    Select the Entities

    • Entities: Incident, Request Customer, Request Technician

    Now for the Select Statements for your report
    These will appear on the right side of the screen
    Also you can have the report Display a Name for that column

    Select

    • Field; Incident > ID | Display Name:Incident #             
      +
    • Field: Incident > Open Date | Display Name: Open     
      +
    • Field: Incident > Close Date | Display Name: Close     
      +

    (Be sure to press the "+" after each selection to add it to the query)
    Select Test and this will display all the Incidents by Incident #, Open, Close

    Next let's add the Customer and Technician to the table
    You can always add the Display Name label afterwards and recommended if there are a lot of Selects.

    Select

    • Field: Request Customer > Username | Display Name: Customer     
      +
    • Field: Request Technician > Username | Display Name: Technician 
      +

    Select Test and this will display all the Incidents by Incident #, Open, Close, Customer, Technician
    Selects can not be reordered on the right, so this might take a couple reentries to sort properly.

    Now let's select a date parameter with WHERE parameter.
    By checking the "Runtime Parameter" it will prompt for the expression.

    Where

    • Runtime Paramter (unchecked)
    • Field: Incident > Close Date
      Expression: Is Null             
      +
    • Runtime Paramter (checked)
    • Field: Incident > Open Date
      Expression: Before        
      +

    Select Test and this will prompt for the Before Date for your Incident list.
    Select the calendar Icon and select a date.
    Selecting Create will display all the Incidents by Incident #, Open, Close, Customer, Technician before that date

    Since these are filtered on Open Incidents Only we can remove the Close Date since it is presumed not closed.

    Upper left select the "-" next to "Close"
    This will remove it as a column in the report.

    Now lets view the list by oldest to newest request.
    (Order By will be displayed under the Selected Tables on right)

    Order By

    • Fields: Open | Ascending
      +

    Select Test and this will prompt for the Before Date for your Incident list.
    Select the calendar Icon and select a date.
    Selecting Create will display all the Incidents by Incident #, Open, Close, Customer, Technician before that date from oldest to newest.

    If you wish to see the Database query that can be run @ the database level you can select the "down arrow" next to "Query"

    Once it works to your satisfaction this Report will appear in the list of canned Reports with an astrisk (*) indicating it is created and can be modified.

    Go to REPORTS > Requests
    Select 

    • Process: Incident Reports
    • Report Type: *Incidents Open before Date x
    • Incident Open Date <: (select calendar to select the date)

    Select Create

    This will display the report you are requesting and is flexible on the date.

    Paul Pedron