1.877.833.1202

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

  • These instructions assume you have installed FileHold and it is operating correctly.
  • They assume SSRS has been 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.
  • You will be using Windows Authentication to integrate SQL Reporting services with the FileHold Server and databases. This document assumes that 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. This was created during your preparation for installing FileHold.
  • You will need to download the correct package for the version of FileHold you are running from the FileHold download server. This file will contain the two reports that come with FileHold and you can download the correct sample report file here for FileHold 14 through 16 with SQL 2005 through 2019: http://download-filehold.com/FileHold09-SQL2008-Report.zip
  • Once downloaded, please unzip this folder and place it somewhere accessible to the FileHold server where the SQL Reporting Services Manager web site is available. This package contains two report definition files:
    • LibraryListReport.rdl
    • SystemActivityReport.rdl

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. This name will be used later in the configuration. 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.

Note that 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.

Microsoft SQL Reporting Services 2008R2 configuration for FileHold

Microsoft SQL Reporting Services 2008R2 configuration for FileHold

You can now connect the FileHold user interface to the reporting system. Find the FileHold library manager web configuration file. This can normally be found at the following location. Your server may differ depending on its configuration.

C:\Program Files\FileHold Systems\Application Server\LibraryManager\web.config

Make a backup copy of the file before making any changes.

There are 7 values that can be configured. The first 6 are available in the <appSettings> section. Alternate credentials are only necessary in circumstances where the FileHold service user on the application server is not enabled to connect to the reporting services database.

Description Key Mandatory Value
Enable or disable reporting ReportServerActive Yes True to enable and false to disable.
Domain for alternate credentials ReportServerDomain No  
User for alternate credentials ReportServerUser No  
Password for alternate credentials ReportServerPassword No  
Folder name for FileHold reports off root Reporting Services root folder ReportRootFolder Yes  
Enable debugging errors to be displayed ReportShowErrors No True to enable and false to disable


Microsoft SQL Reporting Services 2008R2 configuration for FileHold

The seventh value that can be configured is the URL for the reporting server. This is only necessary 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.

Microsoft SQL Reporting Services 2008R2 configuration for FileHold

Installing the Included Reports

There are two pre-built reports that can be downloaded from our web site.

  • 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.

Both report files (.RDL) should be uploaded to the reporting server in the FileHold root folder or any sub-folder off the FileHold root folder.

Installing Microsoft SQL Reporting Services 2008R2 reports

Once the reports are uploaded, new data source definitions will need to be created to support the reports. From the folder view containing the newly uploaded report definitions, click the New Data Source button. Enter the name for the data source as shown on the following screen. Do the same for the connection string.

Installing Microsoft SQL Reporting Services 2008R2 reports

Installing Microsoft SQL Reporting Services 2008R2 reports

Once the sample reports have been correctly installed and configured, the folder view should appear as follows.

Installing Microsoft SQL Reporting Services 2008R2 reports

The reports must now be connected with the data sources. Select the context menu for the report and choose Manage.

Installing Microsoft SQL Reporting Services 2008R2 reports

Choose the data sources page and press the Browse button to find a shared data source.

Installing Microsoft SQL Reporting Services 2008R2 reports

You should see the two new data sources you just created. Choose the Library Manager data source for the LibraryListReport. For the SystemActivityReport choose the User Role Manager data source.

Installing Microsoft SQL Reporting Services 2008R2 reports

You can test the reports first by running them from the SQL Reporting Services page. When they are operating correctly you can run them from the FileHold user interface.