Fullscreen Image

Create an Advanced Report for the Dispatch Dashboard

The lessons in this tutorial introduce you to the process you need to follow to add an Advanced Report to the Dashboard in the Dispatch Module.  The Dashboard is made up of counters and statistics defined by you consisting of Advanced Reports.

After completing this tutorial, you will be able to add an Advanced Report to the Dashboard of the Dispatch Module.  There are some things you need to be aware of.  You should be familiar with writing SQL statements.  You are required to write out the SQL for the Advanced Report.  You should also be familiar with the Reporting Module in TMS.

For the purposes of this tutorial, we’ll add a count of Active work orders by Type Description to the Dispatch Dashboard.  The SQL we need looks like this:

Select TypeDescription, count(*) as 'WOcount'
From vWorkOrders
Where StatusCode = 'ACTIV'
Group By TypeDescription
Order By TypeDescription

To create and add an advanced report to the Dispatch module, follow these steps:

  1. Create the Advanced Report.

  2. Edit the SQL of the Advanced Report.

  3. Add the Advanced Report to the Dispatch Module setup.

  4. Review your Advanced Report in the Dispatch.

Create the Advanced Report

To create an advanced report, follow these steps:

  1. In the Reporting menu, click Report Create

  2. In the Name field, type in the name of your Advanced Report. 

    We’ll type in ActiveWOsForDashboard for the purposes of the tutorial.

  3. In the Description field, type in an extended description of your Advanced Report  (ex:“This is a count of Active work orders by Type Description for the Dispatch Dashboard.”)

  4. From the Type drop-down menu, select Advanced Report.

  5. From the Sub Type drop-down menu, select Complete.

  6. From the Segment drop-down menu, select the segment you are in. 

    For us, it’s the Facilities Segment.

  7. In the Module drop-down menu, select Work Orders.

  8. In the Category drop-down menu, select General.

  9. In the Sub Category drop-down menu, select General.

  10. Select Include In My Favorites so that you can easily find this report later on.

  11. Click Save.

Edit the SQL of the Advanced Report

  1. In the Report Edit screen, click the SQL tab.

  2. In the lower right-hand corner of the screen, click the Edit SQL button.

    TMS warns you that by manually entering SQL in the query, you won’t be able to use the visual designer for the report.

  3. Click OK to continue.

  4. Enter this SQL into the text box:

    select TypeDescription, count(*) as 'WOcount'
    From vWorkOrders
    where StatusCode = 'ACTIV'
    Group By TypeDescription
    Order By TypeDescription

  5. Click the Preview button to verify that the SQL is correct.

  6. Close the Report Preview window and click the Save button to save your Advanced Report.

Add the Advanced Report to the Dispatch Module setup

Now you can add the Advanced Report to the Dispatch Module.  This part of the tutorial assumes that you have a Dispatch screen created.  If you don’t, review the Dispatch Setup and Dispatch Edit help topics.  Follow the step-by-step instructions to create your new Dispatch screen.

The name of our Dispatch screen is Sample Facilities Dispatch Queue.  (Remember, we were working in the Facilities segment when we created the Advanced Report.)

To add the advanced report to the dispatch module, follow these steps:

  1. Retrieve your Dispatch screen from the Setup menu by clicking Work Orders > Dispatch Setup.

  2. Click on Query.

  3. Enter your search criteria and click Execute Query.

  4. Select your Dispatch queue from the list of search results.

  5. In the Dispatch Edit screen, click the Dashboard Reports tab.

  6. Under Dashboard Reports, click New.

    This opens the list of Advanced Reports that you can choose to add to the Dispatch.

  7. Double-click your report and TMS adds it to the Dashboard Reports tab.

  8. Click Save.

    Your Advanced Report is added to the Dispatch called Sample Facilities Dispatch Queue.

To review your Advanced Report in the Dispatch, follow these steps:

  1. From the Work Orders menu, go to WO Dispatch

    This opens the WO Dispatch screen

  2. From the Dispatch Queues drop-down menu, select Sample Facilities Dispatch Queue

    Your Advanced Report, ActiveWOsFotDashboard, is visible in right pane