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.
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:
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:
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.
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:
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.
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.
Ask an Inflectra expert:
And if you have any questions, please email or call us at +1 (202) 558-6885
SpiraTest combines test management, requirements traceability & bug-tracking
SpiraTeam brings your teams together, managing the entire application lifecycle
SpiraPlan lets you manage your programs and portfolio of projects like never before
Orchestrates your automated regression testing, functional, load and performance
The ultimate test automation platform for web, mobile, and desktop applications
The help desk system, designed specifically for software support teams
Cloud hosted, secure source code management - Git and Subversion
Exploratory testing capture tool that automatically records your testing activity
Let us deal with the IT pain so you don't have to. Or use on-premise if you prefer.
Our customers work in every industry imaginable. From financial services to healthcare and biotech to government and defense and more, we work with our customers to address their specific needs.
Our products do not enforce a methodology on you, instead they let you work your way. Whether you work in agile development, Scrum, XP, Kanban and Lean, Waterfall, hybrid, or Scaled Agile Inflectra can help.
If you want to learn more about application delivery, testing, and more take a look at our whitepapers, videos, background papers, blog, and presentations.
Our suite of Accelerators speed up your deployment and adoption of our products, increasing your return on investment and reducing the cost of ownership.
We collaborate with a wide range of organizations to bring our customers a range of services (including load testing, training, and consulting), complimentary technologies, and specialized tools for specific industries.
Learn how different organizations have benefited from using Inflectra products to manage their software testing and application develooment.
Outstanding support is the foundation of our company. We make support a priority over all other work. Take a look at our support policy.
Discover great tips, discussions, and technical solutions from fellow customers and Inflectra's technical experts.
If you can't find the answer you're looking for, please get in touch with us: over email, phone, or online.
We are constantly creating new videos to help customers learn about our products, including through in depth webinars, all freely available along with a wide selection of presentations.
We provide a number of resources to help customers learn how to get the most out of our products, with free online resources, virtual classrooms, and face to face.
Read about Inflectra, our manifesto, and values. Meet our incredible customers who are building awesome things, and our leadership team that are committed to building a great company.
The Inflectra Blog contains articles on all aspects of the software lifecycle.
In addition we have whitepapers,
background articles, videos and
presentations to help get you started.
Events are a big part of our awesome customer service. They are a chance to learn more about us, our products, and how to level up your skills with our tools.
We partner with educational institutions and individuals all over the world. We are also a great place to work and encourage you to explore joining our team.
Please contact us with your questions, feedback, comments, or suggestions. We'll get back to you as soon as possible.
When you need additional assistance (be it training, consulting, or integration services) our global certified solution provider partner network is ready to help.
At Inflectra, we are fully committed to provide our customers with the very best products and customer service. Check out some of our recent awards.
We want to help developers extend and customize our tools to fit in with their needs. We provide robust APIs, sample code, and open source projects.