OData support for reporting

You can access your workspace data using OData (Open Data Protocol), the OASIS REST-based standard for accessing data. You can use OData to generate reports and charts in reporting and business intelligence (BI) tools.

Supported OData versions

OData support has been tested with Power BI and Excel. Any tool that supports OData version is likely to work well.

Caution: Tableau’s support for OData is limited. For example, Tableau's OData support currently does not include the $expand interactive query argument, which is used extensively for cross entity reporting. Consider using a different reporting or business intelligence tool.

For OData version support, download and install the Microsoft Power Query for Excel add-in.

Back to top

Prerequisites

Ensure the following prerequisites are met, before you begin to work with OData:

  • Request that the site admin or the space admin activate basic authentication with the SUPPORTS_BASIC_AUTHENTICATION configuration parameter for each space. For details, see SUPPORTS_BASIC_AUTHENTICATION.

    Caution: Activating basic authentication enables other external systems to access data using this authentication method, not just OData.

  • When authentication is configured with SSO, configure OData integration using the API access keys. For details, see API access.

  • Ensure that Access OData permission is granted to the appropriate role. By default, permission is enabled, but if you have created custom roles, make sure this option is enabled. For details see General System Actions in Roles and permissions.

Back to top

Server base URI

The OpenText Core Software Delivery Platform server base URI for accessing data using OData is: 

<https://server>/odata/v4/shared_spaces/<space_ID>/workspaces/<workspace_ID>/

Note: If you do not receive a successful response to an OData consumer request, it might be because the base URI is different than expected. Consider modifying the ODATA_USE_SERVER_BASE_URL and SERVER_BASE_URL parameters. For details, see Configuration parameters.

Access data from multiple workspaces

You can access data from multiple workspaces that exist within a shared space by modifying the base URI to include the following.

ID type Details
Workspace ID

A workspace whose ID is listed after /workspaces/ is considered the main workspace, and serves to provide metadata for the report, such as field names.
Cross-workspace ID Workspaces whose IDs are listed after /cross-workspace/ are the source of data, and serve to provide the field values for the report.

Using the modified URI, you can:

  • Include data from all the workspaces within a shared space.
  • Include data from specific workspaces within a shared space.
  • Include data only from active workspaces

Note: You cannot include the data from workspaces that exist within an isolated space.

Use case Details
Include data from all workspaces

To include the data from all the workspaces that exist within a shared space, enter an asterisk (*) after /cross_workspace/.

For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/*
Include data from specific workspaces

When including the data from specific workspaces, consider the following:

  • The base URI should list all the workspaces' IDs whose data you need to include in the report. Separate each workspace ID with a comma. For example: <https://server>/odata/v4/shared_spaces/<1001>/workspaces/<1002>/cross_workspace/<1002>,<1003>.
  • Only the data from workspaces whose IDs are mentioned after /cross-workspace/ is included in the report. It means that the main workspace's ID defined after /workspaces/ should also be defined after /cross_workspace/, otherwise the workspace data is ignored.

Note: Private fields defined in specific workspaces are not included in cross-workspace reports.

Back to top

Authenticating

To work with OData, review the prerequisites for basic authentication under Prerequisites, and then authenticate with basic authentication.

You can authenticate with either:

  • Your user name and password.

  • An API access key.

For details, see Basic authentication.

Tip: When working with basic authentication, on each successful authentication, the LWSSO_COOKIE_KEY cookie is included in the response. We recommend that you send the LWSSO_COOKIE_KEY cookie with each subsequent OData request for enhanced performance. For details about the LWSSO_COOKIE_KEY, see Authentication.

Back to top

Access data from a reporting or BI tool

Any reporting or business intelligence tool that supports OData is supported for integration, including Excel 2010 and later, Power BI, Power Query for Excel, and Power Pivot for Excel.

For a list of the tools that support OData, refer to the product documentation.

  1. Choose Home > Get Data > OData Feed.

  2. Enter the server base URI and click OK.

  3. Under Basic, enter your user name and password.

Now you can see all entities in the Navigator.

  1. Choose DATA > From Other Sources > From OData Data Feed.

  2. Enter the server base URI and click OK.

  3. Select Use this name and password and enter the information.

Now you can see all entities.

To troubleshoot issues with Microsoft applications, see Troubleshooting.

Back to top

Templates

Back to top

Scenario: Traceability report

This example demonstrates how to use Power BI to access data using OData. It shows, step by step, how to create a traceability report.

  1. In Power BI, set up the data feed for OpenText Core SDP with OData. For details, see Example: Access data using Power BI.

    Set up the data feed.

    Set up a basic feed using the OpenText Core SDP URI.

    Set up a basic feed using the platform URI.

    Enter the user name and password for basic authentication and click Connect.

  2. After connecting, you can see all OpenText Core SDP entities, and start creating a report.

    To see only stories, select stories and then click Edit.

  3. Next, click Choose Columns and select the columns that you want to see.

    1. For example, select the ID, name, release, and tests columns.

      Choose the columns to include in the report.

      Releases and tests are references to other entities. You can get more information about releases and tests by clicking on the right button for each column to expand them.

    2. Click the button for the release. To get the name of the release, select name.

    3. Similarly, click the button for tests, and then select the name and ID attributes.

    4. You can filter for items in a specific release by selecting the release to filter. For example, Release 2.2.

      Selkect the release to filter in the report.

    5. Click Close & Apply. Below are the results.

      Report results.

  4. Create a graph with selected data.

    In the Visualizations pane:

    1. Select the values.

    2. Select matrix visualizations.

    3. Arrange the rows, columns and values.

      Arrange the rows, columns, and values in the report.

    4. You can also add the pie chart visualization.

      Add the pie chart visualization to the report.

    5. Save and refresh the data.

Back to top

Troubleshooting

When trying to access data from a Microsoft application, a message similar to "The server has closed the remote connection" displays, due to a Microsoft environment issue.

Problem:

When using OData with a secure OpenText Core SDP server and Microsoft Excel (or other Microsoft applications, such as Power BI), this error appears. This is because, by default, the Microsoft application does not support TLS1.2.

Workaround: 

  1. Modify your registry using regedit.ext.

    Set the SchUseStrongCrypto DWORD value in the following two registry keys to 1: 

    • "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319"
    • "HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319"

    If these keys do not exist, create them.

  2. Restart the Microsoft application.

Back to top

See also: