Spotlight on Spira 6.9 - OData Reporting API

April 26th, 2021 by inflectra

One of the old adages about reporting is that however many reports you create, you will always need one more! With that in mind, Spira has for many years had a powerful and flexible reporting engine built-in that lets you create custom reports and custom graphs using the Entity SQL (ESQL) query language. However users have asked us if there was a way to use their favorite reporting tool (e.g. Excel or Microsoft PowerBI) to connect to Spira to create reports. With the latest version of SpiraPlan that is now possible, for both cloud and on-premise customers using our new OData API.

Introduction

OData is an open protocol that lets you easily query data, over the web. Exclusive to SpiraPlan (6.9+), with OData you can directly query the raw data in your database in a secure and safe way. Whenever you use OData in SpiraPlan you are communicating through a secure intermediary (the application itself) to get data from read-only reporting views. Tools like Excel, PowerBI, Tableau support OData and can therefore communicate with SpiraPlan to access this data with just a few clicks.

With OData you don't need to be a SQL expert to generate rich and dynamic insights into your data. If you can fiddle with a spreadsheet, you can stich tables of data from SpiraPlan ("joins" in database language) to get just the data you need. What sort of insights can you get with OData and SpiraPlan reporting views? Here are some examples:

a pie chart of how many users are members of each of SpiraPlan's products
a list of how long ago each open task was assigned to the current owner
get the most recent test run for each test case against each requirement assigned to a sprint
the top 5 most closed then reopened bugs in a product (or program)

Connecting Excel to SpiraPlan using OData

In this article we will be using Excel to illustrate how you can use OData, but in reality you can use any reporting tool that supports OData, for example Microsoft PowerBI.

Excel comes with a built-in Power Query tool that can connect to OData APIs such as SpiraPlan 6.9:

Once Excel connects to SpiraPln you see a popup "Navigator" where you can see all the different data views you can access ("query"). There is a lot here and a lot to explore. You can access pretty much all the information in your application, across all its products and templates, from these views:

To see all the data you have two options:

Load: this loads the whole view, with all records, into a new Excel sheet in the current workbook
Transform Data: this puts the data into Excel's Power Query so that you can manipulate the data that Spira sends to Excel

If you choose the option to load the data, you will see the following:

With this approach, you can connect Excel and Spira together using OData and view data from Spira live in Excel.

Transforming & Querying Data using OData

In addition to loading entire views, the OData protocol allows dynamic querying of the live data source:

In this example, we might have two different queries that are completely independent from each other. If we want to connect them together, we can use OData's query capabilities. For example, imagine for each incident that we want to get extra information about its status. The main query is Incidents, and the secondary query is IncidentStatuses. With OData we can join these together to get a merged set of data:

In addition to this simple example you can use OData to perform common query needs:

Joining different views/tables of data together
Grouping and aggregating data
Sorting and filtering data

The OData query API allows you to do anything with the data you want. For instance, you can turn the data results into a pivot table to tell you how many open bugs and enhancements there are, in total, in each portfolio. The power of OData and the ease with which you can interrogate your data and draw out insights from it makes the new API a game-changer when it comes to enterprise reporting with SpiraPlan. You can create much more complex data that we have illustrated here, or use more complex reporting tools to create live data dashboards that let you extend SpiraPlan with customized queries that make sense to your organization.

How Do I Access the OData API?

If you like what you have seen here then the next step will be to upgrade to SpiraPlan 6.9 (once it's released in May 2021) and then follow the steps in the detailed OData tutorial that we will be publishing in SpiraDocs.

If you are running SpiraTest or SpiraTeam, then you will need to upgrade to SpiraPlan to access this functionality, since it's only available in the SpiraPlan edition of our Spira platform.