Knowledge Base Article

Home Page > Knowledge Base > Spira Platform

Home Page > Knowledge Base > Spira Platform > SpiraPlan

Home Page > Knowledge Base > Spira Platform > SpiraTeam

Home Page > Knowledge Base > Spira Platform > SpiraTest

Article Writing Custom Entity SQL Reports in Spira

by Adam S on Tuesday, December 9, 2014

Our Spira platform (SpiraPlan, SpiraTest, SpiraTeam) has powerful custom reporting capabilities that let you build custom reports using the Microsoft Entity SQL language. This article provides some pointers on writing such reports.

Overview

The Spira report writer lets you build reports using:

  1. Standard sections (i.e. using an existing predefined set of data that comes with the system)
  2. Custom sections (using a SQL query of your own to retrieve the data)

The focus of this KB article is #2, using the custom sections. It assumes that you have already created a new report in Administration > System > Edit Reports and are looking for help on the Microsoft Entity SQL (aka ESQL) syntax.

There are many differences between ESQL and standard database SQL as explained: http://msdn.microsoft.com/en-us/library/vstudio/bb738573(v=vs.100).aspx

The Basics

When you create a new query in Spira, the system will basically create the equivalent of a SELECT * FROM View for the selected reportable entity. For example, if you chose requirements you would get the following:

select value R from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId}

the 'value R' is equivalent to SELECT * in database SQL.

To select just certain fields, you change this to:

select R.REQUIREMENT_ID, R.NAME from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId}

That will just select the name and ID fields.

You can also use 'as' to change the names of the columns returned (unlike database SQL they need to be identifiers not strings):

select R.REQUIREMENT_ID as RequirementId, R.NAME as Name from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId}

You can also join two tables together (e.g. join the test case coverage view):

select R.REQUIREMENT_ID as RequirementId, R.NAME as RequirementName, T.TEST_CASE_ID as TestCaseId, T.TEST_CASE_NAME as TestCaseName from SpiraTestEntities.R_Requirements as R join  SpiraTestEntities.R_RequirementTestCases as T on R.REQUIREMENT_ID =T.TEST_CASE_ID where R.PROJECT_ID = ${ProjectId}

Finally, if you want to do aggregations (such as grouping) that can be applied to the query as well:

select R.REQUIREMENT_ID as RequirementId, R.NAME as RequirementName, count(T.TEST_CASE_ID) as TestCaseId, count(T.TEST_CASE_NAME) as TestCaseName from SpiraTestEntities.R_Requirements as R join  SpiraTestEntities.R_RequirementTestCases as T on R.REQUIREMENT_ID =T.TEST_CASE_ID  where R.PROJECT_ID = ${ProjectId} group by R.REQUIREMENT_ID, R.NAME

In this simple example, we are displaying a list of requirements and counting the number of test cases associated with each one:

RequirementIdRequirementNameTestCaseIdTestCaseName
2Online Library Management System22
3Book Management22
4Ability to add new books to the system33
5Ability to edit existing books in the system11
6Ability to delete existing books in the system11
8Ability to associate books with different authors66
9Ability to associate books with different editions22
12Ability to create different editions33
13Author Management22
18Ability to associate authors with subjects11



Attachments
Article Info
Last Updated: 12/9/2014
Article ID: KB99
# Views: 1728
Powered by KronoDesk v1.1.0.15 | © Copyright Inflectra Corporation 2011-2016 | Licensed to Inflectra Corporation.