Skip to main content
Version: 6.12

Generic Reports

A Generic Report is a report that uses an SQL query to form the basis for the data in the report. This allows any data from any related tables in the database to be reported on. It also allows the report output to be configured as required. The report can be printed or exported to Word, Excel, or PDF formats.

Writing a Stored Query​

Stored queries from Report menu
Generic Reports use Stored Queries, available from the Reports menu.

Start by writing the Entity SQL Query that will retrieve the data. This can be entered using Reports > Queries > Stored Queries.

Stored query example
Example of a Stored Query

When you have finished writing a query, save it under a descriptive name. Queries can be quickly and easily tested by using the Advanced Search window.

For example, to select all clients who are using the SIA1 template, the following query might be used:

SELECT mem.Client_No, mem.Name
FROM PatriotEntities.Memalarm AS mem
WHERE mem.Alt_Alarm_No = 'SIA1000101'

Running the Report​

Generic Reports from Report menu
The Generic Report and Generic Report by User can be accessed from the Reports menu.

The report can now be run from the Reports menu. Simply select the Generic Report option, and then the appropriate query on the next page. This report can be previewed or saved directly to disk. If the email module is registered, then the results can be immediately emailed. The report can also be set up to run automatically or saved for future manual runs, in the same manner as other reports. Consult the Reporting documentation for more details on this process.

Customising the Output​

By default, Patriot includes an output format which simply lists each column, along with a header containing the column name. The column names can be modified by using the Entity SQL 'AS' keyword. For example, the previous query might be modified to:

SELECT mem.Client_No AS [Client Number], mem.Name AS [Site Name]
FROM PatriotEntities.Memalarm AS mem
WHERE mem.Alt_Alarm_No = 'SIA1000101'

The report layout is also able to be completely customised by using a different Report Definition (.RDLC) file. To create a new report layout:

  1. Open Reports > Settings > Report Names.
  2. Add a new Report Name.
  3. Set the report type to Generic Report.
  4. Give the report an appropriate name.
  5. Import the desired RDLC layout file.

See the document on creating custom reports for more information on creating RDLC files. The default Generic Report RDLC file can be used as a starting point. The report can then be run in exactly the same way as the standard Generic Report, but will use the new output format.

By User Generic Reports​

You can extend a Generic Report to run 'By User'. This allows using a <uid> token in the query, to filter the results for a particular user.

Use the GenericReportByUser Report type. This allows you to select a user or a grouping. If you run the report for a user grouping instead of a user, the report will be run once for each user in the group, generating a separate report for each.

caution

No automatic filtering or access checks are applied. You must ensure that the query has appropriate WHERE clauses to filter the data, and doesn't include information that isn't intended for the specified user.

For example, filtering the signals to only include data for sites related to the user:

SELECT s.recdatetime, s.description
FROM PatriotEntities.Signal AS s
JOIN PatriotEntities.Memalarm AS c ON s.client_no = c.client_no
JOIN PatriotEntities.UserToClient AS u ON c.client_no = u.clientno
WHERE u.userid = <uid>
AND s.recdatetime > DATETIME'2016-10-01 00:00:00.000'
ORDER BY s.recdatetime

Dynamic Date Range​

You can also filter the report to only show a particular date range. You need to include a date range in the query by using the <ds> (for Date Start) and <de> (for Date End), for example:

SELECT s.recdatetime, s.description
FROM PatriotEntities.Signal AS s
JOIN PatriotEntities.Memalarm AS c ON s.client_no = c.client_no
JOIN PatriotEntities.UserToClient AS u ON c.client_no = u.clientno
WHERE u.userid = <uid>
AND s.recdatetime BETWEEN <ds> AND <de>
ORDER BY s.recdatetime

Client Range​

You can filter on a particular client range when using a Generic Report. You need to include the client numbers in the query by using the tokens <cns> (client number start) and <cne> (client number end).

For example:

SELECT mem.Client_No AS [Client Number], mem.Name AS [Site Name]
FROM PatriotEntities.Memalarm AS mem
WHERE mem.Client_No BETWEEN <cns> AND <cne>
ORDER BY mem.Client_No