Configuring reports for the simple report viewer

The simple report viewer displays reports formatted as an HTML page by an external tool. Reports generated by running T-SQL code on the SQL Server hosting the FileHold database is the external tool supported at this time. The configuration provides for SQL code that inserts the HTML page into a SQL variable called @html.

For example, the following SQL would generate a valid report for the simple report viewer.

-- The @html variable is predefined by the system
SELECT @html = '<html><body><p>Hello world!</p></body></html>'

FileHold version 17.1 or higher is required to use the simple report viewer.

FileHold ships with four example reports that you can learn from, modify, etc. They will be visible to administrators or any user who is assigned permission from the report node of the library tree. They may be useful as they stand, but they are intended only as examples of what can be done and not an officially supported part of the product for production use.

The easiest method to develop a report for the simple report viewer is to prepare it outside of FileHold by testing it with a normal browser. For testing, the example above could be modified by adding one more select, running in the SQL Management tool or similar, copy and pasting the resulting column to a text editor and saving it as test.html, then opening the file with a browser. Other methods for creating reports are possible, this is just one example.

SELECT @html = '<html><body><p>Hello world!</p></body></html>'
SELECT @html

Once the SQL for the report is complete, it can be added and maintained in the system using the following stored procedures in the library manager database:

ch_AddSimpleReport - Add a report for the first time.
ch_UpdateSimpleReport - Update an existing simple report.
ch_RemoveSimpleReport - Delete an exsiting simple report.
ch_GetSimpleReports - Return all defined simple reports.

ch_AddSimpleReport

This procedure takes six parameters:

Report name - The unique name that will appear in the viewer list as the report name. Up to 200 characters.
Display name - The report description in the viewer list. Up to 500 characters.
Type - This must always be 0 (zero).
Code - The SQL code.
User ID - The FileHold user that will be shown to have created the report. This should be a valid ID from the AdamObjects table or NULL to set the ID to the system process.
Report ID - The internal ID generated for the report will be returned here as an nvarchar(36).

An example call might look like the following:

declare @ReportId nvarchar(36);
declare @UserId int = (select Objectid from AdamObjects where ObjectName='sysadm sysadm')
exec ch_AddSimpleReport 
  'My first report'
  ,'My first report description'
  ,0
  ,'SELECT @html = ''<html><body><p>Hello world!</p></body></html>'''
  ,@UserId
  ,@ReportId

Executing the report should give a result similar to this.

ch_UpdateSimpleReport

This procedure has similar parameters to the previous, but the report ID is now used to identify the report to update.

Report ID - The internal ID returned by ch_AddSimpleReport.
Report name - The unique name that will appear in the viewer list as the report name. Up to 200 characters.
Display name - The report description in the viewer list. Up to 500 characters.
Type - This must always be 0 (zero).
Code - The SQL code.
User ID - The FileHold user that will be shown to have created the report. This should be a valid ID from the AdamObjects table or NULL to set the ID to the system process.

ch_ExecuteSimpleReport

Provides an intermediate method for testing a report as this will execute the code associated with the given Report ID and provide the resulting HTML in a raw form.

ch_RemoveSimpleReport

Delete a report from the system. This procedure only takes the Report ID as a parameter. It has no side effects if the ID does not exist.

ch_GetSimpleReports

This procedure returns a set of all simple reports when user ID is NULL.

Configuring content security policy (CSP) for the simple report viewer

Depending on what you decide to put in your HTML content, you may want to use tools providing Javascript, special fonts, CSS, etc. that are outside of your FileHold system. The key SimpleReports.TrustedSource has been provided to allow you to list the valid locations for outside content. By default it includes the Google Charts tools used in some of the example reports.