About Custom Reports
Helpdesk uses the report generation process that the Notification Server provides. Reports can be created by selecting fields or even using SQL queries. Information on how to create reports can be found here:
Notification Server 6.0 Report Builder
Using the Advanced Report Builder to Create Custom Reports
PLEASE NOTE: Symantec Technical Support is only able to offer basic information on how to create custom reports, such as how the process works. Symantec Technical Support is unable to assist a customer in creating, modifying or troubleshooting custom reports, such as creating a custom report based on the customer's specifications. This is considered a customization request. Customizations are provided by Consulting Services, which can be reached here:
Symantec Consulting Services
Where to Find Helpdesk Reports
There are many out of box reports for Helpdesk. These are found by clicking on the Report tab > Incident Management > Helpdesk, or if the Altiris Console 6.5 is used, Reports menu > By Solution > Helpdesk > Helpdesk.
It is recommended to review the out of box reports for one similar to what is needed; often, there is one. Or if there is one that is close, this can be cloned (right click > Clone) so that it can be edited and modified.
How to Create a Helpdesk Custom Report
In some cases, a completely new report is needed. The following describes how to create a custom report:
- In an Altiris Console, go to Helpdesk reports, to the folder it is to be created in, and then right click on the folder name > New > Report.
- Select the report type: Simple, Summary, Advanced Report Builder or SQL.
- Fill in any other areas.
- Click on the Finish button.
This report or cloned out of box reports can be later edited by clicking on their "Edit this report" button or link.
An Example Custom Report
Included with this article is an example custom report "CUSTOM REPORT Count of Incidents by Date Range, Status and Worker.xml". This can be imported into Helpdesk by doing the following:
- Download this article's attachment.
- In an Altiris Console, go to Helpdesk reports, to the folder it is to be import to, and then right click on the folder name > Import.
- Specify where the file is to be imported from.
How the Example Custom Report Works
The "CUSTOM REPORT Count of Incidents by Date Range, Status and Worker" report enables users to list incidents by date range, status type and by worker name. This is a SQL report and works differently than the out of box reports do. Editing the report and viewing its sections will display how it works.
Under the Queries section, click on the Edit button to edit the single SQL query. Information on how the SQL query works is found below:
- The query used is purely SQL. Excluding the variables from the parameters, this can be copied in to Microsoft SQL Server Management Studio and ran directly.
- In the query, if the Helpdesk database name is not the default name of "Altiris_Incidents", edit the query and change the first line "USE Altiris_Incidents" to be the different name of the Helpdesk database.
- In the query, if the columns used are needing to be changed, change what is being referenced by the SELECT statement near the top. For example, if Helpdesk comments were not wanted, remove the "workitem.comment" part of the SELECT statement.
- In the query, the results list only the last iteration of an incident as a found match using the "is_last" field value of each incident in the workitem table (where incidents and their changes are recorded to; a commonly referenced table for reports).
- In the query, SQL JOIN commands are used to cross reference related tables so that standard names for field values can be used instead of their ID references from just the workitem table.
- In the query, the worker name is referenced against both the worker and contact tables. A worker record has no first and last name, and so their referenced name will be usually first initial/last name, such as JSmith for John Smith. Out of box Helpdesk reports only reference this value. However, the contact table does include a first and last name for the worker. As this is referenced by this query, this enables the user to specify either the worker name (JSmith) or the first and last name.
- In the query, hard coded start and end times are specified. These can be changed in the query, but are not prompted for during the report run process.
- Global parameters are specified by using them as "%Parameter Name%", with exact spelling and case. These work as variables, substituting the value of the global parameter as defined by the global parameter itself or, if the user was prompted, by their value they specified.
In the case of this custom report, the global parameters do two things: enables the user to enter specific report criteria before the report runs, and uses what the user specified as variables in the report run process. Click on the Edit button to edit any of the global parameters. Information on how the global parameters work is found below:
- When creating global parameters, these are displayed to the user (when set to Prompt) based on creation order. There is no method to change the display order afterwards other than deleting global parameters and starting over again, this time in the correct order.
- Global parameter default values can be left blank, set to a specific value or to a wildcard "%".
- The Start Date global parameter is derived by using the current date as its default value. This can be changed to something else, such as the first day of the year, month, etc.
- The End Date global parameter is set up similarly to the Start Date global parameter.
- In most other out of box reports, a Days field global parameter is used. This counts back from the current date to the number of days to involve in the report's calculations. By adding a Start and End Date, however, this enables greater flexibility for the user.
- There is no Start and End Times specified. These are derived automatically by the SQL query. The Start Time is therefore midnight (00:00:00) of the Start Date, and the End TIme is therefore 11:59 PM of the End Date.
- The Status global parameter lists the status types. The first one specified is the default one used. This lists the default status types. If these are different, edit the global parameter and change the values accordingly.
- The Worker global parameter uses a wildcard to list all workers by default. Enter the worker name or contact first and last name to specify a specific worker.
When the report is ran, it will first prompt the user with the global parameters, as seen in Figure 1, below:
Figure 1: Custom report using global parameters that prompt the user for data