Knowledge Base Article: Creating, updating and fixing custom reports after a version upgrade.

Creating, updating and fixing custom reports after a version upgrade.

Reports:4

database report reports views rptCompleteDetails_Job crystal reports sql server data rpt custom reports

HelpMaster > Reports

Reports

4/11/2012 8:04:49 AM

7/6/2013 7:16:26 PM


HelpMaster v11 and above has a completely new reporting foundation.  The underlying database has been changed and every single report has been re-written to give a better reporting experience.  Learn about some of the new changes in this latest version of HelpMaster.

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.

[image]

 

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

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

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. 

    [image]
     

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

    [image]

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.

 [image]

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.