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.

IMPORTANT: 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.

TIP: 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

Configuring Microsoft SQL Reporting Services 2008R2, 2012, or 2014 for FileHold

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.

Configuring Microsoft SQL Reporting Services 2005 for FileHold

Step 1

Open up SQL Server 2005’s Reporting Services Configuration Manager and work through all the steps below until you see the green checkmarks as below. Please note that this server was a SQL 2005 Express machine that was upgraded to SQL 2005 Standard using Microsoft’s upgrade feature within SQL 2005 Standard. The instance in the screenshot is still named SQLEXPRESS even though this is actually SQL 2005 Standard. Please note, this SQL Reporting configuration area can be frustrating at times to work through, while sometimes it is very easy.

 Configure Report Server

Step 2

Once you have completed the Database setup as below, you can then open up SQL 2005 Management Studio to assign the proper access permissions and login credentials for the FH_Service account.

 Database connection for SQL reporting

Since the FH_Service account already has access to all 5 databases in FileHold you just need to give it data reader access to the two Reporting databases (Report Server and ReportServer Temp DB).

Please note - use the FH_Service account here, the screenshots below show an FHReporting - this is not needed. They are to show that other configurations are possible, although out of scope for this guide. If you are experienced with SQL Reporting services - you are welcome to try your own configuration. Please note that FileHold does not provide free technical support with SQL Reporting.

 SQL Reporting Reader Access

Step 3

Browse to the home.aspx within the Reports site using IIS Manager. Right-click Home.aspx and select Browse.

Browse home.aspx

Step 4

Once the reporting services page loads in, click the New Folder button. Create a folder called FileHoldReports and click OK.

New folder - SQL reporting services

Once complete you should then ensure you are in the FileHoldReports area as per below.

FileHold Reports - SQL Server

Step 5

Upload the two FileHold report definition files (LibraryListReport.rdl and SystemActivityReport.rdl) by clicking Upload File.

Once you have uploaded both report definition files, you will then see the two reports appear.

SQL reports upload results

Step 6

Click New Data Source to add a data source for Library Manager database. Keep in mind that the Datasource referenced below should be adjusted to the server or server\instance used by the database server running FileHold.

SQL Reporting - Add a Data Source
 

Step 7

Add another data source for User Role Manager data source. Keep in mind that the Datasource referenced below should be adjusted to the server or server\instance used by the database server running FileHold.

SQL - user role manager data source

You will then see the 2 report files and the 2 data sources appear in the FileHoldReports folder.

SQL Reporting - Reports and Data Sources

Step 8

1. Add the data source to corresponding Report. Click on the LibraryListReport and go to the Properties tab and the Data Sources section. Select the Library Manager data source and click OK and then Apply.

Mapping the Library List Report to the Library Manager data source

2. Click on the SystemActivityReport and go to the Properties tab and the Data Sources section. Select the User Role Manager data source and click OK and then Apply.

Mapping system activity report to User role manager data source

Step 9

Set permissions for Browser Role for FH Service.

  1. Go to the SQL Server Reporting Services > Home and then click Properties.
  2. Click New Role Assignment.
  3. Enter in the FH_Service account information as being able to access reporting services using the browser role. Below the FH_Service has been added.

SQL Role Assignments

In the two screenshots below, the security properties for each data source is displayed.

Library Manager Security Settings

Step 10

Test the LibraryList Report. You should see the Cabinets, Drawers and Folder selections within FileHold if all is setup properly.

SQL Reporting - Test Library List Report

Step 11

Test the SystemActivity Report. You should see the Client Type, Last Name, Login Time, User ID, First Name and Logout Time selections within your FileHold server if all is setup properly.

Test System Activity Report - SQL Reporting

Step 12

Now you will continue with integrating this within the FileHold Web Client > Library Admin > Reporting Services > Reports.

  1. Setup the name of the Report Folder in the Web.config in FH\FileHold\LibraryManager\Web.config file.
    In Windows 2008 Server, you will need to look in the Program Files\FileHold Systems\Application Server\LibraryManager folder for the web.config file.
  2. This uses Windows Authentication so all you need to do is provide the exact name of the ReportServerFolder you created in SQL Reporting Services. See the example below.
  3. Once you have edited the web.config file, restart IIS.

SQL Reporting - editing web.config file

Step 13

  1. Test within FileHold. Login as a Library Administrator or System Administrator to the FileHold Web Client and then click on Library Admin in top right corner of web browser.
  2. Click on Reporting Services > Reports. You can setup Report security and distribute the reports to various users/groups in both the Web Client and Desktop Client.
  3. You should then see this appear for LibraryListReport.

 FileHold Library List Report

  1. And this report will appear when you select SystemActivityReport.

FileHold System Activity Report