If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

Setting Up Aptify to Use SQL Server Reporting Services

This topic describes how to configure your SQL Server 2012 environment to support SSRS reports.

Pre-requisites

Steps

  1. Launch the Reporting Services Configuration Manager from your database server. Specify the Server Name and Report Server Instance.
    • If you have not configured your instance of SQL Server to use Reporting Services or if the service is not currently running, you will not be able to select a Report Server Instance. See the product documentation for your particular version of SQL Server for more information.

      Select Server Name and Instance
  2. Select Service Account from the tree.
  3. Check User built-in account and select Network Service from the drop-down.  
    • Using the User built-in account option makes local admin administrators in the Report server and the only admins. That is, local admin will be able to manage reports and make changes to the server (i.e. add permissions for users, etc.) You can change who has access to the Report later on.
       
  4. Click Apply.
  5. Select Web Service URL from the tree. 
  6. Enter the Virtual Directory as SSRSReportServer (or a different name of your choosing).
    • If you specify a path for the Report Server during the Aptify 5.5.4 setup, this is the name of the directory you specified as part of the SSRS Report Path. See Installing Aptify 5.5.4 Server Software for more details.

      If you did not specify a directory or the directory you specified does not exist, you will need to click Apply before continuing to the next step. 

  7. Click the Advanced.. option.
    • You many have to click Apply or exit out of the Reporting Server Configuration Manager and re-launch it before the Advanced... option is enabled.
    • For web servers that will be available on the public Internet, we recommending securing the site using HTTP over a Secure Socket Layer (SSL) to create an HTTPS site. The default installation of the Aptify web requires SSL. 
       
  8. In the Multiple SSL identities for Report Manager section, click the Add button and select the certificate from the certificate drop-down. The Default SSL Port is 443.

    SSL Bindings

  9. Click OK and then OK again to close the Advanced.. dialog.
  10. Click on Apply (if needed, clicking OK on the Advanced... dialog may automatically start the URL creation process).
  11. Select Database from the tree. 
  12. Click Change Database and select Create a new report server database.
    • In versions prior to SQL Server 2014, you may be able to select the Create a new report server database option directly. 
       
  13. Click Next.
  14. In the Database Server setting, you can select Integrated Security SQL Server Account as the Authentication Type depending on your organization's needs. 
    • You can click Test Connection to make sure the authentication type you select can connect successfully. If using Integrated Security, the domain user in which you have logged into the database server must have a valid user in the Aptify database. 
       
  15. Click Next.
  16. Enter the database name as ReportServer and click Next.
  17. Enter the credentials of an existing account that the report service will use to connect to the report server database. 
  18. Click Next.
  19. Review the Summary screen and then click Next to complete the process and then Finish. 
    • You may need to click Apply to apply the changes.
       
  20. Select Report Manager URL from the tree.  Enter the Virtual Directory Name as SSRSReport.
  21. Click Apply.
  22. Click the Advanced... option.
  23. In the Multiple identities for Report Manager section, click Add to add the localhost URL.

     
     
  24. Click OK.
  25. In the Multiple SSL identities for Report Manager section if not already added, click the Add button and select the certificate from the certificate drop-down. The Default SSL Port is 443.
  26. Click OK.
    • You should now be able to use both http and https for SSRS reporting.
       
  27.  Open the rsreportserver.config file and make sure the SecureConnectionLevel key is set to 0. 
    • When running  SQL Server 2014, this file is found in C:\Program Files\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer by default.
    • When running  SQL Server 2012, this file is found in C:\Program Files\Microsoft SQL Server\MSRS11.SQL2012\Reporting Services\ReportServer by default.
       
  28. From a Browser, open the Report Manager URL (for example, https://servername.domain/SSRSReport).
  29. When prompted, enter the username and password of a user who has administrative rights to report server.
    • If you used the Use built-in account option, only accounts with administrative rights locally to the database server or with role-based administrative rights to the report server itself can access the report manager.
       
  30. Select New Folder and create a folder called Reports (will store the SSRS reports).
  31. Select New Folder again and create another folder called DataSource (will store the data sources that will be used to connect between your reports and the Aptify database).
  32. In the Reports folder, click Upload File to upload the sample reports provided by Aptify (Invoice.rdl and PersonRoster.rdl) and any other reports you want to add.
    • The sample reports included in Aptify 5.5.4 are stored as objects in the Aptify Object Repository. You can obtain these reports by downloading them from the object repository.
    • Additional steps are required to add new reports to Aptify. See Adding Reports to an Entity for more details.
       
  33. Navigate to the DataSource folder and click New Data Source and complete the steps below to create a data connection between the report and the Aptify database:
    1. Enter a name for the Data Source
      • This can be shared across multiple reports.
         
    2. Select Datasource type as Microsoft SQL Server
    3. Add the connection string: Data Source=databaseserverinstance;Initial Catalog=Aptify
    4. Select an authentication method, based on your organization needs, Windows Integration is recommended.
    5. Click  Test Connection to make sure the connection is successful.
    6. Click Apply

      Datasource 
       
  34. To apply the new data source to the uploaded reports, navigate to the Reports folder, make sure you are in Detail View, and select Manage from the drop-down for the report you want to manage.
  35. Click the Data Source tab, select A select shared data source and browse to the data source you created in the previous steps.
  36. Click Apply.

 

If you want other users to at least open reports, you need to provide the appropriate security rights using the steps below. The following steps illustrate how to add trusted domain users so they can access reports. 

  1. Click on Site Settings in the top-right corner and go to Security tab.
  2. Click on New Role Assignment and enter NT AUTHORITY\Authenticated Users in the group or username text field
  3. For end users who are only viewing reports, select System User as the role and then Click OK.

    If a user is required to provide administrative rights, you will need to assign System Administrator rights to that specific user

  4. To apply the new security updates to the uploaded reports, navigate to Home and select Security from the Folder drop-down.

  5. Click on New Role Assignment and enter NT AUTHORITY\Authenticated Users in the group or user name text field.
  6. Check the appropriate options (like Browser and My Reports) and click on OK.

Verifying SSRS Report Run in Aptify

  1. Log into Aptify desktop and navigate to the Object Repository Objects service.
  2. Open the objects for each report you added (in our case, the sample reports PersonRoster_SSRS and Invoice_SSRS) and verify that the Download Path is correct. The path should be something like: https://<ServerName>/<SSRSServerName>?/<ReportsFolderName> 
    • If you specified a Report Path in the Aptify 5.5.4 setup, the Download Path will already be populated. Verify it is correct based on how  you set up your SQL Server Report Server.
       
  3. Once you have verified the path is correct for all reports, close and re-open the Aptify desktop as an end user and make sure you can run the report from an appropriate view.
  4. If you are using Aptify web, you will need to restart  the application pool for the SOA user (typically Aptify_SOAWeb) before reports can be viewed by users through the web interface.
    • The above steps is sufficient when making changes to an existing report's object repository records. However, when adding a new report (which require a brand new object repository record), you must run the Object Repository Synchronizer utility to update your web server's bin folders so it has the latest Aptify objects.  You can also manually add the object to the bin as needed. See Running the Object Repository Synchronizer for more information.

 

 

 

Copyright © 2014-2017 Aptify - Confidential and Proprietary