A Generic Report is a special kind of preset report. It allows 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.
Start by writing the Entity SQL Query that will retrieve the data. This can be entered using Reports -> Queries -> Stored Queries. Once this query is complete, 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 using the Patriot 6.1 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 format is also able to be completely customised by using a different RDLC file. To create a new report output format, simply add a new report name of type Generic Report. This can be done from Reports->Settings->Report Names. Select an appropriate name for this output, and then import the desired RDLC output file. See the document on creating custom reports for more information on creating RDLC files. The default Generic Report RDLC can be used as a starting point if required. The report can then be run in exactly the same way as the standard Generic Report, but will use the new output format.
You can extend a Generic Report to run 'By User', ie a seperate report for each user selected, filtering on just the clients assigned to each user. Use the GenericReportByUser Report type. This allows you to select a user or user group. The query used must have a link to the usertoclient table or muser table, and a where clause with a hardcoded token of <uid>, eg
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'2012-10-01 00:00:00.000'
Order by s.recdatetime
If you use a GenericReportByUser report type, you also get the option to select a date range. You can then include a date range in the query by using the <ds> (for Date Start) and <de> (for Date End), eg,
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