OData support for extended reporting

ValueEdge supports OData (Open Data Protocol), the OASIS REST-based standard for accessing data. You can use OData to extend and improve reporting capabilities, above and beyond the functionality provided by ValueEdge dashboard widgets. This topic provides instructions for working with OData and ValueEdge.

Overview

This overview describes the use cases for using the OData standard to access ValueEdge.

You can retrieve ValueEdge data over OData with reporting and business intelligence tools.

You can use these reporting and business intelligence tools that support OData (such as Power BI) to generate reports and charts, instead of relying only on ValueEdge dashboard widgets.

Connect to ValueEdge's OData support from the application's interface.

Back to top

Supported OData versions

Any tool that supports OData version is likely to work well with ValueEdge.

ValueEdge OData support has been tested with Power BI and Excel.

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.
  • When ValueEdge 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

The ValueEdge server base URI

The ValueEdge server base URI for accessing ValueEdge data using OData is: 

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

Troubleshooting: If ValueEdge does not respond successfully to an OData consumer request, it might be because the base URI used to refer to ValueEdge is different than expected. Consider modifying ODATA_USE_SERVER_BASE_URL and SERVER_BASE_URL as described under Configuration parameters.

Server base URI for accessing data from multiple workspaces

You can access ValueEdge's data from multiple workspaces that exist within a shared space by modifying the base URI.

The URI should include:

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.

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

Use case Details
Including data from all the 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/*

Including 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.

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

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, ValueEdge includes the LWSSO_COOKIE_KEY cookie in the response. We recommend that you send the LWSSO_COOKIE_KEY cookie with each subsequent OData request for enhanced performance. For details, see the information about the LWSSO_COOKIE_KEY under Authenticating.

Back to top

Access ValueEdge data from a reporting or BI tool

Any reporting or business intelligence tool with OData support can integrate with ValueEdge, 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, go to http://www.odata.org/ecosystem/ and select Consumers.

To troubleshooting 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 ValueEdge data using OData. It shows, step by step, how to create a traceability report.

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

    Set up a basic feed using the ValueEdge URI.

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

  2. Now we have connected and can see all ValueEdge entities. Let's start creating our report.

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

We now select the columns we want to see. Click Choose Columns.

For our report, let's select the ID, name, release, and tests columns.

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

Click the button for the release. We would like the name of the release, so we select name.

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

Let's filter for items in release 2.2.

Click Close & Apply. Here is the result so far.

Now create the graph with data we select.

From the right pane:

  • Select the values.

  • Select matrix visualizations.

  • Arrange the rows, columns and values.

We can also add the pie chart visualization.

Save and refresh the data.

Back to top

Troubleshooting

When trying to access ValueEdge 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 ValueEdge server and Microsoft Excel (or other Microsoft applications, such as PowerBI), this error appears. This is because, by default, the Microsoft application does not support TLS1.2, which ValueEdge expects.

Workaround

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.

Restart the Microsoft application.

Back to top

See also: