1. Getting comments using Custom Report

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will be using a custom report with a custom ESQL section.

To create the report you need to:

  1. Go to Administration  > Edit Reports
  2. Click on Create a new Report (at the bottom of the list of existing Reports)
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

  5. Then click [Save] twice and you can run your report.

Paste into the Query section the query itself:

SELECT
C.CREATOR_NAME AS CREATOR, C.ARTIFACT_ID, C.ARTIFACT_TYPE_NAME as ARTIFACT, C.CREATION_DATE, C.COMMENT_TEXT as COMMENT 
 FROM
    SpiraTestEntities.R_Comments as C

Feel free to modify the query in order to filter the necessary data, for instance if you'd like to get comments made only for artifact type Requirement, then your query would be:

SELECT
C.CREATOR_NAME AS CREATOR, C.ARTIFACT_ID, C.ARTIFACT_TYPE_NAME as ARTIFACT, C.CREATION_DATE, C.COMMENT_TEXT as COMMENT 
 FROM     SpiraTestEntities.R_Comments as C
  WHERE C.ARTIFACT_TYPE_NAME = 'Requirement'

To filter the data by Creator, then use the following WHERE statement instead:

 WHERE C.CREATOR_NAME = 'Fred Bloggs'

Or use both filters to get the comments made by Fred Bloggs for Requirements only:

SELECT
C.CREATOR_NAME AS CREATOR, C.ARTIFACT_ID, C.ARTIFACT_TYPE_NAME as ARTIFACT, C.CREATION_DATE, C.COMMENT_TEXT as COMMENT 
 FROM     SpiraTestEntities.R_Comments as C
  WHERE C.ARTIFACT_TYPE_NAME = 'Requirement' AND C.CREATOR_NAME = 'Fred Bloggs'

Then click the 'Create Default Template' option to generate the following XSLT report template or use the generated XSLT template:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:template match="/RESULTS">
    <table class="DataGrid"><tr><th>CREATOR</th><th>ARTIFACT_ID</th><th>ARTIFACT</th><th>CREATION_DATE</th><th>COMMENT</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="CREATOR"/></td><td><xsl:value-of select="ARTIFACT_ID"/></td><td><xsl:value-of select="ARTIFACT"/></td><td><xsl:value-of select="CREATION_DATE"/></td><td><xsl:value-of select="COMMENT"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Considering the double-filter (creator and artifact) the final report will look like:

To make a summary report, grouped by the Comment Creator and artifact type please use this query:

SELECT
C.CREATOR_NAME AS CREATOR, C.ARTIFACT_TYPE_NAME as ARTIFACT_TYPE, COUNT(C.ARTIFACT_TYPE_NAME) as Count
 FROM
    SpiraTestEntities.R_Comments as C
GROUP BY C.ARTIFACT_TYPE_NAME, C.CREATOR_NAME

So the report will display the total count of artifacts that have a comments per comment creator user:

2. Pulling comments using Power Query:

How to connect the data source can be found here.

In the Navigator at left side of the window, find and check the RPT_COMMENTS view.
There immediately appears data containing in it on the right. 
If you'd like to modify the information first - click Transform or just Load the entire table into the tool (Excel or PowerBI Desktop):

In this example, we
- removed 1st and 3rd columns since they do not provide any significant information at this moment and
- reordered columns for better visual appearance:

Finally we'll get this table in PowerBI that can be easily filtered upon need and then published:

and make a brief table with number of artifacts with comments, grouped by comment creator: