Create a report:

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will build a custom report with 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 HTML format (the dynamic elements won't work in Word/PDF/Excel)
  4. Choose to add a Custom Section:

SQL Query:

5. Click Add New Custom Section and then
6. Copy and paste the below simple query to pull some incident information into the Query section of the window displayed:

SELECT
    I.INCIDENT_ID,
    I.NAME AS IncidentName,
    I.INCIDENT_STATUS_NAME,
    I.INCIDENT_TYPE_NAME,
    I.PRIORITY_NAME
FROM
    SpiraTestEntities.R_Incidents AS I

7. You can click Preview Results button to see the sample of the results.

8. If you will click on Generate Default Template button, you will get a normal report as a table of data. It will be a five-column table with incident info: IncidentID, Incident Name, its status, type and priority and that will also work for reporting purposes after saving changes.
 

Default template modification:

9. If you replace default template with the one from below, you will get the standard output plus the Export to Excel button on the top of the table:

<?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">
    <html lang="en">
      <head>
        <meta charset="UTF-8"/>
        <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
      </head>
      <body class="p-6">
        <div>
          <div>
            <button onclick="exportToExcel()">
              Export to Excel
            </button>
          </div>
          <table class="DataGrid" id="incident-table">
            <tr>
              <th>INCIDENT_ID</th>
              <th>IncidentName</th>
              <th>INCIDENT_STATUS_NAME</th>
              <th>INCIDENT_TYPE_NAME</th>
              <th>PRIORITY_NAME</th>
            </tr>
            <xsl:for-each select="ROW">
              <tr><td><xsl:value-of select="INCIDENT_ID"/></td>
                <td><xsl:value-of select="IncidentName"/></td>
                <td><xsl:value-of select="INCIDENT_STATUS_NAME"/></td>
                <td><xsl:value-of select="INCIDENT_TYPE_NAME"/></td>
                <td><xsl:value-of select="PRIORITY_NAME"/></td></tr>
            </xsl:for-each>
          </table>
          <script>
            function exportToExcel() {
              const table = document.getElementById('incident-table');
              const htmlContent = table.outerHTML;
              const styleContent = document.querySelector('style').innerHTML;
              const fullHtml = `
                <html xmlns:o="urn:schemas-microsoft-com:office:office"
                      xmlns:x="urn:schemas-microsoft-com:office:excel"
                      xmlns="http://www.w3.org/TR/REC-html40">
                <head>
                  <meta charset="UTF-8">
                  <meta name="viewport" content="width=device-width, initial-scale=1.0">
                  <style>${styleContent}</style>
              </meta>
            </meta>
                </head>
                <body>
                  ${htmlContent}
                </body>
                </html>
              `;
              const blob = new Blob([fullHtml], { type: 'application/vnd.ms-excel' });
              const url = URL.createObjectURL(blob);
              const a = document.createElement('a');
              a.href = url;
              a.download = 'incident_report.xls';
              document.body.appendChild(a);
              a.click();
              document.body.removeChild(a);
              URL.revokeObjectURL(url);
            }
          </script>
        </div>
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

Modification explanations:

Export-to-Excel Function is the most dynamic part of the code and where the JavaScript comes in. It provides a simple, client-side solution for saving the report.

  • exportToExcel() Function: This function is triggered by the button's onclick event. It's a clever hack that works by generating a new HTML document formatted specifically for a spreadsheet application like Excel.

  • Creating the HTML: The script gets the HTML content of the table (table.outerHTML) and the styles from the <style> tag. It then combines them into a full HTML string with special XML namespaces (xmlns:o and xmlns:x) that tells Excel to interpret the content as a spreadsheet.

  • Triggering the Download: Finally, the script creates a Blob object from the generated HTML string. It then uses a temporary <a> element to create a downloadable link. This process tricks the browser into downloading the file with an .xls extension, allowing Excel to open it.

Sample output:

9. After that, the report is now ready for saving and running. Give the report a meaningful name and save it for further use.
10. Go to the product's report center and locate the newly created report. Click Generate and wait for the page to load. You should get something similar to this:

11. When you click on Export to Excel button the entire table will be exported as .xsl file and available for further use.