Build a help-desk reporting dashboard using Excel
Build your own helpdesk reporting dashboards using Microsoft Excel
Microsoft Excel is an excellent report tool and it has a lot of power to deliver charts, graphs and other visualizations of your data.
When it comes to HelpMaster reporting, many people tend to run a built-in HelpMaster report (which uses the Crystal Reports reporting engine), then export the report to a CSV file (comma separated value), or an Excel file and then use Excel to manipulate the data and create graphs and other reports. Sound familiar?
That's the hard way.
Here's the easy(er) way. It's also a LOT more powerful and flexible.
Pivot tables and pivot charts are you friend here.
1. Open Microsoft Excel. The first step is to connect directly to your HelpMaster SQL Server database.
2. Enter your database connection credentials.
3. Select your HelpMaster database from the top drop-down box. Then select a reporting query (view). Any view that is prefixed by "rpt" has been designed to be consumed by a report.
Note! The screen shot below was taken from a HelpMaster v10 database. If you are using a newer version please be aware that the reporting views have changed. Refer to the following article for changes in names. http://www.helpmasterpro.com/Community/Blogs/EntryId/77/A-look-at-the-new-reports-and-reporting-structure-in-HelpMaster-v11.aspx
4. Give the data connection a name (optional) and click "Finish"
5. Now the important bit. Make sure you select "PivotChart and PivotTable Report" and click OK
6. A pivot table has 4 components to it. Scroll down the list of available fields until you find "JobNumber". Drag it into the "Values" section. This will be the field that will be the Pivot Table aggregation.
7. The default aggregation type is set to "Sum". You'll need to change this to "Count"
8. Now the fun part. Drag and drop different fields from the reporting view onto either the Axis Fields, or the Legend Field. You'll notice the pivot chart changing as you do this.
9. Experiment with different combinations. You can stack multiple fields on either axis for different statistical views.
10. Right-click the chart to change the chart type.
Filter your data
Note! This is important!!!
Please be aware that when you connect directly to your live HelpMaster database and use any of the default rpt views, you are pulling ALL OF YOUR DATA across your network. The views do not have a filter on them - they return everything! This means that your SQL Server database takes a bit hit, your network bandwidth takes a big hit and your local computer takes a big hit. Furthermore, everyone on your network and those connected to HelpMaster at the time of you doing this will experience a performance hit. (Try looking at your task manager for what this is doing to your computer).
Remember that the HelpMaster reporting views are unfiltered - this means that every job you've ever logged will be queried and returned back to the client machine.
To avoid this situation, consider doing one or more of the following:
- Write your own query using SQL, and ensure that the query is filtered using a "WHERE" clause to only the data you want. To do this, update the connection properties of the Data Connection as shown below. Contact PRD Software if you require assistance with querying the HelpMaster database.
- Create a filtered view based on an existing rpt view and point Excel at this instead. You could create specific views especially for reporting purposes that are filtered by date, time, job number or other factors
- Create a separate, database warehouse style database. This is essentially a de-normalized database that has an optimized database schema for reporting.
- Use a backup database to base your reporting on. For even better performance, you could restore your database to a local machine.
You need to adjust date-based fields from UTC to local time
Remember that all dates stored in the HelpMaster database are stored in UTC time. This means if you will need to transform every date to your local time to get an accurate. For further information about this, please refer to this discussion board post and knowledge base article:
Automatically update your dashboard data
If you wish to automatically update your chart/data at regular time intervals, you can adjust the data connection properties to do so. This is ideal if you are displaying your data on a monitor in a common area.
Download a sample
Download a sample Excel Report. In order to use it against your data, you will need to update the database connection string as shown above.
Download a sample report
Extend your Excel Dashboard reports
Once you start using dynamic reporting of this nature, you can get a lot of insight into your data. Furthermore, you can save the Excel spreadsheet as a regular .xls file and open it at another time. You can create copies of the file, or even add more tabs, more PivotTables and/or charts to create an even better helpdesk dashboard effect.