Inflectra Customer Forums: FYI: how to create a report the outputs JavaScript object data (Thread)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. 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); } /* the aSTReleases array object has " + aSTReleases.length + " elements. Here it is, stringified: "+ JSON.stringify(aSTReleases,null,2) + " "); }); /*]]>*/ var aSTReleases=[ {'link':' ', "projectId":" ", "releaseId":" ", "indentLevel":" ", "name":" ", "description":" ", "owner":" ", "countOfReqs":" ", "startDateInMS":" ", "endDate":" "}, 0]; aSTReleases.pop(); Save the report then run it in the HTML format. null en-US(C) Copyright 2006-2024 Inflectra Corporation.support@inflectra.com/Computers/Software/Project_Management//Computers/Software/Quality_Assurance/KronoDesksupport@inflectra.comhttp://www.inflectra.com/kronodesk/forums/threads120/Support/Forum/spirateam/issues-questions/1005.aspxthreadId=1005Jon Freed (jfreed@edmap.com)report reporting xslt entity-sql sql html rest join jqueryFYI: how to create a report the outputs JavaScript object dataFYI, 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. 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); } /* the aSTReleases array object has " + aSTReleases.length + " elements. Here it is, stringified: "+ JSON.stringify(aSTReleases,null,2) + " "); }); /*]]>*/ var aSTReleases=[ {'link':' ', "projectId":" ", "releaseId":" ", "indentLevel":" ", "name":" ", "description":" ", "owner":" ", "countOfReqs":" ", "startDateInMS":" ", "endDate":" "}, 0]; aSTReleases.pop(); Save the report then run it in the HTML format. null Thu, 18 Sep 2014 14:31:56 -04002014-09-22T15:43:04-04:00/Support/Forum/spirateam/issues-questions/1005.aspxmessageId=1837Jim R (donotreply5@kronodesk.net) Thanks Jon. Thanks Jon. Mon, 22 Sep 2014 15:43:04 -04002014-09-22T15:43:04-04:00/Support/Forum/spirateam/issues-questions/1005.aspx#reply1837