HelpMaster Service Management Software Blog

rss

Practical tips and information about running an efficient service desk. News and information about HelpMaster, PRD Software and the ITSM industry.

One of the design goals for HelpMaster v11 was to provide a more flexible, structured reporting system.

In order to achieve this, we've re-written every report, and every database view that will be used in the reporting process.

 

The database views designed for reporting

The HelpMaster database contains many views that have been made especially for reporting purposes.  These are easily recognized because they are prefixed with "rpt", eg. rptJob, rptActions, rptClient_Basic etc.  Each of these views pull together the raw table-based data and combine it into easy to access, specific views that have a dedicated reporting purpose.  In many cases, the reports will build on each other - that is, one view will contain the columns from another.  When this is the case, the naming convention of the view will give an indication of what the view contains.  eg. rptJob is a view that contain just the minimum columns for a HelpMaster job.  rptJob_Client contains the columns from rptJob as well as rptClient...and so on.

Every report that HelpMaster uses is based on one of the "rpt" views.  These can be seen by expanding the "Views" folder on the SQL Server HelpMaster database.

Crystal Reports reporting views

 

Previous reporting view compared with v11 reporting views

As mentioned, the physical names of the database views that are used for reporting have changed for the v11 release.  Here is a general mapping of what has occured.

The "mega" views of v10 and earlier have been replaced by smaller, simpler views that contain specific fields.  The naming convention of the view will give a good indication of what level of detail the view will contain.  In many cases there is not a direct 1 to 1 mapping of old and new views.  In order to get the same or similar data from the v11 database reporting structure, you may have to use several views to "re-create" the view column definition of v10 and earlier views. 

 

Old Name New name(s)
   
rptCompleteDetails_Client rptClient_Basic
  rptClient_Custom
  rptClient_Extended
   
rptCompleteDetails_Job_Base rptJob_Basic
  rptJob
   
rptCompleteDetails_Job_ClientSite1 rptJob
  rptJob_Client
  rptJob_Client_Site
   
rptCompleteDetails_Job_ClientSite1_ActionLog rptJob_Actions
  rptJob_Client_Actions
  rptJob_Client_Site_Asset_Actions
   
rptCompleteDetails_Site rptSite_Basic
  rptSite_Custom
   
rptCompleteDetails_KnowledgeBase rptKnowledgeBase
   

 

View structure, design and naming convention

Each reporting view has been designed to return a specific set of fields, and the name of the view gives a good indication of what it returns.

eg. 

  • rptJob = Job based fields
  • rptClient = Client based fields
  • rptSite = Site based fields
  • rptAsset = Asset based fields

Some views combine these base views to form bigger, combined views of data.  Again, the naming convention of the view gives a good indication of the columns returned.

eg.

  • rptJob_Client = Everything from rptJob + everything from rptClient
  • rptJob_Client_Site = Everything from rptJob + everything from rptClient + everything from rptSite

etc.

Viewing the design of each view in SQL Server will reveal that these "superview" views are simply combinations of the base views joined together.

 

Custom data fields for each entity type

In the new reporting structure, the base reporting views (rptJob, rptClient, rptSite etc)  and their extentions do not contain the custom data fields that you may have configured within HelpMaster for each of these entity types.  This has been designed this way to ensure optimum database performance against the base entity data.  Custom data fields are contained in the following reporting views.  These views are automatically re-created whenever the custom field definition changes from HelpMaster.

  • rptJob_Custom_Crosstab
  • rptClient_Custom_Crosstab
  • rptSite_Custom_Crosstab
  • rptAsset_Customer_Crosstab

If you require these fields to be displayed in custom reports that you may have created, you may consider the following options.

  1. Create a new view in your database to include these custom fields and then base your report on this view. 


     
  2. Create the SQL definition for such a view and use this as the basis for a Crystal Reports Command definition

    crystal reports command sql

These techniques can be used for all reports.  If a database field does not exist in any of the readily available "rpt", reporting views, you can always build your own database view and base your report on that, or use the raw SQL that would make such a view and use that in the Crystal Reports command definition.

Fixing broken reports

Custom Crystal Reports created against the database of previous versions of HelpMaster may no longer work with the updated database format.  This will definately occur if the report was based on a reporting view from a previous verison.  The v11 database reporting views have been completely re-made and have a new name.  Note however that all of the fields that were available in previous versoins will still be available in the v11 reporting views.

In many cases, fixing broken reports is simply a matter of opening the report in Crystal Reports and re-pointing the datasouce to the new reporting view.

 Crystal Reports set datasource

Once you have re-pointed your custom report to the new reporting view, save the report and try running it again.

If your custom report's datasource was not based on one of the "rpt" views, but instead created from the base table objects, you may need to write, or find a view that returns the data you need.

Need help?

If you need help with your custom reports, or would like to have a custom report made, PRD Software offer a report writing service.  See http://www.helpmasterpro.com/Services/Custom-Reports.aspx for details.


blog comments powered by Disqus