Search

Helpdesk software blog

 
     

Creating custom reports in HelpMaster Pro – part 2

Aug 20 2009

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:

  1. Use a backup database to base your reporting on.  For even better performance, you could restore your database to a local machine.
  2. 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
  3. 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.

Have fun.

<p>&nbsp;</p>
Total: 4 Comment(s)
Scott McRae
Scott McRae  Wow! This is great. You put a lot of work into this. I'm sure many of our users will take advantage of this page.
· reply ·
ComSuper
ComSuper  Hey Rod,

I know you demonstrated this to me the other week and it was fantastic and we are very keen to progress to this position.

Just one question, I presume this doesn't work with Microsoft Office 2003, or does it?

Stu
· reply ·
Rod Weir
Rod Weir  Hi Stu, yes, Excel 2003 can do this also. The connection method is a little different - I think you use the "Data" menu to get to the database? I haven't used Excel 2003, since, well, 2007 came out, so I'm a little rusty on the specifics of it, but it will do this reporting method.

Have a go and let me know.
· reply ·
Rod Weir
Rod Weir  For a discussion about the database changes in more recent versions of HelpMaster, please refer to www.helpmasterpro.com/Community/Blogs/EntryId/77/A-look-at-the-new-reports-and-reporting-structure-in-HelpMaster-v11.aspx
· reply ·

Tag

 
     

Blog Archive

Regression Testing an ITSM tool
9/24/2014 10:34 AM | Rod Weir
ITSMF Australian LeadIT conference - Canberra - recap
8/13/2013 12:00 AM | HelpMaster Administrator
Helpdesk and service desk experiences at CeBIT 2013 - Darling Harbour, Sydney
6/12/2013 12:00 AM | HelpMaster Administrator
2012 Year in review for PRD Software and HelpMaster help desk software
12/31/2012 1:00 AM | HelpMaster Administrator
HelpMaster helpdesk software qualifications
9/4/2012 12:00 AM | HelpMaster Administrator
HelpMaster and VersaSRS
8/7/2012 12:00 AM | HelpMaster Administrator
ITIL-compliant software and blues-compliant guitars
12/8/2011 1:00 AM | HelpMaster Administrator
Cheerful helpdesk
10/21/2011 1:00 AM | HelpMaster Administrator
People, Process and Technology
10/11/2011 1:00 AM | HelpMaster Administrator
The Helpdesk you used to know
9/27/2011 12:00 AM | HelpMaster Administrator