Skip Navigation LinksHome Page

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

Thursday, September 18, 2014

rest

report

jfreed

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, '\\"');

// 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">

<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>

<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

Monday, September 22, 2014

inflectra.jimx

re: jfreed Thursday, September 18, 2014

Thanks Jon.

Statistics

Started: Thursday, September 18, 2014
Last Reply: Monday, September 22, 2014
Replies: 1
Views: 13083

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

Edit Thread

Edit Message

Search Results