Tenjin Icon

Install and Configure Reporting in Front Office

The Reporting feature is a licensed option. Please either email info@biomni.com or contact the support team at https://frontofficehelp.biomni.com/hc/en-us/requests/new to find out more information.

The files needed to set up reporting are in Front Office 9.7 Reports.zip.

This article covers the following topics:

Architecture

Reports are hosted by an instance of SQL Server Reporting Services (SSRS) in SQL Server, which does not need to be the same server that hosts the Front Office database. The reports are set up in a folder in SSRS to isolate connection strings and customizations for different installations of Front Office.

The Front Office portal uses a web service to connect to SSRS to get the report definitions. Access to the reports can be configured for different user groups. Finally, when the reports are viewed, Front Office connects to SSRS using a web service to run and display the reports.

Prerequisites

  • SQL Server Reporting Services (SSRS) 2014 or 2016, 2017, 2019, or 2022(preferred) is required to run Front Office reports; the Front Office database itself does not need to run on the same version or the same server.
  • Reporting is not supported in Azure SQL or AWS RDS environments.

Installing Reports in SQL Server Reporting Services

A license that allows Reporting is required for this feature.

Note: There have been user interface changes between versions of SSRS. These instructions focus on the SQL Server Reporting Services 2017 UI but the same can be achieved in other supported versions.

  1. Copy Biomni.Directa.Reporting.SupportCode.V1.dll to the [SQL Server installation directory]\Reporting Services\ReportServer\bin directory.
    By default this will be:
    Version Path
    2022 %ProgramFiles%\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin
    2019 %ProgramFiles%\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin
    2017 %ProgramFiles%\Microsoft SQL Server Reporting Services\SSRS\ReportServer\bin
    2016 %ProgramFiles%\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer\bin
    2014 %ProgramFiles%\Microsoft SQL Server\MSRS12.MSSQLSERVER\Reporting Services\ReportServer\bin
  2. Ensure Reporting Services is configured using Reporting Services Configuration Manager, in particular, the Web Service URL, Database (click Change Database to create the database), and Web Portal. The Web Service URL virtual directory should be titled ‘ReportServer’ and the Web Portal URL should be ‘Reports’. Click Apply on these screens to create the corresponding site.
  3. Set the Reporting Services Session Timeout.
    Open SQL Server Management Studio as an Administrator, connect to ‘Reporting Services’.
    Right-click the server > Properties, select the Advanced page, set SessionTimeout to at least the Front Office session timeout.
    Note: Front Office session timeout is in minutes, reporting services is in seconds.
  4. On the report server machine, run a browser as Administrator and navigate to: http://localhost/Reports.
  5. Create a folder; for example, FrontOffice. This is where the reports will be installed. The purpose of the sub folder is to isolate connection strings and report customizations for different installations of Front Office.
  6. The Front Office reports adapter requires a domain account to connect to the report server. The account can be any domain account and requires no special rights, although it is important that the password will not change or expire. This account will be used as the Security credentials that the Front Office Adapter uses to run the reports (this will be set up in a later step).
    Drill into the folder created above, click Manage Folder, and select the Security tab. You should see that BUILTIN\Administrators has the Content Manager role. Click Customize Security then Add group or user. Enter the domain user account Front Office will use to access the reports into Group or user and assign the Content Manager.
  7. The report server needs to connect to the Front Office database to retrieve data to build the reports. The report server can use the same SQL Server credentials to connect to the database as the portal, which are set when Front Office is installed.

    In the FrontOffice folder create a new data source called ‘FrontOffice’.
    1. Set the connection string to the Front Office database; for example, "Server=local);Database=FrontOffice;"
    2. Set the username and password under Using the following credentials to the same credentials the Front Office portal uses to connect to SQL Server.
    3. Click Test connection then, if successful, click Apply.
  8. In the FrontOffice folder upload the reports (.rdl files) from Front Office 9.7
    Reports.zip.
  9. Click on … (three dots) for each report, select Manage, choose the Data sources tab, and browse to the FrontOffice Data Source you created above (this may already be selected).
  10. Test the reports executed on the report server by clicking on each report.

Configuring Reports in Front Office

  1. In Front Office go to Admin > Support > Configuration Check and click on the Reporting tab.
  2. Set the 'Reporting Services Folder' to the name of the folder created above by clicking Edit.
  3. Create a ‘Reporting Services’ Adapter by clicking the Adapter link or icon.
    1. Click New
    2. Set the Web Service URI to the ‘Report Server Web Service URL’ in ‘Reporting Services Configuration Manager’ for the server the reports were uploaded to; for example,
      http://localhost/ReportServer.
    3. Set the ‘Authentication Mode’ to Windows Authentication.
    4. Set User Name and Password to the domain account that was given the ‘Content Manager’ role on the reporting services folder created above.
    5. Click OK.
  4. Ensure the following
    • There are no errors displayed. If errors are found, view them in Admin > Support > Error Log and see the ‘Security’ and ‘Resources’ sections below for further details, or search for the error message on the internet.
    • The reports expected are listed in Reports & Data sources.
    • The data source for each report is correct.

  5. In Front Office navigate to Admin > Organization > Report and click Refresh Reports. The reports from the folder created above should now be listed.
    NB: This step will need to be repeated when new reports are added, or modifications made.
  6. Click on each individual report in the list to configure user group access as required.
  7. Test each report by clicking on the Reports menu item in the top bar, selecting a report, and viewing it. It is recommended that a Microsoft browser be used to ensure all functionality is available.

Security

If the reports do not work some security settings for the network configuration may need to be adjusted. Consider the following:

  • If the error "The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'." is found it may be necessary to modify the rsreportserver.config file to allow the appropriate authentication method.
  • Try changing the Service Account to ‘Network Service’ or ‘Virtual Service Account’ in Reporting Services Configuration Manager.

Adapter Configuration

The report server name in the Web Service URI should use the ‘Full computer name’ or IP address. The ‘Full computer name’ can be found in the machine properties.

The username may need to use the UPN format rather than the old Windows 2000 format, for example, user.name@full.domain.name.com, rather than domain\user.name. This may not be essential or available in all network configurations but is the most reliable, if available.

Report Styling

Reports are unaffected by Front Office styles; the style is set in the report. Therefore, consideration should be given to applying any Front Office style customization to the reports.

Report Localization and Personalization

To assist with localization and personalization, Front Office automatically populates extra parameters with data for the user/system running the report, if the parameters exist on the report. To receive the benefit of these parameters, they should be added as hidden parameters with the names below. If they are not present on the report, Front Office will not attempt to populate them.

  • Directa_UserId
  • Directa_UserEmailAddress
  • Directa_UserTimeZoneKey
  • Directa_UserTimeZoneDisplayName
  • Directa_SystemTimeZoneKey
  • Directa_SystemTimeZoneDisplayName
  • Directa_UserCurrencyCode
  • Directa_UserCurrencyBeforeSymbol
  • Directa_UserCurrencyAfterSymbol
  • Directa_SystemCurrencyCode
  • Directa_SystemCurrencyBeforeSymbol
  • Directa_SystemCurrencyAfterSymbol

In addition to these custom parameters, the built-in User Language property is populated with the Front Office user’s culture code. The report Language property can be set to this property to allow consistent localization if required.

Biomni.Directa.Reporting.SupportCode.V1.dll may be referenced to provide consistent formatting and conversion. Specifically, there are some methods to help with the conversion of dates to and from time zones.

Share this article

Comments

0 comments

Article is closed for comments.