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.
- 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:
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.
When you have properly connected to the Reporting Services Configuration Manager, you will see the following screen.
Choose an appropriate service account for your environment.
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.
You will now need to point to the report server database. Click Change Database to proceed.
This is the database used for reporting services information. Create a new one or use an existing one as appropriate for your environment.
Use the FileHold service account for authentication or another account as appropriate for your environment.
When you have finished configuring the database the screen should reflect your changes.
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.
If everything is configured correctly, you should see the standard reporting screen. Click on Folder Settings to configure security for the root folder.
From the security page, click on New Role Assignment.
Enter the FileHold service account name, select the browser role, and click OK.
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.
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.
|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|
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.
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.
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.
Once the sample reports have been correctly installed and configured, the folder view should appear as follows.
The reports must now be connected with the data sources. Select the context menu for the report and choose Manage.
Choose the data sources page and press the Browse button to find a shared data source.
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.
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.
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.
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.
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.
Browse to the home.aspx within the Reports site using IIS Manager. Right-click Home.aspx and select Browse.
Once the reporting services page loads in, click the New Folder button. Create a folder called FileHoldReports and click OK.
Once complete you should then ensure you are in the FileHoldReports area as per below.
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.
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.
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.
You will then see the 2 report files and the 2 data sources appear in the FileHoldReports folder.
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.
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.
Set permissions for Browser Role for FH Service.
- Go to the SQL Server Reporting Services > Home and then click Properties.
- Click New Role Assignment.
- 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.
In the two screenshots below, the security properties for each data source is displayed.
Test the LibraryList Report. You should see the Cabinets, Drawers and Folder selections within FileHold if all is setup properly.
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.
Now you will continue with integrating this within the FileHold Web Client > Library Admin > Reporting Services > Reports.
- 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.
- 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.
- Once you have edited the web.config file, restart IIS.
- 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.
- 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.
- You should then see this appear for LibraryListReport.
- And this report will appear when you select SystemActivityReport.