Configuring Microsoft SQL Server Reporting Services (SSRS)

FileHold provides a number of reports out-of-the-box that are designed to help maintain and administer FileHold. As customers add their own documents and metadata they often have specialized needs for reporting. Some of these can be satisfied using the built-in saved search feature of FileHold, but some are more complex and require a highly configurable reporting capability. SSRS is a tool that can be optionally installed with SQL Server and makes an excellent platform for custom reports. FileHold enables this capability by allowing custom built SQL reports to be integrated into the FileHold user interface. The support is further enhanced by integrating access to the reports into the normal user and group security of FileHold.

A skilled IT systems administrator will be needed to setup and manage SSRS. Similarly, a skilled report creator will be needed to create the reports. The FileHold professional services team is available to provide these services as needed. FileCare does not include support for working with SSRS. FileHold does not publish complete documentation with details of the database structure, but sample reports and knowledge base articles do provide typically needed information. This document provides a basic example of how to configure SSRS and FileHold. It is provided for informational purposes only. You will need to be familiar with SQL Server, SSRS, IIS, and the principles of Windows Authentication to setup reporting. The implementation method described is only one of many ways to work with SSRS.

Assumptions

• FileHold is installed and it is operating correctly.
• Microsoft SSRS is installed. This can be done at the same time SQL Server is installed or added at a later time. SSRS has limited function on SQL Express versions.
• Use Windows Authentication to integrate SQL Reporting services with the FileHold Server and databases. IIS and SQL Standard or Enterprise are on the same Windows Server.
• The two sample reports described in this article assumes that FileHold was installed with the default database prefix of "ch_". If the FH databases were renamed other than with the usual "ch_" prefix when installed, such as "FH_librarymanager", you will need to modify the sample report definition files in order to work.

Information you will need

Before you begin, make sure you have the following information available as it is necessary to complete the instructions.

• FileHold service account name and password.
• Download the sample reports for FileHold 14-16 with SQL 2005-2019.
• This package contains two report definition files:
  • LibraryListReport.rdl
  • SystemActivityReport.rdl
• Put the files on the FileHold server where the SQL Reporting Services Manager website is available.

Installing the reporting services

This document assumes that Microsoft SQL Server Reporting Services has been installed and basic user access has been configured for FileHold. 

Configuring the report server for FileHold

You can create one or many folders to organize the reports you will use in FileHold, but there must be exactly one folder for FileHold at the root level. The name you use for the folder will appear in the FileHold library tree. Each folder for FileHold reports can contain zero or many reports and zero or more folders.

Any sub-folders you create will appear in the FileHold user interface without the hierarchy. When you open a folder, the folder list will only display reports contained in the folder; sub-folders will not be displayed in the folder view. The FileHold library administration will allow you to assign user and group permissions to each folder.

1. Create a new folder called "FileHold Reports" or similar.

Image

2. On the FileHold server, open the library manager web configuration file: C:\Program Files\FileHold Systems\Application Server\LibraryManager\web.config
3. Make a backup copy of the file before making any changes. In the <appSettings> section, fill out the manadatory settings listed in the table below:

4. If the SQL Reporting Service is installed on a different machine than the FileHold application server, find the key with the name "FileHold_LibraryManager_ReportService2005Service_ReportingService2005". Change the value to reflect the actual location of the server.
5. Save the web.config file.

Installing the included reports

There are two pre-built reports that can be downloaded.

• The library list report will allow you to see all documents in any part of all of the library structure in a single flat view.
• The system activity report is similar to the report that is available on the system administration page.

1. Upload both report files (.RDL) to the root folder or any sub-folder off the FileHold root folder.

Image

2. Click New > Data Source.
3. Add the data source for the library manager database. Example configuration:

4. Click Test connection. The message "Connected successfully" appears.
5. Click Create.
6. Click New > Data Source.
7. Add the data source for the user role manager database. Example configuration:

7. Click Test connection. The message "Connected successfully" appears.
8. Click Create.

Image

9. Connect the reports with the data sources. Select Manage from the context menu on a report.
10. On the Data sources tab, select the data source and click Save. Map the Library Manager data source to the LibraryListReport and the User Role Manager data source to the SystemActivityReport.

Image

11. Test the reports.

Image

12. Once the reports are set up correctly, you can assign permissions to the reports in FileHold.