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 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.
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.
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.
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.
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.
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.
If you require these fields to be displayed in custom reports that you may have created, you may consider the following options.
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.
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.
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.
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.