Thread

Skip Navigation LinksForums > SpiraTeam Forums > SpiraTeam Issues & Questi... > FYI: how to create a repo...

FYI: how to create a report the outputs JavaScript object data RSS Feed

Thursday, September 18, 2014
FYI, here is how to create a report that puts data into a JavaScript object.  (It uses the out-of-the-box "HTML" report format.)

Why?  Well, if you have data in a JavaScript object it is then easier to use that data with things that like to have JSON or a JavaScript object as input.  These things might include a REST interface to another system, fancy JavaScript-based reporting engine, or something else.  Perhaps you just want to customize the report output and would rather do it in JavaScript than in XSLT.

Why not just use the existing Spira REST api to get the data in a JavaScript object?  You could, but not all objects are available through the published api.  Also, you might just find it easier and faster to get all your data in one report rather than through multiple calls to the Spira REST api.

I've thrown in some extras into this example.
  • How to join tables in Entity-SQL
  • How to use a Entity-SQL query as a table (and join to it)
  • How to cast a number to a string in Entity SQL (because we are storing release owner in the custom field CUST_01 field because release owner isn't in the out-of-the-box data model) 
  • How to run an msxsl script during the server-side XSLT processing
  • How to include jQuery in the report output
  • How to create a string in the javascript object that includes an "a" hyperlink object.
Enjoy.  
DISCLAIMER:  This is released to the SpiraTeam community free of charge, but you use it at your own risk.  Work at a college or university or know people who do?  Contact ED MAP to learn how we get great course content into the students' hands, lower the cost of education, and improve outcomes.

STEPS:
  • Create a new report.  Administration > System > Edit Reports, Add New Report.  Name it whatever you want.  Category "Release Reports" makes sense for this example.
  • Add New Custom Section
  • Here is example Entity-SQL for retrieving release data
SELECT r.PROJECT_ID, r.RELEASE_ID 
, r.INDENT_LEVEL
, r.NAME, r.DESCRIPTION
, u.USER_NAME AS OWNER
, r.START_DATE
, r.END_DATE
, sq.COUNT_OF_REQS
FROM SpiraTestEntities.R_Releases AS r
LEFT JOIN SpiraTestEntities.R_Users AS u on r.CUST_01 = cast(u.USER_ID AS String)
LEFT JOIN (SELECT q.RELEASE_ID AS RELEASE_ID, count(q.REQUIREMENT_ID) AS COUNT_OF_REQS 
      FROM SpiraTestEntities.R_Requirements AS q 
      WHERE q.IS_DELETED = false 
      GROUP BY q.RELEASE_ID) AS sq ON r.RELEASE_ID = sq.RELEASE_ID
WHERE r.PROJECT_ID = ${ProjectId} 
AND r.IS_DELETED = false
AND r.ACTIVE_YN = 'Y'
ORDER BY r.INDENT_LEVEL
  • Here is example XSLT for creating the HTML output.
<?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" xmlns:edmap="http://www.edmap.com" exclude-result-prefixes="msxsl">
  <msxsl:script language="JScript" implements-prefix="edmap">
    function escape(str) {
      // Escape characters that may cause JavaScript compile errors.
      // The selected chars are based on the list at http://json.org
      // The chars do not include all possibly problematic characters such as \ufour-hex-digits.
      // Note that default server-side scripting language is JScript.  Language reference at http://msdn.microsoft.com/en-us/library/x85xxsf4(v=vs.90).aspx
      str = str.replace(/^(\s*)([\W\w]*)(\b\s*$)/,'$2'); // this is supposed to trim the string; unlike JavaScript, JSCript doesn't have a trim function
      str = str.replace(/\\/g,'\\\\');
      str = str.replace(/\//g, '\\/');
      str = str.replace(/"/g, '\\&quot;');
//    str = str.replace(/\b/g,'\\b'); // This ends up putting \b all over the place so it is commented out.  We probably won't see this character anyways.
      str = str.replace(/\f/g,'\\f');
      str = str.replace(/\n/g,'\\n');
      str = str.replace(/\r/g,'\\r');
      return str.replace(/\t/g,'\\t');
    }
    function getTime(str) {
      return Date.parse(str);
    }
  </msxsl:script>
  <xsl:template match="/RESULTS">
    <script src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
    <script>
      <xsl:comment>/*<![CDATA[*/   
        // wrapping this script content in the xsl:comment and CDATA tags helps preclude the XSLT processor from erroring-out on valid JavaScript, like &&

        $(document).ready(function(){
          $("body").append("<div> the aSTReleases array object has " + aSTReleases.length + " elements.  Here it is, stringified:</div><pre>"+ JSON.stringify(aSTReleases,null,2) + "</pre>");
        });

      /*]]>*/</xsl:comment>
    </script>
    <script>
      <xsl:text>var aSTReleases=[</xsl:text>
      <xsl:for-each select="ROW">
        <xsl:text>{'link':'</xsl:text><a href="/{PROJECT_ID}/Release/{RELEASE_ID}.aspx"><xsl:number/></a><xsl:text>',</xsl:text>
        <xsl:text>"projectId":"</xsl:text><xsl:value-of select="PROJECT_ID"/><xsl:text>",</xsl:text>
        <xsl:text>"releaseId":"</xsl:text><xsl:value-of select="RELEASE_ID"/><xsl:text>",</xsl:text>
        <xsl:text>"indentLevel":"</xsl:text><xsl:value-of select="INDENT_LEVEL"/><xsl:text>",</xsl:text>
        <xsl:text>"name":"</xsl:text><xsl:value-of select="edmap:escape(string(NAME))"/><xsl:text>",</xsl:text>
        <xsl:text>"description":"</xsl:text><xsl:value-of select="edmap:escape(string(DESCRIPTION))" disable-output-escaping="yes"/><xsl:text>",</xsl:text>
        <xsl:text>"owner":"</xsl:text><xsl:value-of select="OWNER"/><xsl:text>",</xsl:text>
        <xsl:text>"countOfReqs":"</xsl:text><xsl:value-of select="COUNT_OF_REQS"/><xsl:text>",</xsl:text>
        <xsl:text>"startDateInMS":"</xsl:text><xsl:value-of select="edmap:getTime(msxsl:format-date(START_DATE,'MMM d, yyyy'))" disable-output-escaping="yes"/><xsl:text>",</xsl:text>
        <xsl:text>"endDate":"</xsl:text><xsl:value-of select="msxsl:format-date(END_DATE,'yyyy-MM-dd')" disable-output-escaping="yes"/><xsl:text>"},</xsl:text>
      </xsl:for-each>
      <xsl:text>0]; aSTReleases.pop();</xsl:text>
    </script>
  </xsl:template>
</xsl:stylesheet>
  • Save the report then run it in the HTML format.
null
1 Replies
Jim R.Jim R.
re: Jon Freed on Thursday, September 18, 2014
Monday, September 22, 2014
Thanks Jon.
Statistics
  • Started: 9/18/2014
  • Last Reply: 9/22/2014
  • Replies: 1
  • Views: 1158
Powered by KronoDesk v1.1.0.15 | © Copyright Inflectra Corporation 2011-2016 | Licensed to Inflectra Corporation.