Database Dictionary

Pre-made reporting views overview

The HelpMaster SQL Server database ships with many pre-made queries, or views that are designed to be used for custom report development. These can be found under the “Views” folder in SQL Server Management Studio, and all start with the prefix “rpt”.

These views have been created to make report development easy as they join all of the base tables into a convenient reporting view for a specific focus (Jobs, Clients, Assets etc). Use these views with reporting tools like Crystal Reports, SSRS (SQL Server Reporting Services), PowerBI, Excel, Cognos or any other reporting tool.

Use SQL Server Management Studio to view and browse the reporting views. Expand each view to see the data that is returned for that view.

View design considerations

Inheritance pattern

The views are designed around an “inheritance” pattern - that is, a view may reference other views to build up a more complete data-set of information. This allows for ease of use and construction of reporting views and report design, but is generally not considered best practice for very large datasets. Views that reference other views can become bloated with unnessesary database fields, and this can in turn affect performance and network traffic.

Unfiltered views

Each reporting view is also unfiltered, that is, there is no “Where” clause on the view. This means that it will return ALL available data for the intended design of the view. eg. Executing the view rptJob, will return all jobs within the system - open or closed, from all time periods. Completely unfiltered. When using these queries with reporting tools, the recommended best-practice is to either:

1. Create a copy of the reporting view and add a Where clause in the view itself and then use it
2. Specify a Where clause in the reporting tool when referencing the view. eg. Select * From rptJob Where DateLogged > #2020/1/1#
3. Use the reporting view as the basis for creating your own report views and include an appropriate Where clause

Create your own views

Each reporting view within the HelpMaster database is designed to return a specific set of data for a given entity type, and some views - such as the Jobs will have natural extensions to also included linked data eg. Jobs data that includes the linked client, site and asset data. Sometimes though, you may wish to include even more data (linked or otherwise) for a specific report that you are designing. If the pre-made reporting views do not include the data you require, you may wish to consider the following options:

1. Duplicate the view that is closest to what you’re after and then manually update the SQL definition of the view to include the data that you require
2. Create a new view that joins 2 or more views to create the dataset you require

Reporting View Types

Action Views

Action views contain information that is found in the Action Log of a job.

• rptActions

Asset Views

Asset views contain information about Assets within the system.

• rptAsset_Basic
• rptAsset_Custom
• rptAsset_Details
• rptAsset_ServiceHistory

Client Views

Client views contain information about clients within the system.

• rptClient_Basic - A basic view that display client information. Contains the Primary Site only
• rptClient_Site - Extends rptClient_Basic by including additional site details, including the site hierarchy for the client’s primary site
• rptClient_Details - The basic contact and other user-definable data for a client
• rptClient_Extended - Even more data about a client

Job Views

• rptJob
• Job
• rptJob_Actions
• rptJob_Basic
• rptJob_Client
• rptJob_Client_Actions
• rptJob_Client_Asset

See Also

Creating a custom report

Getting help with building reports

Power BI Dashboards