Reporting Services

Order Management includes a powerful reporting system that uses Microsoft SQL Server Reporting Services (SSRS) and Report Builder. This facilitates customized reports based on your data with far greater granularity than the Quick Export feature in the Administration module. These reports run SQL queries against a reporting database that is a mirror of the production database, minus sensitive information (such as credit card numbers) due to PCI compliance requirements. This also avoids affecting performance of the production environment. The reporting database is synchronized with the production database approximately every 10 minutes. Access to the reporting database is provided on an as-needed basis via VPN. Access and support requests should be placed through the Salesforce Support portal.

If Reporting Services doesn't appear in the Administration module accordion menu, you have to enable it in the provider. It's enabled by default.

Before using reporting services, you must configure VPN access to the reporting server.

When you have configured access, use Report Builder to create your custom SSRS-based reports. The Order Management Database Schema Tables are provided to help you design your reports. For information on SSRS and Report Builder functionality, such as scheduling reports and exporting them to files, as well as system and software requirements, see the SSRS documentation at https://msdn.microsoft.com and the SSRS tutorials on Microsoft TechNet.

Note: Report authors have read-only access to dbo objects in the database, as well as execute and select rights to cst schema objects. Salesforce Client Services users additionally have create/modify/delete access to cst schema objects.

Note: When running a query against the reporting database, whether through a report or by directly accessing the DB, always add the

WITH
    (NOLOCK)

hint to each of your SELECT statements, or set the transaction isolation level to READ UNCOMMITTED for the entire query. This prevents locking issues with other reports and with the reporting database update process. Because the reporting database is read-only aside from the automated mirror process, report queries don't need locks. Best practice is also to limit the amount of returned data by specifying a TOP value, especially when directly accessing the DB. Failure to do so might result in queries that take a long time to run.

You can view your reports within the Administration module or via the Reporting URL.

For best results, access the reporting system by running Microsoft Internet Explorer as an Administrator. Note that there are known issues with certain versions of the browser.

You can query the reporting database using Microsoft SQL Server Management Studio (SSMS). This let's you query the read-only data directly from the customer's reporting database.