Helpdesk Customization: Building a Better Admin Report

The Helpdesk Admin Report can be a very useful tool for obtaining an at-a-glance overview of the status of your Helpdesk workers and queues. Unfortunately, it often becomes cluttered as your system matures, turning into a giant wall of text that is more difficult to read than it should be. This is largely due to the fact that all of the workers and queues are displayed, regardless of whether or not they are in use. The objective of this customization is to boil down the Admin Report so that it shows only pertinent information to your environment.

The Admin Report: Only 6 of these workers are active

Click to view.

This guide will show you how to:

• Remove inactive workers and queues from the admin report
• Remove and re-order the columns displayed in the admin report

All modifications will be made utilizing best practices for Helpdesk customization.

The Customization Files

The files that will be created for this customization are:

• .\AeXHD\ custom.config
• .\AeXHD\custom\ CustomStrings.xml
• .\AeXHD\custom\ CustomAdminReport.ascx
• .\AeXHD\custom\ CustomReportQueries.xml (optional)

Modify the Query - CustomStrings.xml

Using your favorite XML editor, create a new file in your ".\AeXHD\custom" folder called "CustomStrings.xml" We will be using this file to overwrite the SQL query that is used to build the list of workers displayed in the Admin Report. You can see the original query in the string "sidSqlAdminWorkerReporEx," located in the Formats.xml file. By overwriting this string, we can control the logic used to build that list. CustomStrings.xml will contain the following code (Notice that we've named this new string "csidSqlAdminWorkerReportEx" ).

<?xml version="1.0" encoding="utf-8"?>
<strings>
  <string id="csidSqlAdminWorkerReportEx">
  <![CDATA[
  SELECT DISTINCT w.worker_id as 'id', {0} w.worker_name as 'name'
  FROM worker_view w
  WHERE (
  w.worker_status IN ('a', 'v') 
  OR w.worker_id IN
  (SELECT workitem_assigned_to_worker_id
  FROM workitem_current_view
  WHERE workitem_status_lookup_id NOT IN ('400', '600'))
  OR (w.worker_id IN
  (SELECT workitem_owned_by_worker_id
  FROM workitem_current_view
  WHERE workitem_status_lookup_id NOT IN ('400', '600'))
  AND w.worker_id NOT IN
  (SELECT workitem_owned_by_worker_id
  FROM workitem_current_view
  WHERE assigned_to_worker_status IN ('a', 'v'))
  )
  )
  GROUP BY worker_name, worker_id, worker_is_virtual
  ORDER BY w.worker_name, w.worker_id
  ]]>
  </string>
</strings>

The SQL query contained here is slightly more complex than the out-of-the-box query. While we want to hide the inactive workers, we don't want to put ourselves in a situation where active incidents are orphaned from the report. To combat this, the logic included in the query is:

• Display the worker if it is Active or Virtual
  --OR--
• Display the worker if it has incidents assigned to it that have any status other than "Closed" or "Resolved," regardless of whether or not it is inactive
  --OR--
• Display the worker if it is the owner of any incidents that have a status other than "Closed" or "Resolved" AND are not assigned to an active worker, regardless of whether or not it is inactive

This query allows you to set workers that are no longer being used to inactive, and will remove them from the admin report as long as there are no action-requiring incidents still assigned to or owned by them. Now that we have the proper query in place, we need to reference it in the "CustomAdminReport.ascx" page.

Reference the New String - CustomAdminReport.ascx

The page that actually builds and runs the Admin Report is called "AdminReport.ascx" and is located in the ".\AeXHD\Templates" directory. Copy this file into the ".\AeXHD\custom" folder, and rename the copied file to "CustomAdminReport.ascx."

Open the file in your editor of choice, and modify the following line:

Dim adminWorkerReportQuery As String = TryString("csidSqlAdminWorkerReportEx", workerSql.ToString)

Notice that all we're doing is adding a leading "c" to the string name, indicating that it will use the string we've defined in CustomStrings.xml instead of the original. Using the search function on the string "sidSqlAdminWorkerReportEx" will help you to quickly identify where you need to make the modification, as there is only a single reference to that string in the file.

Custom.config

If you're not already using a custom.config file for other customizations, go ahead and create one using an XML editor in the root of you Helpdesk directory. This file is used as a central reference point to the files we've created so far. The contents of this file should contain the following code:

<?xml version="1.0" encoding="utf-8"?> 
<custom.configuration>
<files path="~/custom/">
  <file id="AdminReport" file="CustomAdminReport.ascx"/>
  <file id="ReportQueries" file="CustomReportQueries.xml"/>
</files>
  <resourceLibs>
  <resourceLib file="CustomStrings.xml" path="~/custom/" />
  </resourceLibs>
</custom.configuration>

Save your custom.config file, restart IIS, and load up the Admin Report. You should no longer see any inactive workers, unless they still have non-closed tickets assigned to them ( as is the case with the "Operations" queue in the screenshot below). You'll also notice that the total the "Owned" row doesn't add up to the total number of incidents. Because I'm using test data, the owner of most of my incidents is the Helpdesk service account. The Helpdesk worker is inactive and all of the incidents that it owns are assigned to active workers, so it is not displayed here.

Much Better, but maybe still a little wide...

Click to view.

  AND w.worker_id NOT IN
  (SELECT workitem_owned_by_worker_id
  FROM workitem_current_view
  WHERE assigned_to_worker_status IN ('a', 'v'))
  )

Remove and Reorder Columns - CustomReportQueries.xml

While we've managed to trim things down vertically, it's still a little wide. The report still contains some data that I don't feel is necessary in a quick overview. I'd also like to move the "Closed" column closer to the "Open" column, as it will be easier for me to identify workers with a lot of outstanding work orders. To accomplish this, copy the "ReportQueries.xml" file from ".\AeXHD\Templates" to ".\AeXHD\custom" and name the copied file "CustomReportQueries.xml." Again using your XML editor, open the file and make your modifications. You'll notice that each of the columns is identified by a query id, and you can remove them from the list by deleting everything between the <query></query> tags. I've chosen to delete the "Planned" and "Requested" sections to remove those columns, and I moved the "Closed" column next to the "Open" by cutting and pasting that section of the file as seen below:

<?xml version="1.0" encoding="utf-8" ?>
<reportQueries>
  <query id="open">
  <display>sidColOpen</display>
  <label>sidLblOpen</label>
  <titleAllAssigned>sidAllOpenTitle</titleAllAssigned>
  <titleMyAssigned>sidMyOpenTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedOpenTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerOpenTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedOpenTitle</titleWorkerOwned>
  <sql>
  workitem_status_lookup_id = 300
  </sql>
  </query>
  <query id="closed">
  <display>sidColClosed</display>
  <label>sidLblClosed</label>
  <titleAllAssigned>sidAllClosedTitle</titleAllAssigned>
  <titleMyAssigned>sidMyClosedTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedClosedTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerClosedTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedClosedTitle</titleWorkerOwned>
  <sql>
  workitem_status_lookup_id = 600
  </sql>
  </query>
  <query id="late">
  <display>sidColLate</display>
  <label>sidLblLate</label>
  <titleAllAssigned>sidAllLateTitle</titleAllAssigned>
  <titleMyAssigned>sidMyLateTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedLateTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerLateTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedLateTitle</titleWorkerOwned>
  <sql>
  workitem_is_scheduled='1' and (workitem_status_lookup_id = 100 or workitem_status_lookup_id = 200) and workitem_start_on < getdate()
  </sql>
  </query>
  <query id="overdue">
  <display>sidColOverdue</display>
  <label>sidLblOverdue</label>
  <titleAllAssigned>sidAllOverdueTitle</titleAllAssigned>
  <titleMyAssigned>sidMyOverdueTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedOverdueTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerOverdueTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedOverdueTitle</titleWorkerOwned>
  <sql>
  workitem_is_scheduled='1' and workitem_status_lookup_id != 600 and workitem_due_on < getdate()
  </sql>
  </query>
  <query id="resolved">
  <display>sidColResolved</display>
  <label>sidLblResolved</label>
  <titleAllAssigned>sidAllResolvedTitle</titleAllAssigned>
  <titleMyAssigned>sidMyResolvedTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedResolvedTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerResolvedTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedResolvedTitle</titleWorkerOwned>
  <sql>
  workitem_status_lookup_id = 400
  </sql>
  </query>
  <query id="held">
  <display>sidColHeld</display>
  <label>sidLblHeld</label>
  <titleAllAssigned>sidAllHeldTitle</titleAllAssigned>
  <titleMyAssigned>sidMyHeldTitle</titleMyAssigned>
  <titleMyOwned>sidMyOwnedHeldTitle</titleMyOwned>
  <titleWorkerAssigned>sidWorkerHeldTitle</titleWorkerAssigned>
  <titleWorkerOwned>sidWorkerOwnedHeldTitle</titleWorkerOwned>
  <sql>
  workitem_status_lookup_id = 500
  </sql>
  </query>
</reportQueries>

Go back to the custom.config file and add an entry for the CustomReportQueries.xml file:

<?xml version="1.0" encoding="utf-8"?> 
<custom.configuration>
<files path="~/custom/">
  <file id="AdminReport" file="CustomAdminReport.ascx"/>
  <file id="ReportQueries" file="CustomReportQueries.xml"/>
</files>
  <resourceLibs>
  <resourceLib file="CustomStrings.xml" path="~/custom/" />
  </resourceLibs>
</custom.configuration>

Once again, restart IIS and reload the admin report. Depending on what you decided to do with the columns, your finished product may look something like this:

A much more concise Admin Report

Click to view.

Conclusion

That's it for the Admin Report customization guide. Your feedback would be greatly appreciated!