Guide To Custom Spira Data Extraction & Reports

July 1st, 2021 by inflectra

custom reporting reporting data extract data export

One of the pivotal aspects of any data captured in a system is the ability to report on such data. The application lifecycle management (ALM) tool is undoubtedly one such system where a combination of manual and automated data entries captures the transition of needs into requirements, test cases, and incidents supported by auditable comments, governance workflows of various artifacts, and the status of manual and automated execution. All these activities translate the strategic product roadmap into operational backlogs for release and iteration planning.
The Spira family of products, such as SpiraTest, SpiraTeam, and SpiraPlan, also support these needs at various levels. It should be emphasized that most of the standard reporting available from Spira and the widgets available in the project and reporting dashboard meets the majority of the clients’ needs across all the industries. Yet, a few clients may have some requirements that can be better served by modifications to existing reports as well as developing their own custom reports. Inflectra offers its implementation services to support these needs. However, the Spira product also provides support for enabling clients to develop some of these reports themselves. In this blog, we will review how to address this need.

(by Dr. Sriram Rajagopalan)

First Things First

Spira provides rich, context-sensitive document help. This help system can be accessed by clicking on the “Documentation” under the user’s profile menu in the top navigation bar (Fig 1). Therefore, readers are advised to seek this help if there is any question on the specific areas of the user interface.

Figure 1: Contextual Help Documentation Location

While modifying existing reports, users are advised to ‘clone’ the report (Fig 2) and work on the clone. This is one of the best practices strongly recommended to avoid any challenges in reverting to the original report.

Figure 2: Cloning Reports as part of the Best Practice

Approach 1: Modifying the Standard Reports

One of the first and easiest reporting needs for customers is to modify the existing standard reports. These requests are removing a few existing data, modify the order in which they appear or change the title of the field, or add additional data elements that are also captured. These apply to both long-form reports or tabular reports. The long-form text-based reports capture a lot of information from multiple artifacts over a period of time. These reports are rendered in HTML, Word, or PDF formats. The tabular reports report data frequently from a set of specific artifacts. These tabular reports are rendered in HTML or Excel format.

To modify the existing standard section of the report, you would need to edit the XSLT section of the specific standard section. You can accomplish this by doing the following steps.

Access the Edit Report from the Administration Menu.
Clone the specific report you want to modify.
Click on the Edit button for the new report.
Click on the “Customize” button of the specific standard section (Fig 3) you want to edit as shown below.

Figure 3: Location of the Standard Section Customization

In the pop-up dialog, edit the “template” section with the changes that you want to see in the report. For instance, let us say that you want to get rid of the columns such as the automation engine, component, and duration on the tests. You will have to delete these relevant columns in the XSLT where the columns are defined as part of the table header (Fig 4) as well as in the logic for the data columns (Fig 5). Shown below is the removal of these columns.

Figure 4: Location of Template showing the three columns to be deleted in the table header (TH tag)

Figure 5: Deleting the corresponding Data Columns (TD Tags) shown in Notepad++

You can use this method to add a new standard section and use one of Spira’s prebuilt datasets (Fig 6).
Save the sections and the report.

Figure 7: List of available Standard Sections

Benefits and Challenges of the Standard Section Modification

The benefit of this approach is that the dataset required for reporting is already prebuilt by Inflectra for you to use any of the data. It is easy to add new columns, remove columns, or even use XSLT programming to add your own customization to the dataset. However, the challenge is that one is to the dataset already pre-built and it requires the knowledge of the XSLT programming language. While working on complex reports with nested loops and additional conditional logic, it is possible to make some logical errors requiring you to spend time addressing these errors introduced.

Approach 2: Using Custom Section

This approach addresses the challenge associated with the earlier approach of a pre-built datasets and prior knowledge of XSLT programming. If one has the power to directly access the dataset and can work through what data is required, then, you can address some of these challenges. One powerful data access language is the Structured Query Language (SQL, pronounced Sequel). The Spira family of products supports the Entity SQL (commonly known as E-SQL) that can be used to directly access the data stored.

To include your own data using E-SQL in the custom section, you would need to develop the E-SQL query first and then create the XSLT transformation required to render the report. You can follow the steps below to develop this custom section.

Access the Edit Report from the Administration Menu
Clone the specific report you want to modify
Click on the Edit button for the new report
Delete standard sections you do not want (some prefer to keep project information)
Click on the “Add New Custom Section” (Fig 8)

NOTE: If you already have a custom section that you want to modify, then, click on the “Edit” button next to that section under the operations column)

Figure 8: Adding a Custom Section

Give a name to the custom section (Fig 9). Updated other areas like description, header, and footer as necessary.

Figure 9: Naming the Custom Section

Select one of the datasets available for you to query as shown. Note that every time you select a dataset, a default query is added to the rich text field called “Query”. Make sure only one query is available in the “Query Window”.

Figure 10: Showing available datasets for a Custom Section

For example, when the “Incidents” query is selected from the drop-down, the Query window will appear as follows (Fig 11). You can click on the “Preview Results” to see the name of the attributes (columns) that describe the dataset (entity). This query means selecting all the attributes from the SpiraTestEntities.R_Incidents where the primary key (project_id) is the id of the project selected in the workspace section.

Figure 11: Showing E-SQL Query for the Incident Query

Now, identify specific fields from this entity that are required to be reported. For example, if only the incident_id, project_id, priority_id, incident_status_id are required, the query will look like the following as shown below. Make sure that the query is written in the “Query” window (Fig 12)

select R.INCIDENT_ID, R.PROJECT_ID, R.PRIORITY_ID, R.INCIDENT_STATUS_ID from SpiraTestEntities.R_Incidents as R where R.PROJECT_ID = ${ProjectId}

Figure 12: Modified E-SQL Query showing specific data of Incident artifact

Click on Preview Results to ensure data looks as you expected
Save the section and report

Benefits and Challenges of the Custom Section

The benefit of this second approach is that you have the power over the dataset required for reporting. Using the power of E-SQL, you can join with multiple datasets, compute new data based on the existing system and data attributes, and build robust queries. However, the challenge is that one needs to be somewhat knowledgeable about the E-SQL syntax and spend time on the database schema surrounding the Spira-specific datasets. Those that are familiar with the E-SQL can find this alternative attractive with the limitation that SQL is a set-specific language amenable more to the tabular type of reporting rather than the long-form reporting.

Approach 3: OData Access

While these two approaches solve the majority of the reporting requirements, Inflectra also makes available a third approach to address some of the challenges with the E-SQL knowledge requirements of the custom section approach. This approach requires the use of the higher edition of SpiraPlan and is not available in the SpiraTest and SpiraTeam editions. In this third approach, data is accessed directly through Microsoft Excel using the Open Data Protocol, called OData.

By connecting Microsoft Excel with the OData services exposed by SpiraPlan, data available from Spira can be queried directly from Microsoft Excel using Power Query editor. Inflectra’s blog article on OData access provides a detailed overview of how to establish the connection and perform simple and complicated queries using the OData services. Please review this blog article for more details.

Summary

The request for reporting services is a continuously growing need. While Inflectra recognizes this need and provides numerous customer-friendly out-of-the-box reports, Inflectra also recognizes the need for customizing reports. To support customers to develop these reports themselves, Inflectra makes several options, such as reporting through XSLT modifications, custom E-SQL, and OData services. Inflectra’s support center also provides a lot of articles on these custom reporting needs to extend continuous support.