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 in this article - How Entity ESQL Differs from Database SQL.

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: