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
Start by writing the Entity SQL Query that will retrieve the data. This can be entered using Reports > Queries > Stored Queries.
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
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:
- Open Reports > Settings > Report Names.
- Add a new Report Name.
- Set the report type to Generic Report.
- Give the report an appropriate name.
- 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 means that the report will be filtered to show data for clients the user is assigned to. If you run the report for a user grouping instead of a user, a separate report will generate for each user in the group.
Use the GenericReportByUser Report type. This allows you to select a user or a grouping. The query used must have a link to the usertoclient table or muser table, and a WHERE clause with a hardcoded token of:
<uid>
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 > 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