Creating Custom Reports

To create custom reports, connect to the reporting URL via the VPN and run the Report Builder.

See the SSRS documentation at https://msdn.microsoft.com and the SSRS tutorials on Microsoft TechNet for information on using SSRS and Report Builder.

  1. Connect to the VPN using the appropriate two-factor authentication.
    • When using Salesforce Authenticator, the password consists of your personal VPN password plus the current 6-digit code shown in the Salesforce Authenticator app. For example, if your password is [email protected], and the code shown in the app is 654321, then you would enter [email protected] in the Password field.
  2. Access the Report Builder using the appropriate URL: .
    • For US users (US01): http://oc-rs01.us01.oc.demandware.net/Reports
    • For US users (US02): http://us02p-rs01.us02.oc.demandware.net/Reports
    • For EU users (POD10): http://oc-rs01.pod10.oc.demandware.net/Reports
    For best results, use Microsoft Internet Explorer and run it as an Administrator. See Troubleshooting.
  3. Enter your generated username and your password and select Remember Me.
    The username format is US01\username, US02\username, or POD10\username, depending on your instance.

    The generated username for logging into the reporting database takes the form of up to the first 17 characters of the Order Management username, followed by a random 2-digit number, followed by either a “P” for Production instance or “S” for Stage instance. Any special characters in the Order Management username are replaced with an underscore (_). Special characters might include: / []:;|=,+?<>@.*" This username appears on the user account detail page in Order Management. The password associated to this database user account is the same as the one used to access Order Management.

    You must select Remember my credentials each time. It isn't saved for the next session; it only maintains the current authoring session.

  4. In the reporting back end, click Report Builder at the top of the page.
    The Microsoft SQL Report Builder 3.0 application opens. The first time you run it, you might need to wait for it to download and install. If the automatic installation fails, you can download it from the Microsoft website.
  5. In Report Builder, right-click the Data Sources folder and click Add Data Source to add a data source.
    1. Enter a report name.
    2. Click Use a shared connection.
    3. Select Custom > your_domain > BF_DATASOURCE. Do not select a different data source. If you do, then database updates may break your reports.
    4. Enter the name of the data source and click OK. You can give the data source any name, as long as it points to BF_DATASOURCE.
    You should see the data source added to the hierarchy.
  6. Right-click the Datasets folder and click Add Dataset to add a dataset to the report.
    This contains all the fields in the report, as well as the SQL to retrieve the data for those fields.
    1. Enter a name.
    2. Select Use a dataset embedded in my report.
    3. Under Data source, select the data source you just created.
  7. Paste in the SQL for your report, or use the Query Designer option.

    The Query Designer helps when you don't know all the tables / field names available in the data source.

  8. Add the WITH (NOLOCK) hint to each of your SELECT statements, or set the transaction isolation level to READ UNCOMMITTED for your entire SQL 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. Failure to do so might result in queries that take a long time to run. Note that a report query is terminated by the system automatically once it exceeds 10 minutes.

    This example query creates a list of the first 500 master items and their current statuses, ordered by item code:

    SELECT TOP 500 item_cd, item_status FROM dbo.mf_items_view WITH (NOLOCK) WHERE item_type_id = 3 ORDER BY item_cd

    To see the query results, paste it into the query section and click Refresh Fields.

  9. When prompted for credentials, the user name should be pre-populated with the VPN account username. Enter the VPN password and click OK.
    If a timeout error appears, try again.
    The newly created dataset appears beneath Datasets.
  10. Select the Table Wizard to add the data to your report page.
  11. Choose the dataset you just created.
  12. The next two pages enable you to perform grouping and layout changes.
    When you are finished with the wizard, the table appears on the page.
  13. Click Run on the top left to view the report results.