Creating custom reports in HelpMaster Pro – part 2
Help desk reporting 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 2007. 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.
Note! (This is important!!!)
Please be aware that when you connect directly to you live HelpMaster database and use the default rpt views, you are pulling ALL OF YOUR HELPDESK DATA across your network. 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 displayed.
As a workaround for this situation, please consider doing one or more of the following:
- Use a backup database to base your reporting on. For even better performance, you could restore your database to a local machine.
- 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.
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 a helpdesk dashboard effect.