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.

Ensure that Windows Integrated security for report data source connections is set in SQL Surface Area Configuration.

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 and 15 with SQL 2005, SQL 2008, SQL 2012, or SQL 2014: 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

Reporting Services are installed along with SQL Server. If for some reason SQL Reporting Services is not installed on your SQL Server you can always run the SQL Server installation program again and select to install additional components. Once complete, SSRS will be configured automatically, but some additional setup will be required.

Internet information service (IIS) must be running on the server where you will install SSRS.

After installation, open the SQL Server folder in All Programs, then select the Configuration Tools folder under it, locate the Reporting Services Configuration Manager application and run it.

You should not need to change the default settings, just go to the tab Web Service URL and make a note of the URL of the Report Server's Web Service. You will need to specify this URL in deploying new reports.

Once you setup the Reporting Services administration site, confirm that you can login and use the site. You can now proceed with and work through the rest of the guide. If you cannot get SSRS or the administration site working correctly contact your Microsoft support team. FileHold does not provide support services for SQL server.

Configuration of SQL Reporting Services

Start reporting services configuration. A menu option should have been added to the Windows menu during the SQL Reporting Services installation. Enter your server details and press Connect.

Microsoft SQL Reporting Services 2008R2 configuration

When you have properly connected to the Reporting Services Configuration Manager, you will see the following screen.

Microsoft SQL Reporting Services 2008R2 configuration

Choose an appropriate service account for your environment.

Microsoft SQL Reporting Services 2008R2 configuration

For the web service URL, accept the defaults or choose appropriate values for your environment. You will need to remember these settings for later in the installation process if the SQL Reporting Service is installed on a different machine than the FileHold application server.

Microsoft SQL Reporting Services 2008R2 configuration

You will now need to point to the report server database. Click Change Database to proceed.

Microsoft SQL Reporting Services 2008R2 configuration

This is the database used for reporting services information. Create a new one or use an existing one as appropriate for your environment.

Microsoft SQL Reporting Services 2008R2 configuration

Microsoft SQL Reporting Services 2008R2 configuration

Use the FileHold service account for authentication or another account as appropriate for your environment.

Microsoft SQL Reporting Services 2008R2 configuration

Microsoft SQL Reporting Services 2008R2 configuration

Microsoft SQL Reporting Services 2008R2 configuration

When you have finished configuring the database the screen should reflect your changes.

Microsoft SQL Reporting Services 2008R2 configuration

Testing the SQL Report Server Configuration

The basic configuration should now be complete. You can test that the report server is working correctly by clicking the URL on this screen.

Microsoft SQL Reporting Services 2008R2 testing

If everything is configured correctly, you should see the standard reporting screen. Click on Folder Settings to configure security for the root folder.

Microsoft SQL Reporting Services 2008R2 testing

From the security page, click on New Role Assignment.

Microsoft SQL Reporting Services 2008R2 testing

Enter the FileHold service account name, select the browser role, and click OK.

Microsoft SQL Reporting Services 2008R2 testing

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.