Signature SSRS Report Setup

System Requirements

To find a complete list of system requirements across all the Signature modules, refer to System Requirements.

Prerequisities

Certain IIS features must be installed before you can enable SQL Server Reporting Services to be installed with SQL Server. For SQL Server 2012 or later, select all the System Requirements, as well as the additional features below. Select Start > Administrative Tools > Server Manager > Roles > Add Roles, and select the Web Server (IIS)role to configure.

Web Management Tools

• IIS 6 Management Compatibility
  • IIS 6 WMI Compatibility
  • IIS Metabase and IIS 6 configuration compatibility
• IIS Management Console
• IIS Management Scripts and Tools
• IIS Management Service

World Wide Web Services

• Application Development Features
  • .NET Extensibility
  • ASP.NET
  • ISAPI Extensions
  • ISAPI Filters
• Common HTTP Features
  • Default Document
  • Directory Browsing
  • HTTP Redirection
  • HTTOP Errors
  • Static Content
• Security
  • Request Filtering
  • Windows Authentication

Health and Diagnostics

• HTTP Logging and Request Monitor

Performance

• Static Content Compression

Before You Begin

Before deploying the latest Signature reports, you must have SSRS reports set up. This setup requires the following steps:

Step 1: Install SQL Server Reporting Services

SQL Server Reporting Services is part of the Microsoft SQL Server installation pack. You must install this to be able to use SSRS reports in Microsoft Dynamics GP and Signature. Refer to the Microsoft SQL Server documentation for information on installing SQL Reporting Services.

Step 2: Enable Use of Microsoft Dynamics GP SSRS Reports

If you have not done so already, you must run the Microsoft SQL Server Reporting Services wizard to enable access to SSRS reports within Microsoft Dynamics GP.

1. Launch the file Microsoft.Dynamics.GP.BusinessIntelligence.SRSDeployment.exe
2. On the Welcome Screen, select Next >.
3. Enter the Microsoft Dynamics GP Server name and instance. For example, if the server name is Fred and the instance Fred1, you would enter Fred\Fred1.
4. Enter your server User Name and Password, then select Next >.
5. Select the company database for which to enable reports, for example, TWO. In addition, select the Microsoft Dynamics GP products to enable reports for, by marking the appropriate checkboxes. Mark Select All to select ALL products. Select Next >.
6. Enter the Target Server URL as http://MyMachineName/ReportServerName_ where _MyMachine is your machine name and MyReportServerName is the name of the report server given when you installed SQL Reporting Services. To determine the name of the report server, select Start > All Programs > Microsoft SQL> Configuration Tools > Reporting Services Configuration. Connect to your server and select Web Service URL. The Virtual Directory field contains the name of the report server. When you are ready to deploy reports, select Finish.
7. When processing is complete, if you want to deploy reports for another company, mark the checkbox and select OK to start the wizard again. If you are finished deploying reports, leave the checkbox unmarked and select OK.

Step 3: Define the Location of SSRS Reports Server and Report Manager

1. Select Microsoft Dynamics GP > Tools > Setup > System > Reporting Tools Setup. The Reporting Tools Setup window opens.
2. Complete the following fields on the Reporting Services tab:
   • SQL Server Mode
     Select Native mode. Signature SSRS reports do not currently support SharePoint Integrated mode.
   • Report Server URL
     This is the location of the reporting server site that hosts the web service. You specified this location when you installed SQL Server Reporting Services. Enter: http://MyMachine/MyReportServerNamewhere MyMachine is your machine name and MyReportServerName is the name of the report server given when you installed SQL Reporting Services. To determine the name of the report server, select Start > All Programs > Microsoft SQL> Configuration Tools > Reporting Services Configuration. Connect to your server and select Web Service URL. The Virtual Directory field contains the name of the report server. The instructions for finding the report manager URL may vary depending on which version of SQL Server you are running.
   • Report Manager URL
     Enter the Web location where the Report Manager is accessed. Enter: http://MyMachine/MyReportsFolder where MyMachine is your machine name and MyReportsFolder is the name of the virtual directory of the Report Manager. To determine the name of the report server, select Start > All Programs > Microsoft SQL> Configuration Tools > Reporting Services Configuration. Connect to your server and select Report Manager URL. The Virtual Directory field contains the name of the report server. The instructions for finding the report manager URL may vary depending on which version of SQL Server you are running.
3. When you are finished, select OK to save the Reporting Tools Setup window.

Setting up Signature Reports

Complete the following steps to enable the use of Signature SSRS reports.

Step 1: Deploy Signature SSRS Reports

To deploy Signature SSRS reports, you must run the Signature SQL Reporting Wizard.

To ensure a clean installation and deployment of the new reports, you should have removed any existing Signature SSRS reports from both the Microsoft Dynamics GP install directory and the Report Manager. Refer to the Signature Install and Upgrade manual for more information.

Before you begin, determine the name of your SQL report server, which was set up when SQL Server Reporting Services was installed. Select Start > All Programs > Microsoft SQL> Configuration Tools > Reporting Services Configuration. Connect to your server and select Web Service URL. The Virtual Directory field contains the name of the report server; write down this name, as you must enter it in the steps that follow.

The instructions for finding the report manager URL may vary depending on which version of SQL Server you are running.

To start the Signature SQL Reporting Wizard, navigate to your Microsoft Dynamics GP install directory, then open the Signature\SRS Reports folder and launch the file Signature.Dynamics.GP.BusinessIntelligence.SRSDeployment.exe.

You can also run this wizard from the following locations within Microsoft Dynamics GP:

• From Service Management, select Run Wizard in the Service Options window.
• From Job Cost, select Run Wizard in the Job Cost Setup Options window.
• From Equipment Management, select SRS Wizard in the System Setup window.
• On the Welcome screen, select Next >.

1. Enter the Microsoft Dynamics GP Server name and instance. For example, if the server name is Fred and the instance Fred1, you would enter Fred\Fred1.
2. Enter your server User Name and Password, then select Next >.
3. Select the company database for which to enable reports, for example, TWO. You must run the wizard multiple times if you wish to deploy SRS reports for multiple companies.
4. Enter the Report server URL. This is the location of the reporting server site that hosts the web service. You specified this location when you installed SQL Server Reporting Services. Enter: http://MyMachine/MyReportServerName where MyMachine is your machine name and MyReportServerName is the name of the report server given when you installed SQL Reporting Services.
5. Enter the Dynamics/Signature Directory. This is the directory where Microsoft Dynamics GP and Signature are installed.
6. Select Next >.

7. All SSRS report folders found in the Dynamics/Signature directory appear on the next wizard screen. Unmark the checkbox next to any folder, or expand a folder and unmark the checkbox next to any report, that you do not want to deploy. 

   To use KPI reports and report templates, you must be running SQL Server Reporting Services 2008 R2 or later. Additionally, you must have SQL 2008 R2 Business Intelligence Studio installed to deploy the Signature Template Pivot report template.

8. Select Deploy. It will take a few moments to deploy the reports. A message appears when the deployment is successful. Select OK. The Signature SQL Reporting Wizard starts over again. You can either deploy reports for an additional company database by choosing Next, or you can exit the wizard by choosing Cancel.

Additional Setup for Equipment Management Reports

For Equipment Management, you must also set up your SSRS reports in the Report Definition Setup window (Microsoft Dynamics GP > Tools > Setup > Equipment > System > Report Definitions). For step-by-step instructions, refer to the Advanced Rental feature chapter of the Equipment Management User Guide.

Step 2: Set up the Company Logo (Optional)

You can customize the company logo that appears on some of your customer-facing reports, for example, invoices.
For each of the reports that display a logo, the .rdl file points to the subfolder Signature Images and the file Company Logo. The default logo is a transparent image that appears on the reports as blank.

1. In Report Manager, locate the company where reports are deployed, and open the folder Signature Images.
2. Rename or delete the default Company Logo file by choosing Show Details, then Edit or Delete.
3. Return to the Signature Images folder and select Upload File. Before uploading, change the name of your new logo to Company Logo (no file extension), then select OK.
      

   

   
   

   

Step 3: Set up Signature SSRS Reports to Print from GP

To set up Signature SSRS reports to print from the appropriate window in the GP application, you must replace the provided Dexterity reports with the corresponding Signature SSRS reports. This requires that you either manually populate the WSRepts table with the pathname to the replacement report, or run the stored procedure WS_SetReplacementReportsForSRS, which populates all applicable Signature SSRS report pathnames into the table.

About the WSRepts Table

The table WSRepts stores the path location for replacement reports. This table is created in each company database during the Signature installation or upgrade process.

A select statement on this table yields something like the following:

The ReportReference column identifies the name of the report you want to replace, most likely a Dexterity report name.

The ReportLocation column stores the full path and file name of the report being referenced. This could be a local SSRS report or the URL of a remote SRS report. A blank column assumes the system is running the Dexterity version of the report. To replace the Dexterity report with the SSRS report, you must populate this column with the SSRS report location.

In the example below, an existing Dexterity report is replaced with an SSRS report using the stored procedures.

UPDATE WSRepts Set ReportLocation = 'http://localhost/ReportServerNew/TWO/Signature Service/Service Cost Audit' where ReportReference ='SV_Service_Cost_Audit_Report'
If you are replacing reports individually, you can determine the name of the Dexterity report by printing that report from within the Signature system. For SSRS reports, you also need to know the machine name on which your report server resides.

Running the SQL Stored Procedure

The SQL procedure WS_SetReplacementReportsForSRS globally replaces all applicable Dexterity reports with the SSRS report equivalent.
Running this procedure DOES NOT update a ReportLocation that already contains a value; it only applies to blank ReportLocation columns, which assumes that the Dexterity report is being used. If you already have a custom report specified to replace the Dexterity report, it will not be overwritten.

To replace Dexterity reports, run the following command against the company database to call on the stored procedure: exec WS_SetReplacementReportsForSRS
The common printing DLL Signature.ReportControl.dll provides the WSRepts table and the two SQL procedures that can be used to set up SQL reporting. For more information on this DLL, refer to the user manual.