Create flexible custom reports using Crystal Reports to suit your business based on the ServiceDesk Plus data.

ServiceDesk Plus 5 (Build 5003) uses MySQL 4.1.12 in the backend, to connect to this database from Crystal Reports you need a MySQL ODBC Driver. Download the latest version MySQL Connector / ODBC 3.51 from the URL

Pick a mirror to download. Don’t bother to fill up the huge form, scroll down; you will see the download URLs.

Note: The MySQL Driver will be listed only if you have installed the driver as mentioned earlier.

Clicking on Finish invokes the Add Data Source Name configuration.

In the Login Tab

Data Source Name: servicedeskplus

You can provide any unique name to identify ServiceDesk Plus database. Say if you want to identify based on region you can name it as SDUSA or SDEurope

Description: servicedeskplus database

This field in not Mandatory, if you are not the typical organized person. You can skip this

Server: localhost

As the ServiceDeskPlus database is running on the same machine.

User: root
Password: There is no password set to access the ServiceDesk Plus database

Database: Do not specify the database name for now. We’ll be doing this in the next step.

Accessing ServiceDesk Plus databases with Crystal Reports

Before we get further into the topic. I am assuming that you have a basic understanding of Crystal Reports. If you are looking for information on getting started refer: www.crystalreports.com

You can access ServiceDesk Plus database from Crystal Reports 11. In the Getting Started Page, choose the Standard Report Wizard under New Reports section.

Click on Next to proceed to the login screen.
Specify User ID and Password. To connect to the ServiceDesk Plus database, specify the user ID as root without any password.

Once the connection is established you will see all the ServiceDeskPlus tables listed. You need to choose the tables you will be based your reporting needs. You can choose to generate reports for Requests and assets or you can get specific reports based on requests or assets.

Requests Table Relations

Tables related to Assets

After selecting the required tables and fields based on which you have to generate reports. Select the fields that need to shown in the reports and you can link the tables based on names or key.

Crystal Reports will automatically try to link fields based on field names and data types. You can to delete these links and create link data as shown in the Table Relations diagram. Proceed through the wizard to customize your report further with Filters or Group By field options.

Table Relations for Requests

To get the Create time for a request from the workorder table

NumberVar TimeChange := -1;
NumberVar SecPerDay := 86400;
NumberVar SecPerHr := 3600;
TimeValue (Remainder((({workorder.CREATEDTIME}/1000)-(TimeChange * SecPerHr)),SecPerDay)/SecPerDay) ;

To get the Create date for a request from the workorder table

NumberVar TimeChange := -1;
NumberVar SecPerDay := 86400;
NumberVar SecPerHr := 3600;
Truncate((({workorder.CREATEDTIME}/1000)-(TimeChange * SecPerHr))/SecPerDay) + Date(1970,01,01);

As you might notice the Variable Time Change is the offset to GMT.

More Details on Date functions in Crystal Reports

Here is a sample report (.rpt file)

Download the a sample report file(.rpt)

Finally you can choose a template and create your reports. You can use the Crystal Reports Field Explorer to add more fields and enrich your
report. The Design view helps you to customize and format the reporting fields. Click Finish to create your report.

Here is a sample report based on the Request Status and technicians associated with it

This evolving article is not complete without your contribution and feedback. Please share your sample reports, tips, and tricks. Shoot them to support@servicedeskplus.com or directly to alexdpaul@adventnet.com.