Inflectra Customer Forums: Spira Custom Reports

Custom Reporting Resources

David J (adam.sandman+support@inflectra.com) — Thu, 10 Nov 2016 15:06:35 -0500

We have had a request by Dave F (thanks Dave!) to create a new forum where users can share custom reports that they have found useful. This forum will be a repository for these items. As a helper, here are some useful links when writing custom reports: Blog Series on Writing Custom Reports (we recommend that you read this first) Article on using the Entity SQL (ESQL) syntax for writing custom reports Search for all KB articles on custom reports - lots of examples for different versions of Spira.

Report Displaying Projects, Project Groups and Releases

David J (adam.sandman+support@inflectra.com) — Fri, 21 Jul 2017 15:28:34 -0400

We had a request from a customer looking for a custom report with the following information: We need a report or exported data dump from the system having following data. it seems there is no such default report for the same. Please share the detail where these data points stored in the DB. that will help us to write a query to export the dump. 1. Project Group 2. Project Name 3. Release name 4. Release workflow status.

Exporting Incident Change History to Excel

David J (adam.sandman+support@inflectra.com) — Sat, 26 Aug 2017 07:43:52 -0400

We had the following request from a customer: Id like to export the Incident Change History to Excel for reporting purposes. In particular Im interested in seeing the Status Changes of all incidents in my project. How can I implement such a report?

Custom report of test cases linked to incidents

David J (adam.sandman+support@inflectra.com) — Wed, 11 Oct 2017 03:52:30 -0400

A customer asked how to write a custom Entity SQL report that displayed: Incident ID Incident Status Name Test Case ID Test Case Name

Custom Report Query to Find Tasks Linked to Deleted Requirements

David J (adam.sandman+support@inflectra.com) — Tue, 21 Nov 2017 20:39:38 -0500

We had a customer that was looking for a custom report to find all of the Tasks in SpiraTeam that are linked to deleted requirements. These are requirements that are soft-deleted (i.e. have not been purged and can therefore be undeleted). The ESQL query to use is: select T.TASK_ID, T.NAME as TASK_NAME from SpiraTestEntities.R_Tasks as T join SpiraTestEntities.R_Requirements as R on T.REQUIREMENT_ID = R.REQUIREMENT_ID where T.PROJECT_ID = ${ProjectId} and R.IS_DELETED = True

Custom Requirements Traceability Report

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Tue, 28 Nov 2017 18:45:07 -0500

A customer wanted a RTM report that looped per row for each requirement, rather than having a set of requirements IDs concatenated with commas: his section displays a list of all the requirements with the associated test cases/requirements. Req # Name Type Status Release # Test Traceability Requirements Traceability RQ4 Ability to add new books to the system Feature Developed 1.0.0.0.0001 TC2,TC13,TC8 RQ6 RQ4 Ability to add new books to the system Feature Developed 1.0.0.0.0001 TC2,TC13,TC8 RQ30 RQ5 Ability to edit existing books in the system Feature Tested 1.0.0.0.0001 TC3,TC8 RQ57 RQ5 Ability to edit existing books in the system Feature Tested 1.0.0.0.0001 TC3,TC8 RQ7 RQ5 Ability to edit existing books in the system Feature Tested 1.0.0.0.0001 TC3,TC8 RQ31 RQ6 Ability to delete existing books in the system Feature Tested 1.0.0.0.0002 TC8,TC12 RQ4 RQ7 Ability to associate books with different subjects Feature Developed 1.1.0.0.0001 TC4,TC8 RQ5 RQ57 Ability to report on book editions Feature Accepted TC4,TC2,TC5 RQ5 RQ30 Creating a new book in the system Use Case Completed TC2 RQ4 RQ31 Editing an existing book in the system Use Case Completed TC3 RQ5

Writing a Report to Show the Electronic Signatures in SpiraTeam

David J (adam.sandman+support@inflectra.com) — Fri, 15 Dec 2017 21:40:02 -0500

In SpiraTeam v5.0 we added native support for electronic / digital signatures . This was to allow our customers in regulated industries such as healthcare and finance be able to ensure that workflow changes were only performed by the appropriate personnel and that there was a signed audit trail of the change. A customer asked us how to write a custom report to display the electronic signatures in a project.

Changing the Test Case Detailed Report to show only latest test run

Clark R (simon.bor@inflectra.com) — Thu, 19 Apr 2018 15:06:42 -0400

We had a request from a customer to change the built-in test case detailed report: The Test Case Detailed report is very close to what we need, BUT it shows multiple test runs for a single test case. When a test run results in a failure or a caution, even though there is a retest shown immediately after it, thats not acceptable to the reviewing folks. So in this post we include the XSLT template changes needed to accomplish this.

Report on the Document Type used by Test Case Automation Scripts

David J (adam.sandman+support@inflectra.com) — Thu, 09 Aug 2018 01:46:08 -0400

A customer asked: I need to create a custom graph that shows the amount of test cases per document type. However when I look at the columns that are returned when I query the SpiraTestEntities.R_TestCases table (from within the Edit Graphs screen), I dont see any columns that represents the Document Type field. Which table should I query in order to find the Document Type value for a test case?

Test Step Custom Property on Reports

David J (adam.sandman+support@inflectra.com) — Mon, 19 Aug 2019 12:28:38 -0400

We added a Custom Property (List) to the Test Step for one of our projects. When I run the Test Run Detailed Report and view the output as XML, I can see it is not returning any values for it. I get this instead: System.Collections.Generic.Dictionary`2[System.String,System.Reflection.PropertyInfo] Will I need to use a Custom Report to get to the Custom Properties list values?

How to generate a report for test cases which have been run more

Jim R (donotreply5@kronodesk.net) — Tue, 12 Mar 2019 08:47:19 -0400

How to generate a report for test cases which have been run more than once. For example: If I have failed a test case and once defect is fixed ,i have re-run it. So how I am going to prepare report which only displays test cases which have been run once

Report for Test Cases which are executed twice(incase of Failed)

Jim R (donotreply5@kronodesk.net) — Fri, 07 Jun 2019 12:49:49 -0400

How can we generate a Report for Test Cases which are executed twice(incase of Failed).

Knowledge Base 35 - list of all reportable tables

Jim R (donotreply5@kronodesk.net) — Mon, 05 Dec 2016 15:27:04 -0500

Hi Can I have a list of the names of the fields in the User file, please? BR, Lene, Tryg Insurance

Sigh. I messed up one of my reports ..

David J (adam.sandman+support@inflectra.com) — Thu, 08 Dec 2016 16:07:56 -0500

Hi Can I have a list of the names of the fields in the User file, please? BR, Lene, Tryg Insurance

How to add summary to top of report

Jim R (donotreply5@kronodesk.net) — Fri, 23 Mar 2018 18:27:45 -0400

I would like to include a summary section at the top of the task report. While I can manually update the values in the spreadsheet after I run the report. I would prefer this is dynamically done when the report is generated. I want it to look something like: Tasks Not Started: 25 % Not Started: 25% Tasks In Progress :20 % In Progress: 15% Tasks Completed: 10 % Completed: 5% I found a reference to a test status report that did something similar. The formatting of that report is listed below. I am not sure where the path references come from and I cant seem to update it to reference tasks instead of test sets. Any ideas?

Applying business hours to custom reports

Ilia Poliakov (ilya.polyakov@edetek.com) — Fri, 23 Mar 2018 18:49:03 -0400

I have created a custom report to display all overdue tasks, and the number of days by which each task is overdue. However, I have only found a way to calculate that number using all days since the overdue date (ex. diffDays(CurrentDateTime(), R.End_Date) . In other words that calculation includes weekends. So if a task is one week overdue, I would like the report to indicate that the task is 5 days overdue, not 7 days. Does anyone know of a way to exclude non-business days from such a calculation?

migration from Kronodesk 1.1 to 2.0

Jim R (donotreply5@kronodesk.net) — Wed, 30 Aug 2017 09:52:08 -0400

Hello I use the version 1.1 of Kronodesk and i have some questions before installing the version 2.0 which is now available. First of all, if i upgrade my Kronodesk in version 2.0, is the database used in version 1.1 compatible with the version 2.0 or must i restart with a empty database (and i will lost all my history of help tickets and knowledge database) ? In the version 1.1 i cant known precisely the time for resolution of a ticket; i would calulate it in hour and not in day. Is it possible with the version 2.0 ? In my actual version i use many SQL script to do some reports; is the structure of the database similary in version 1.1 and 2.0 (does my SQL script will always run ?) ? I use custom lists in version 1.1; is it compatible with version 2.0 ? Thanks for your answers

Creating a custom report for Incident detection

David J (adam.sandman+support@inflectra.com) — Tue, 12 Apr 2022 08:57:20 -0400

Hi can anyone help in creating a custom report to show which release an incident was detected. I am trying to show the spread pattern to see when we are identifying incidents to see if our new quality measures are improving earlier detection times I saw a custom report that show an incident count by project, but i do not have the expertise to add releases to the list indented underneath and show a count by detected release Count of Incidents By Project The following Entity SQL command can be used in the Spira custom report (custom section) or Spira custom graph administration section to generate a graph of incidents by project: select INC.PROJECT_NAME, count(INC.INCIDENT_ID) as INCIDENT_COUNT from SpiraTestEntities.R_Incidents as INC where INC.IS_DELETED = False group by INC.PROJECT_NAME order by INC.PROJECT_NAME That gives the following data grid: PROJECT_NAMEINCIDENT_COUNT Library Information System63 Sample Application One1 Can anyone help?

Printable Test Script on Test Set

Petr Havlíček (Havlicek@komercpoj.cz) — Tue, 11 Apr 2017 11:09:12 -0400

Hi, I will print printable test script on the one of Test Set. Can I use some of templates from standard section or I must write SQL script to custom section? Thanks Petr

Create word test script from test set

martin alavi (malavi@visionrt.com) — Thu, 17 Nov 2016 10:23:03 -0500

Hi all, We regularly have to print out our test cases into a word document. We usually have to have one word document per test set. At the moment we are entering all of the test cases ids and using a variant of the printable test scripts. Does anybody know if we can do the same by just entering the test set id instead of manually adding all the test ids? I have tried the test set detailed report but even though i select test steps to include, the steps are not included. Does anybody have a custom report for this? thanks, Martin

Adding a custom properties field to a report

Danny Vanderbyl (dvanderbyl@gmail.com) — Wed, 05 Apr 2017 18:23:37 -0400

We run a test run list report and I would like to add a custom field that exist on the test case page. The custom field is a user list. I would really just like to add the column to the standard section test run list section so I dont have to recreate the whole report for adding one custom column. Any ideas??

Requirements Summary Report

Jim R (donotreply5@kronodesk.net) — Thu, 15 Mar 2018 18:34:09 -0400

The Requirements Summary Report does not show the Comments. Any help to get a report that pulls the comments within the Summary report.

Custom report scripts. Swap them with your forum friends

David J (adam.sandman+support@inflectra.com) — Wed, 09 Nov 2016 06:50:01 -0500

Hi all, Until Infectra create something like a report script trading area, I suggest we use this post to share our custom report scripts. Please provide both: The query/script code in a format others can cut and paste into their Spira Test The version of SpiraTest you are running A sample report to show others the results that your script will produce (i.e. a sample that shows a hew rows of your report, or attach a file)

Very simple traceability matrix - Requirement to Test case report

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Sun, 13 Nov 2016 22:45:58 -0500

The attached it useful for reporting on test case coverage. I wanted this to be simple and clean. I hope some of you find this useful. Spira ver - SpiraTeam v 5.0.0 . 8 Report sample Requirement / Test Case Traceability Report REQUIREMENT_ID REQUIREMENT_NAME TEST_CASE_ID TEST_CASE_NAME 104 3.14 RG Reporting 2600 Reports can be accessed remotely and are filterable 104 3.14 RG Reporting 2601 VIP Room Access report 104 3.14 RG Reporting 2602 Player Status Report 104 3.14 RG Reporting 2603 Player Status Change History 104 3.14 RG Reporting 2604 RG Incidents by member 103 3.13 RG Management User Matrix 2605 WPH RG Manager/Staff, member home venue is WPH 103 3.13 RG Management User Matrix 2606 WPH RG Manager/Staff, member home venue is CCT 103 3.13 RG Management User Matrix 2607 CCT RG Manager/Staff, member home venue is CCT 103 3.13 RG Management User Matrix 2608 CCT RG Manager/Staff, member home venue is WPH Query select R.REQUIREMENT_ID, R.REQUIREMENT_NAME, R.TEST_CASE_ID, R.TEST_CASE_NAME from SpiraTestEntities. R_RequirementTestCases as R where R.PROJECT_ID = ${ProjectId} Template REQUIREMENT_ID REQUIREMENT_NAME TEST_CASE_ID TEST_CASE_NAME

All tests with test execution Status and Actual results fields

Charles Sparks (williamvang046@hotmail.com) — Sun, 13 Nov 2016 23:00:19 -0500

This report produces a single line for every test case executed with the Status(e.g. Passed, Failed) and the Actual result as typed in my the tester. This is useful if the testers put the defect ID# against failures and blockers. ver SpiraTeam v 5.0.0 . 8 Report Sample Test Case ID Test Case Name Status Date Executed Actual Result 1391 eReport - sanity checks Failed 13/09/2016 236 1391 eReport - sanity checks Failed 9/09/2016 259 1395 ePromotions - sanity checks Passed 20/09/2016 1395 ePromotions - sanity checks Passed 13/09/2016 1395 ePromotions - sanity checks Passed 9/09/2016 1565 #18 - Member Export Publisher - 6 Edit a member by adding a comma Caution 19/09/2016 Exported with Comma and Quotes 1574 #18 - Member Export Publisher - 15 Edit member and with an invalid gender Caution 19/09/2016 The member has a gender set as A (truncated from ABCDEF) , this may cause issues when importing data 2601 VIP Room Access report Blocked 21/09/2016 289 2602 Player Status Report Blocked 21/09/2016 289 2603 Player Status Change History Passed 9/09/2016 Query select R.TEST_CASE_ID, R.TEST_CASE_NAME, R.EXECUTION_STATUS_NAME, R.ACTUAL_RESULT, R.END_DATE from SpiraTestEntities.R_TestRunSteps as R where R.PROJECT_ID = ${ProjectId} Template Test Case ID Test Case Name Status Date Executed Actual Result

Test execution status per Release with Actual result

David J (adam.sandman+support@inflectra.com) — Mon, 14 Nov 2016 00:12:53 -0500

This report allows me to use Excel to draw a lot of information from the testing performed over multiple releases to see how we are tracking, find recurring issues, produce stats on the defects causing the largest impact to testing, etc. Ver 5.0.0.8 Report sample: Release Test Case ID Test Case Name Status Date Executed Actual Result R7 - 20 Sept 3112 Upload documents - allowable formats Blocked 20/09/2016 Known environmental issue. Server being rebuilt R8 - 3 Oct 3112 Upload documents - allowable formats Failed 4/10/2016 317 R9 - 12 Oct 3112 Upload documents - allowable formats Passed 12/10/2016 R7 - 20 Sept 3115 Search functionality Passed 20/09/2016 R8 - 3 Oct 3115 Search functionality Failed 4/10/2016 322 R9 - 12 Oct 3115 Search functionality Not Run -- R10 - 24 Oct 3115 Search functionality Passed 26/10/2016 R11 - 7 Nov 3115 Search functionality Passed 9/11/2016 Query: select R.TEST_CASE_ID, R.TEST_CASE_NAME, R.EXECUTION_STATUS_NAME, R.ACTUAL_RESULT, R.END_DATE, L.RELEASE_NAME from SpiraTestEntities.R_TestRunSteps as R LEFT JOIN SpiraTestEntities.R_TestRuns AS L ON R.TEST_RUN_ID=L.TEST_RUN_ID AND R.PROJECT_ID = ${ProjectId} Template: Release Test Case ID Test Case Name Status Date Executed Actual Result

Summary of Test sets executed

David J (adam.sandman+support@inflectra.com) — Mon, 14 Nov 2016 00:21:04 -0500

Another report based on test sets that I then feed into Excel to extract deeper information about the current state of testing. Brief and informative. ver 5.0.0.8 Report sample: Test Set # Name Description Status Release Creator Owner Automation Host Created On Planned Date Last Modified Last Executed # Passed # Failed # Caution # Blocked # Not Run # N/A Est. Duration Act. Duration 32 Comps functional tests 2/11/2016 -- 0 0 0 0 135 0 TX94 Comps (CR tests only) Not Started Jill K 2/11/2016 -- 2/11/2016 -- 0 0 0 0 44 0 TX95 Comps (no CR tests) Not Started Jill K 2/11/2016 -- 2/11/2016 -- 0 0 0 0 91 0 7 R04 29 August 27/09/2016 9/09/2016 781 336 26 23 1063 0 0.78 TX42 R04 functional and sanity test set Completed R4 Lana N 29/09/2016 -- 4/10/2016 9/09/2016 21 18 0 1 54 0 0.05 TX39 R04 SIT test set Completed R4 Dave F 27/09/2016 -- 4/10/2016 13/09/2016 394 159 13 11 1009 0 0.72 TX62 R04 SIT test set (Member export, Micros) Completed R4 Annie K 3/10/2016 -- 4/10/2016 3/10/2016 34 9 13 11 0 0 0.00 TX63 R04 SIT test set POS Gateway Completed R4 Jill K 3/10/2016 -- 4/10/2016 3/10/2016 332 150 0 0 0 0 0.02 8 R05 7 September 27/09/2016 3/10/2016 32 7 0 0 0 0 0.00 TX66 R05 SIT test set (Micros POS terminal) Completed R5 Jill K 3/10/2016 -- 3/10/2016 3/10/2016 32 7 0 0 0 0 0.00 Template: Test Set # Name Description Status Release Creator Owner Automation Host Created On Planned Date Last Modified Last Executed # Passed # Failed # Caution # Blocked # Not Run # N/A Est. Duration Act. Duration padding-left: px; TX

Export Multiple Comments from Incidents from spira to xls

David J (adam.sandman+support@inflectra.com) — Mon, 14 Nov 2016 11:27:38 -0500

Please help me guys , this is urgent I need to export existing incidents to xls – which is OK , however some spira have multiple comments and export just gives last comment. Any idea how we can export multiple comments associated with individual issue ?

Can we export attachments in xls jpg png format associated with incidents

Jim R (donotreply5@kronodesk.net) — Fri, 18 Nov 2016 14:58:22 -0500

How do i reference a custom field value and not the internal id of the selection?

Carlotta Simonson (csimonson1@gmail.com) — Thu, 06 Apr 2017 02:40:41 -0400

the template below lists all the custom fields and displays them with their values. How do I reference a specific custom field if I dont want all of them listed? When I tried referencing CUST_02 it displays the values (2,3,4) which is the index of the selected value but not the value itself (which should be text like no workaround, has workaround, cosmetic). I want my report to show the CUST_02 field and list the text . How do i do that? This works to list all custom fields:

Problems with a report not referencing a custom field value

Kat A (elise.brooks@inflectra.com) — Fri, 02 Jun 2017 18:14:19 -0400

When I try to use a statement given to me in this forum im getting an error when the report runs My intention in the report is to print out the user associated with a requirement. The users name is stored in a custom list field CUSTOM_01. Im trying to get the report to print out the user name NOT the id of the user in the dropdown list. This is the statement i was told to use but it is causing an error

Simple Traceability Matrix

David J (adam.sandman+support@inflectra.com) — Wed, 12 Jul 2017 08:56:57 -0400

Recently, our QA/regulatory decided to run an audit and they requested a traceability matrix that traces through requirements, tests, incidents and tasks. Unfortunately, there are no ready-made SpiraTeam reports that does something like that so I had to write the template myself. As this was my first attempt to create xml/xslt template (I am embedded s/w guy), it is inefficient but does work. The template is based on Requirements Detailed template, so I suggest that if you were to use it, just clone the built-in template and replace the code with the one provided here. Heres how the output looks like: Req Name Relationship Related Req Task ID Test ID Incident ID Requirements PRJ-SRS-0001 Depends-on PRJ -PRS-001 TK5 TC101 IN201, IN202 PRJ -SRS-0002 Depends-on PRJ -PRS-002 TK6 TC102 IN204 PRJ -SRS-0003 Depends-on PRJ -PRS-003 TK7 TC103 IN205, IN207 Note that we are using custom requirements numbers in requirement name. The Software requirements are marked as SRS and project level requirements are marked as PRS. Heres the template. Req Name Relationship Related Req Task ID Test ID Incident ID 1>, 1>, 1>, TK 1>, TC 1>, IN

Is there a way to allow non-admin users to build reports?

Jim R (donotreply5@kronodesk.net) — Wed, 03 Jan 2018 18:16:25 -0500

Hello, Im looking for a way to allow non-Admin users to build and manage reports for their projects but I dont see any options or role settings that would allow that. Is there a way to do it? Thanks, Will Menton

Custom Graphs: How do I make them available to users?

Will Menton (william_menton@cinfin.com) — Mon, 03 Dec 2018 15:38:15 -0500

The custom graphs are great. Except that I cant find how to make custom graphs available to users. They dont seem to be available as widgets anywhere and they dont seem to appear on any pages by default. Where do these graphs appear? Thanks, Will Menton

Adding Filters to Custom Reports?

David J (adam.sandman+support@inflectra.com) — Thu, 10 Sep 2020 16:52:41 -0400

This is actually a cross-post from here https://www.inflectra.com/Support/Forum/spirateam/issues-questions/848.aspx Is there any plan to make the results of custom queries filterable? Not being able to filter means dumping the querys result set to Excel and the user further filtering it in Excel. Not horrible, but also not awesome. Thanks, Will Menton

XSLT Version

David J (adam.sandman+support@inflectra.com) — Thu, 15 Oct 2020 16:14:27 -0400

Hello, It appears that the custom reports only support xslt version 1.0. Since inflectracon2020, the number of requests for custom reports has increased so I was wondering if you had plans to upgrade to at least version 2.0 (released in 2009) if not version 3 (released in 2017)? The improved functionality would make custom reporting substantially easier, or at least far less verbose. Thanks, Will Menton

Report with Has Steps

Mariano Hagger (mariano.hagger@mobilezone.ch) — Wed, 12 Jul 2017 14:11:54 -0400

Hallo I coding a new Report. I us a Excel report with various values. One value i cant select i need to select the values from Has Steps? I use this code : But is not work. Can you tell me where is the error? Thank a lot for your help. Best wishes

Custom report with RS-to-IN or TR-to-IN association

Jim R (donotreply5@kronodesk.net) — Mon, 28 Aug 2017 09:07:04 -0400

Hi, Id like to make a custom report that basically has all the data Test Run Summary Report includes. Additionally I need one more column Incident that contains Incident ID if test run or step has failed and Incident has been created. I can see from Test Run Details that the Incident is linked to specific test step, but I dont know how I can access the association data to actually insert IN on correct row. I presume I need to use Custom Section, but Im unable to determine a correct query. Any ideas?

Requirements Word report : having titles and sub-titles ?

Jim R (donotreply5@kronodesk.net) — Fri, 04 May 2018 13:27:51 -0400

Hello , Is it possible, when generating a custom report in MS Word format, to have some Word styles (Title1, etc ..) applied on Requirements headline , in order to build easier index and tables in MS Word ? Thanks.

Custom report of test steps

Jim R (donotreply5@kronodesk.net) — Thu, 19 Oct 2017 14:05:40 -0400

I work in a project which has requirements traced directly to test steps and I would like the ability to report on execution progress at the test step level. Id like to see which test steps are passed/failed, if they are traced to a requirement and if they are traced to an Incident. This is what I would like the ability to view: Test Set ID Test Set Name Test Case ID Test Case Name Test Step ID Run Status Linked Requirement ID Linked Incident ID Can you help me set up this custom report query?

Creating Team-Wide Report Dashboards?

David J (adam.sandman+support@inflectra.com) — Mon, 12 Mar 2018 18:39:40 -0400

I can create custom reports and custom graphs and have those available to folks on my team, but is there anyway to configure the project and/or reporting dashboard so that everyone on my team sees the same widgets and reports by default? Right now I can add my custom graphs to my reporting dashboard, but no one else on my team will see it unless they go configure their own dashboard to include it. It would be nice if there was a way to publish a report dashboard view so everyone on the team is on the same page.

Date Compare in ESQL Query

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Mon, 09 Apr 2018 15:50:41 -0400

Im trying to do a date comparison in my custom query so that I can create a report that always reports on the last 30 days of test runs. Unfortunately it appears that every method suggested in the IBM documentation does not work in the custom query. Ive tried: Using DATEDIFF(date1, date2, days). Simply doing an interval subtraction (CurrentDateTime() - TR.END_TIME) DAY

Is it possible to include more parameters to the test case summary graph -Reporting dashboard

Jim R (donotreply5@kronodesk.net) — Tue, 10 Jul 2018 20:41:26 -0400

Currently on X-axis we can see two parameters ,Can it be done for more than 2 ? We are looking for a stacked bar chart which can help providing parameters on x-axis.

Report on product membership

David J (adam.sandman+support@inflectra.com) — Fri, 28 Feb 2020 11:32:44 -0500

Hi, Has somebody created a custom report from Product membership containing information about Program, product, product role, fullname and userid ? (across program and product) In Edit Reports it isnt possible to make a report on Product Membership. Regards Wenche

Custom Report Formatting and Styles

David Wilson (dwilson@rosenaviation.com) — Fri, 27 Apr 2018 18:36:48 -0400

Im converting a manual test report into a custom report in SpiraTest. Ive had some success with getting the content into place, now Id like to adjust the styling. I didnt see any provisions for a CSS file, so I tried to enter style attributes into tags. Most of it got deleted by the template editor. For example, I have a table in the report footer. I added a style attribute to the table tag and specified a width parameter. That worked fine. I added other attributes such as border and alignment, but they were deleted as soon as I toggled the source button in the editor. Similarly, if I added a style tag with the desired stylesheet markup, it was deleted as soon as I switched out of source view. I experimented for a while and found I could add an alignment parameter for example and, even though it was deleted, the changes did seem to take effect. So, in greater detail, I added a width parameter, like so: ... I generate a report as HTML, the width looked correct, and the page source looked just like I wrote it. The data in each cell was centered and I wanted it aligned to the left. So I added a text-align parameter: ... I saved and generated a report. The page source had the text-align parameter, but it was deleted from the report template. So my questions are: Is there someplace I can manage my own stylesheets in Spira or a path convention I can follow to add my own stylesheets? Why do the SpiraTest template editors accept some style parameters and quietly delete others? And when the template editor deletes some of my markup but uses it anyway, is it stored someplace where I can actually see whats being used and possibly modify it?

Requirement Coverage Report grouped by Component name

Aleksandra Marszalek (ama@avito.no) — Fri, 10 Aug 2018 09:19:20 -0400

Hi I would like to create report which will looks like that Component name Covering Passed Failed Blocked In Progress Book Management 17 8 3 0 20% Author Management 3 2 0 1 50% Administration 1 1 0 0 10% all Requirements are split out into 10 Components (parents requirement) to have better overview of status of each component, and I need to know what is the Requirement coverage total in each component + progress. in code I have only overview of each Coverage name in separate column... else, need help here Component Name Priority Status Covering Passed Failed Blocked Task %On Schedule %Running Late %Starting Late %Not Started padding-left: px;

Display Requirement Description in the grid report

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Thu, 16 Aug 2018 06:28:07 -0400

Id like to get the requirement description, not name out of the requirement and join with other information, ex test cases coverage. However I couldnt find a way to get it from the custom section of the report as theres no query to display requirement description value Thanks,

SQL queries for the existing graphs

Jim R (donotreply5@kronodesk.net) — Thu, 06 Dec 2018 05:47:49 -0500

Hi, Id like to create some custom queries for the Task, for ex Task Aging, and just wanted to access to view the queries of the existing ones to clone or learn, is there any way we can view? Thanks, Tung

Custom Reporting with Custom Properties

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Wed, 08 May 2019 23:40:27 -0400

Hi, I have a situation similar to what mentioned in the below https://www.inflectra.com/Support/KnowledgeBase/KB369.aspx However in my case I have multiple CUST values, (01, 02) and Im running query against Project Group, not Project. In the group, some project has only 1 CUST, some has 2 which made my report grabbing the unexpected custom properties. Can you please help to advise how to get them correctly? Thanks

Report the number of projects created

David J (adam.sandman+support@inflectra.com) — Mon, 09 Dec 2019 03:06:24 -0500

Hi there, I want to generate a report to show the number of projects/or products in rls6.x with created date, I couldnt find this was asked in the past. Does anyone know if its possible to do so and if yes, how? Thanks!

Error in report for custom values

Tung Nguyen (tran.nguyen@transport.nsw.gov.au) — Wed, 18 Mar 2020 00:50:07 -0400

Hi, Im generating report which includes a custom value and followed the instruction, https://www.inflectra.com/Support/KnowledgeBase/KB369.aspx i managed to get the report from the custom fields, however with the one using for the Date data type, its showing error Heres the error message in the Event Log Messages: An error occurred while reading from the store providers data reader. See the inner exception for details. [System.Data.EntityCommandExecutionException] Conversion failed when converting the nvarchar value 2020-03-11T13:00:00.000 to data type int. [System.Data.SqlClient.SqlException] at APPLICATION.Business.ReportManager.ReportCustomSection_ExecuteSQL(Int32 projectId, Int32 projectGroupId, String sql, UpdateBackgroundProcessStatus updateBackgroundProcessStatus, Int32 progress, Int32 numberOfRecords) My suspect is with the conversion from Date type to Int32 or something like that Anyone knows how to fix this? Many thanks

Need HELP creating a Custom graph

David J (adam.sandman+support@inflectra.com) — Mon, 04 Feb 2019 20:10:22 -0500

Hi all, I just learned about the ability to create a Custom Graph. I need some pretty simple graphs out of my SpiraTest data, but I dont know how to structure the ESQL commands. If anyone could provide a couple of simple examples I would greatly appreciate it. What Im looking for is pretty simply, need to create a graph that shows the growth of test cases within a given Project over time. Thanks for any input!!!

Default reports' SQL queries

David J (adam.sandman+support@inflectra.com) — Tue, 27 Nov 2018 13:36:18 -0500

Dear All, I have a question connected to the default reports. I can see all the templates but no SQL queries. I would like change one of them, add a plus field on which my ordering would be based. Thank You David

Creating a form for custom reports

Mark Grossman (mark.grossman@immersaview.com) — Tue, 27 Nov 2018 13:38:59 -0500

Dear All, I would like to ask You, if there is an option to create a form when generating the report, so I can select eg: my test set or release number to generate custom report from it. Thank You David

Need help creating a custom report

David J (adam.sandman+support@inflectra.com) — Fri, 08 Feb 2019 20:18:30 -0500

Hi. I have been trying to create a custom report for the last several days and am having some issues finishing it. I want a report that allows me to see all instances of a certain type of incident(we call these incidents Epics), along with all of its associated incidents of two certain types(we call them features and deliverables). We dont necessarily need any data along with the Epics themselves other than the name and number of the incident, along with the associations of course. We would like a table for the associations to have the columns for incident type, incident ID, incident name, status, estimated effort, remaining effort, actual effort, and owner. I have created a mockup of what I want the report to look like: The ability to filter certain fields before the report generates would be great if possible(especially helpful for detected release), similar to the way standard reports are now. If you could help provide some insight or code to help create this report, it would be much appreciated.

Data model

Jim R (donotreply5@kronodesk.net) — Mon, 11 Mar 2019 10:48:29 -0400

Hi everyone I am new to ST. How can I get a data model of the 38 tables that are available in the customized reports? I could of course potentially deduct it from select all statements of each table, but that would be quite cumbersome. A data model would also help me understand the tool. BW Peter Bunde Hansen

Requirement and tasks relationship

Ricky Wong (ricky.wong@compassoffices.com) — Mon, 19 Apr 2021 07:25:16 -0400

I tried to create a standard report which filter out the requirement and tasks. The template is as below. padding-left: px; I find that the second for-each is not working to retrieve the Task information. May I know if it is possible to do so? And if there is any document with corresponding information on the artifects relationship?

How to get a list of all tables under SpiraTestEntities

Andrii Bilous (andrii.bilous@edetek.com) — Fri, 24 May 2019 10:19:48 -0400

Hello comminity, Im new in Spira and currently working with Custom Report creation so have few questions. 1. Is it possible to extract all table from SpiraTestEntities by using something similar to the following? SELECT * FROM INFORMATION_SCHEMA.TABLES 2. Currently in the project we are using electronic signature for Test sets, Incidents, Requirments. So what is the way to retrieve electronic signature date and hash for those entities? Would be appreciated for any help. Best Regards, Andrii B

Analog for window function in Entity SQL

Jim R (donotreply5@kronodesk.net) — Wed, 05 Jun 2019 04:32:20 -0400

Hello, Im working with SpiraTestEntities.R_HistoryChangeSets table and would like to output only latest hisoryChange record for each requirement/test set. I was trying to find information about how to use window function with Entity SQl but have found nothing. Looks like it is not supported. Is there any workaround in Spira to use window functions such as ROW_NUMBER() or DENSE_RANK()? Best Regards, Andrii

column with associated Requirements of one Specific type

David J (adam.sandman+support@inflectra.com) — Tue, 09 Jul 2019 14:16:19 -0400

Hi, I would like to create a traceability report, with a column that shows only associated Requirements of a specific Type. To see if a Requirement is associated with a Requirement of a specific type. For example a column Requirements Traceability Type Feature where only associated Requirements of the type Feature are listed. I am not sure how to customize the Report Template correct to get this output. I tried: 1) and (RequirementTypeName=Feature))> , RQ RQ but got no output. Thank you!

Report PDF or Word header

David J (adam.sandman+support@inflectra.com) — Wed, 11 Sep 2019 12:41:09 -0400

Hi, When I create a report in pdf or word format, there is by default a header with the name of the report. Then there is by default a heading with, again, the name of the report. And at the end of the page there is a footer with: Page x of y. I want to change specially the header and the heading. How ist this possible? Thanks Christine

Notes in Reports

David J (adam.sandman+support@inflectra.com) — Wed, 23 Oct 2019 15:21:39 -0400

Hi, How can I include the Notes (Template Agile) to the report. (The Description is correct entered in my Report) Thanks

Linked Requirements information in Report

Vladimir Plotkin (vladimir_plotkin@oslerdiagnostics.com) — Tue, 05 Nov 2019 10:50:44 -0500

Hi, I want to create a report with a table Linked Requirements, as in the Report Requirements Detailed. Is it possible to include the Description of the Linked Requirements in the table? Ive tried but it doesnt work. Regards, Christine

Adjust column width

Mike Becker (Michael.Becker@protechassociates.com) — Wed, 20 Nov 2019 14:30:44 -0500

Is it possible to adjust the column width in a costum Report? Thanks!

Dynamic data in Header and Footer

Ilia Poliakov (ilya.polyakov@edetek.com) — Thu, 04 Jul 2019 17:59:27 -0400

The documentation says that I can only use static labels and images in the header and footer. My requirement is to add ProjectID into the header of the report. I also need to add current date and page numbers in the footer. Is this possible? If I try to add an XML section into Header and save, my XML is removed. Thank you!

Custom Tracabiility Report

David J (adam.sandman+support@inflectra.com) — Thu, 17 Oct 2019 21:31:16 -0400

This custom Traceability Report from the KB https://www.inflectra.com/Support/KnowledgeBase/KB471.aspx is great! Is there any way to allow for Sorting and Filtering like we have in the Standard (Template) report? Thanks

Reference elements from parallel nodes

Kat A (elise.brooks@inflectra.com) — Mon, 21 Oct 2019 22:47:08 -0400

I need to add a Requirement Number into the Test Steps grid in the Test Case Detailed report. I noticed from the XML source of the report that the structure of the XML template is based on: ... ... ... ... ... ... ... ... ... So Inside of this reports XML template I have a section for displaying the test steps where I need to add Req # as first column: Test Steps: Step Description Expected Result Sample Data Last Status with = , Please explain how it works to link to the parent (TestCase) node using check and if I can potentially reach into the ... node to show the linked Requirements # from TestSteps/TestStep node. I understand that I could add a Custom SQL section (which I already have) but the result of that shows at the bottom of the report and I really only need to add one column to the existing grid to fulfill my requirements so there is no reason to make it complicated. If I need to re-do the whole report in Custom SQL displaying TC, TS and Reqs - this is also too much work and Im not sure I could get all Custom Properties and all text values correctly in my SQL. Please help! Thank you

use of Sub-Queries in Custom Section

David J (adam.sandman+support@inflectra.com) — Mon, 28 Oct 2019 15:21:35 -0400

Hello, I tried using a sub-query in the SQL of a Custom report Section but I keep getting generic errors when I try to preview: System.Data.EntitySqlException: The query syntax is not valid. Near term 1, line 17, column 15. Can I use Sub-Queries or is this restricted in Custom SQL Section? My SQL is below: select TC.TEST_CASE_ID, TC.NAME AS TESTCASE_NAME, TC.DESCRIPTION AS TESTCASE_DESCRIPTION, TC.EXECUTION_DATE as TESTCASE_EXECUTION_DATE, TC.CREATION_DATE as TESTCASE_CREATION_DATE, TC.LAST_UPDATE_DATE as TESTCASE_LAST_UPDATE_DATE, TC.AUTOMATION_ENGINE_NAME as TESTCASE_AUTOMATION_ENGINE_NAME, TC.EXECUTION_STATUS_NAME as TESTCASE_EXECUTION_STATUS_NAME, TC.AUTHOR_NAME as TESTCASE_AUTHOR_NAME, TC.OWNER_NAME as TESTCASE_OWNER_NAME, TC.TEST_CASE_STATUS_NAME as TESTCASE_TEST_CASE_STATUS_NAME, TC.TEST_CASE_TYPE_NAME as TESTCASE_TEST_CASE_TYPE_NAME, TC.TEST_CASE_PRIORITY_NAME as TESTCASE_TEST_CASE_PRIORITY_NAME, TC.CUST_01 as TESTCASE_CUST_01, TC.CUST_02 as TESTCASE_CUST_02, TC.CUST_03 as TESTCASE_CUST_03, (SELECT TOP 1 TC_C1.NAME from SpiraTestEntities.R_CustomListValues as TC_C1 WHERE cast(TC.CUST_02 as int32) = TC_C1.CUSTOM_PROPERTY_VALUE_ID and TC.PROJECT_ID = TC_C1.PROJECT_ID) as TESTCASE_CUST_04, TS.TEST_STEP_ID, TS.POSITION AS TESTSTEP_STEP, TS.DESCRIPTION as TESTSTEP_DESCRIPTION, TS.EXECUTION_STATUS_NAME as TESTSTEP_EXECUTION_STATUS_NAME, TS.EXPECTED_RESULT as TESTSTEP_EXPECTED_RESULT, TS.SAMPLE_DATA as TESTSTEP_SAMPLE_DATA, RTC.REQUIREMENT_ID, RQ.NAME AS REQUIREMENT_NAME, RQ.DESCRIPTION AS REQUIREMENT_DESCRIPTION, RQ.REQUIREMENT_STATUS_NAME as REQUIREMENT_REQUIREMENT_STATUS_NAME, RQ.REQUIREMENT_TYPE_NAME as REQUIREMENT_TYPE_NAME, RQ.IMPORTANCE_NAME as REQUIREMENT_PRIORITY, RQ.RELEASE_VERSION_NUMBER as REQUIREMENT_RELEASE_VERSION_NUMBER, RQ.CUST_01 as REQUIREMENT_CUST_01 from SpiraTestEntities.R_TestCases as TC left join SpiraTestEntities.R_TestSteps as TS on TC.TEST_CASE_ID = TS.TEST_CASE_ID left join SpiraTestEntities.R_RequirementTestCases as RTC on RTC.TEST_CASE_ID = TC.TEST_CASE_ID left join SpiraTestEntities.R_Requirements as RQ on RTC.REQUIREMENT_ID = RQ.REQUIREMENT_ID where TC.PROJECT_ID = ${ProjectId}

sorting results of a Custom SQL report

David J (adam.sandman+support@inflectra.com) — Mon, 28 Oct 2019 20:14:57 -0400

Hello, Im trying to re-sort the results that I get from the SQL query. My query joins Test Cases with Test Steps and Requirements. It seems to be sorting using record IDs by default. However, my Test Steps are out-of-sequence, so Test Step 6 has a higher TEST_STEP_ID than Test Step 5 and these show-up in the report in the same default sequence. I added a new SORTBY column into my SQL hoping that would work but it doesnt Is there another way to custom-sort my results since ORDER BY does not work in ESQL? Thanks!

Filtering Custom report by Release

Alex Soloviev (alex.soloviev@celerion.com) — Mon, 07 Dec 2020 22:40:24 -0500

So there is a note underneath the SQL section of the Custom Section form that says: For report sections that can be filtered by release, use tokens ${ReleaseId} to filter by the specified release, or ${ReleaseAndChildIds} to filter by a comma-separated list of the release and child sprints/phases. When I add an additional filter into my SQL: AND RQ.RELEASE_ID=${ReleaseId} Then it works correctly for the times when Release is selected. But when I select Release ---ALL--- value it doesnt work and no results are displayed. What is passed to the query when Release=ALL is selected and how should I account for the selection in my SQL? Thank you!

Using multilist fields in custom reports

David J (adam.sandman+support@inflectra.com) — Fri, 11 Dec 2020 14:41:02 -0500

I was wondering if anyone ran into this issue before. One of our products uses a custom field of the multilist type. I ran into a problem when trying to create a custom report using this. The custom property holding multiple list values will look something like 0000000015;000000011, refering to the custom list values from the CustomListValues table. So when I want to have the actual readable names of the values selected I have to do a table merge of my artifact table with CustomListValues, where the Custom List Value ID should be contained in the CUST property. I can use something like ON CustomListValue ID IN CUST property, but the ID is obviously an integer and the multiselect propertys elements are strings. I can cast the integer to a string, but that wont give me the leading zeroes... As far as I know there is no way to do string formatting in E-SQL, but Im not really an SQL wizard so perhaps Im overlooking something here. On the other hand, this seems like a standard enough thing to run into with Spira so I think there should be an easy solution? Any help is appreciated!

Landscape section in custom reports

Mike Becker (Michael.Becker@protechassociates.com) — Fri, 12 Mar 2021 10:42:22 -0500

Hello, Im creating a custom report (which will be exported in Word format), and want to make one section of the report (with a particularly wide table) in landscape orientation. I can do a section break with the mso word formatting code, but this code for making the section Landscape doesnt work: @page LandscapeSection {mso-page-orientation:landscape;} div.LandscapeSection {page:LandscapeSection;} MY SECTION Is this supposed to work, am I doing something wrong, or can this be achieved some other way? Help would be appreciated.

Retrieving username in custom report

Ilia Poliakov (ilya.polyakov@edetek.com) — Mon, 29 Mar 2021 13:15:29 -0400

Id like to make a report called something like My daily changes for reviewing purposes. This would list all artifact changes made by me (the one requesting the report) on the current date. This in order to summarize changes I have just made to submit to the review group, so they know what artifacts and changes to look at. The data itself is no problem, but I cant see a way to easily retrieve the name /ID of the user that has pressed the Generate Report button. Perhaps ESQL provides some function to retrieve the LDAP login name of the current user but I really need the Spira user ID so I can filter the records using this. If this is not available, consider this a feature request, which I think should not be too hard to implement (something like having a token {$UserId} in my query). Im sure I could think of several other useful things to do with this (My Assigned Defects, My tasks, etc....)

Document table not available??

David J (adam.sandman+support@inflectra.com) — Tue, 25 May 2021 12:34:33 -0400

Use case: Wed like to make documentation based on Spira data, which we (for the time being) store in Sharepoint. Currently, we create a document using Word and our QA templates, and copy-paste the bulk of the content from a custom Spira report for each new version. While this works, its not the best user experience... Problem: We have document content that does not fit neatly into Requirements, Risks, Test Cases, etc.... (for example, Introduction, Review History, Terms and Abbreviations, etc.) However, this content can vary depending on the doc type, version, and product, so we cannot hardcode it in the Spira template. The front page, TOC etc. is not really a problem for now since that can be coded into the template. Our solution: We use the Spira Documents artifacts. For each document, we add a Link Document artifact (linking to the actual document in Sharepoint), and add some metadata. We use the Description field for that document for Introduction, terms, history, etc...) This content belongs to the document, not to the underlying requirements, test cases, etc. so it logically fits. Then for each Spira template, we add a Doc Intro section that matches Product + DocType (from the Spira Document Types we configured) and retrieve the Description so it can be inserted in front of the bulk content. This way, everything can be maintained in Spira, and when generating a report, we can produce a complete document with proper layout and all content accounted for. Only.... when creating a Custom Section....there is no SpiraTestEntities_R_Documents? There is DocumentTypes and DocumentStatuses, but no Documents? Is that table named differently, or is this just a bug I found? It seems very silly to omit this table since you do have Types and Statuses available for Documents. How can I get to the Documents table? Im using SpiraPlan 6.9.0.1

Some suggestions for new and improved reporting features

Colin Main (colin.main@cmat.co.uk) — Thu, 17 Jun 2021 11:36:39 -0400

After a few months of using Spira actively (and creating a load of reports), here are some suggestions to the great folks at Inflectra for new features that would make life a lot easier. Do what you will with them and feel free to comment or append. Low hanging fruit: Allow re-ordering of document sections by moving them up or down. Its driving me nuts to have to use delete and clone just to change the order of things or to insert another section somewhere. Use a treeview like structure instead of a list for the reports. A single list can get pretty long and you lose overview. Addendum to the last: In the treeview, have a Favorites node where you can put the reports that you use often Allow creation of custom categories (like Requirements Reports, Test Case Reports, etc.) Management/Structural: Reports are separate from Project Templates. Why? A custom report can assume certain types or custom fields to be present that are defined within a template. Some reports will not make sense for projects using project templates they are not intended for. Link a set of (custom) reports to a project template to have one consistent whole. Allow versioning of a report as a single entity. A report is now just a collection of text fields that can be very easily overwritten by mistake, wasting hours of work (yes it happened to me too several times...) Allow storage mechanism similar to Documents, where you can add/upload a new version. In QA driven organizations, it is required for Document templates to have versions also. It should be possible to restore an older template. We are using a separate Git now where we store the SQL and XSLT portions of each report separately which is, frankly, quite ridiculous. Improve the edit view of the SQL and XSLT portions. Preferably full screen (while editing) and syntax aware. We are currently using VS Code or sometimes even Notepad to edit them because the web form is really horrible to use. Report content: Allow to add custom graphs to a report as a custom section, as an alternative to the current sections. Graph can be included as a rendered image, based on a defined custom or standard graph in Spira. Its a shame to only be able to see those graphs in the Spira GUI itself. More custom parameters in the SQL query of a custom report. Next to ProjectId and ReleaseId, we would like UserId (the one requesting the report), BranchId, and perhaps even define a custom parameter yourself in the report definition - youd set a display name (for the GUI), a variable name (for in the SQL query) and a field type (string, number, user, date, etc.) that determines the edit control to enter it when requesting the report. This would make up for the lack of extensive filter options you have in a custom section report as opposed to using only standard sections. Thats all I can come up with. Thanks for making such a great product!

Using Includes in report XSLT

David J (adam.sandman+support@inflectra.com) — Fri, 16 Jul 2021 12:51:39 -0400

There are several XSLT templates we are re-using across documents - take for example the format-date template from the default Spira doc sections. We now also have sections that (for the Word export) add house style stuff like headers, company logos, page numbering, etc. This could all be placed in separate templates, but this still means the XSLT code needs to be copied for every doc template. Wed like our xsl files to reference the shared generic templates instead. Is it possible to use the xsl include feature and if so, what is the base folder on the server where the parser will look for these files while generating the reports (using absolute paths would be possible too of course) ? We would like to build up a little library of re-used xslt template snippets to get rid of the duplication. Also, this would make adapting our house style a LOT easier. Or is there some other solution to this than xslt includes? Are there examples available for this?

Track Changes report in Spira

Ilia Poliakov (ilya.polyakov@edetek.com) — Wed, 30 Mar 2022 13:14:59 -0400

One of the most often heard complaints after introducing Spira in our company, was people grumbling about how they were missing Word and how easy it was to see the changes in the requirements there (using the Track Changes). Granted, Spira artifacts have a History view, but just to review the latest requirements additions, its not very practical - especially if you have huge section of rich text...good luck finding the changes between now and last week! Many resorted to Word exports and using Word compare to a baseline they made before. I thought there had to be a better solution, and fortunately there was. Spira has all the data needed to provide a smart track changes report, with the last bit of smart behavior provided by some Javascript included. Initial experiments showed positive responses by users. The report is now set up as a HTML-only Last weeks Requirements Changes, going a week back (but this can be easily changed in the SQL and XSLT). It includes a date selector that allows to filter out older changes (before a certain date). The Javascript uses HTMLDiff (credits included in the XSLT), a handy bit of Javascript that performs the insertion and deleted strike-through formatting familiar from Word, although you might want to adapt your SpiraPlan html base template to style the and tags to your specific tastes, as seen here: ins { text-decoration: none; background-color: #d4fcbc; } del { text-decoration: line-through; background-color: #fbb6c2; color: #555; } The XSLT is quite clumsy due to the fact that CDATA sections had to be included to the Javascript to be properly parsed, the fact that XSLT 1.0 does not have any Date/Time datatypes, and the fact that requirements also have requirements steps, which are technically different artifacts, but should be included where they are supposed to. However, it does the trick. Mind you, multiple changes within the set time span of a single Field in a single Artifact are squashed, taking the difference between the oldest value and the newest value (which was more convenient than seeing multiple subsequent changes in the same field as different entries). I used only standard fields, but you may want to adapt the requirement types in the SQL you that want to include in the report ... Screenshots: https://dennis-pie.tinytake.com/msc/NjY4MjkzNF8xOTI1OTE3Mw https://dennis-pie.tinytake.com/msc/NjY4Mjk1MV8xOTI1OTE5MA SQL and XSLT: https://drive.google.com/file/d/1QcFzySmXfev_4ewYsPR66atD71-0DjPp/view?usp=sharing Oh and Inflectra... I do think something like this should be included by default in your history view! Please add it to your backlog!

Getting source code associations for report

Ilia Poliakov (ilya.polyakov@edetek.com) — Thu, 04 Aug 2022 11:16:19 -0400

Since we managing our requirements BDD-style, it makes good sense to have links between our Gherkin files (source code) and Requirements, since the Gherkin serves both as a test case and as an extension to the requirements. I dont want to maintain Gherkins as separate Spira artifacts since our CI server runs continuous (cucumber) tests based on those Gherkins, so they are considered source code. Thankfully, this type of association is supported by Spira. However, Id like to make a custom report that shows the requirements listed and for each its associated source files (=gherkins). Now I dont see an out of the box table to use for getting to the source files (although I can pull that info out using the REST API). Also, Im not sure if the links between source files and requirements would show up in the ArtifactAssociations table (since the source files have no table and no Ids). Is a report like this possible, or will it be possible in the near future?

Getting IndentLevel for Test Folders

D L (dennis.lardenoye@pie.nl) — Wed, 13 Sep 2023 13:26:02 -0400

When we create a requirements document/report (custom), I use the IndentLevel field in the Requirements table to make sure the requirements appear in the document in the same order an hierarchy as they do in the Spira GUI (using Word heading levels). IndentLevel will look something like AAAAAAC, signifying both the order and the nesting level in the tree of a specific item. If I want to do the same for a Test Specification, based on the Test Case Folders, this does not seem to be so simple. The default Test Case Folder table does not include an IndentLevel field, only a ParentFolder field. ParentFolder is not so easy to use when building up a document, since you need to reconstruct the hierarchy of folders before being able to do anything. Now the Standard section available in Spira called Test Case List does have a property IndentLevel, which is what I need. However, I cannot use Standard sections because my documents consist of multiple custom sections and adding a standard section will always automatically put its contents first in the document, before all other sections, messing up the document order. So Id like to know, is there an easy way to reconstruct the IndentLevel for test case folders using SQL or XSLT? Im not THAT great with SQL, so any help would be appreciated!

Custom graph X axis date format?

D L (dennis.lardenoye@pie.nl) — Wed, 17 Jan 2024 15:50:18 -0500

Id like to create a custom (bar chart) graph showing specific test activity over time. Number of test runs passed/failed are counted per day. Since Im filtering for custom fields, I cannot use one of the standard graphs in Spiraplan. I have it working so far, except one thing: the standard graphs account for empty days (with no activity). It appears this is not handled by the graph control itself and smart placement on the X-axis, but the empty days are present in the underlying data table. Id like the bars in my graph to represent their actual placement in the date range. Since Im not an (E)SQL wizard, I would really like to know how to produce a range of dates that could be used to JOIN my existing table with, so I can have all dates in my range, including the ones without activity. An example would be most welcome!

Custom Report or Graph with Test Cases by Program

David J (adam.sandman+support@inflectra.com) — Thu, 21 Nov 2019 09:46:26 -0500

Hi, I am trying to find a way to show All test cases by Project and Status. I looked in the the build in Reports, but I couldnt find. I tried creating custom one, but I ended up by select INC.PROJECT_NAME, count(INC.TEST_CASE_ID) as INCIDENT_COUNT from SpiraTestEntities.R_TestCases as INC where INC.IS_DELETED = False group by INC.PROJECT_NAME order by INC.PROJECT_NAME I want to be able to show the table below in a report or graph. Can anyone help please. Test Case Status 1 Test Case Status 2 Test Case Status 3 Project 1 Project 2 Project 3

ESQL error

David J (adam.sandman+support@inflectra.com) — Mon, 14 Dec 2020 21:53:32 -0500

Hello, I am new to Spira and while I have some SQL experience, I have not used ESQL before. I keep getting an exception when trying a report syntax that worked in our previous tool. Ive spent some time searching and looking at examples, but have come up empty. I am hoping some ESQL expert can point me in the right direction. I am trying to get a list of test case ids from test cases that are nested two folders deep. I would like a query to specify both folders so have written this: select TC.TEST_CASE_ID as TestID, TC.NAME as Test_Name, TCF.TEST_CASE_FOLDER_ID as FolderID, TCF.NAME as Folder from SpiraTestEntities.R_TestCases as TC, SpiraTestEntities.R_TestCaseFolders as TCF where TC.PROJECT_ID = ${ProjectId} and TCF.NAME = and TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_ID and TCF.PARENT_TEST_CASE_FOLDER_ID = (select R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = ) However, this produces the following error: System.Data.EntitySqlException: The argument types Edm.Int32 and Transient.collection[Transient.rowtype[(TEST_CASE_FOLDER_ID,Edm.Int32(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)] are incompatible for this operation. Near equals expression... Thanks, Shari

Custom report to check is incident has been postponed

David J (adam.sandman+support@inflectra.com) — Mon, 02 Mar 2020 23:57:08 -0500

Dear Team, I am new to create the customized reports. We have added a custom field for Incident called Delivery Package where we define the releases in which bug will be delivered. Now, I have to create a report that will fetch all the incidents which have been postponed to next release (by checking delivery package). I have the logic. I should check at the time of creation if value for Delivery Package is not same as current value then it means it has been postponed. But I am not sure what the query will be. Please assist. Thanks!

Print out the available entity sets under the SpiraTestEntities

Caleb Cox (caleb.cox@Nlign.com) — Thu, 16 Jan 2020 19:38:22 -0500

Hi, I am trying to find a command to list all of the available entity sets under the SpiraTestEntities entity container. I want to custom query for incidents and any associated incidents, I am assuming i will have to query for incidents and use a join with a query for the artifact links. I cannot find any documentation on this

TruncateTime not actually removing the time - just zeros it

Leslie Gustafson (leslie.gustafson@toluna.com) — Tue, 11 May 2021 20:39:02 -0400

Im trying to just have the YYYY-MM-DD value from a date to show in a graph and per https://www.inflectra.com/Support/KnowledgeBase/KB250.aspx i, the TruncateTime() method should do this. But when I use it, it is still showing a time value of T00:00:00 appended. i.e. it simply changes a date from 2021-01-01T05:22:15 into 2021-01-01T00:00:00 . But I want to use the date as the x axis on a graph. This is the sample query I am using: select DatePart, count(CASE WHEN R.EXECUTION_STATUS_ID =1 THEN 1 WHEN R.EXECUTION_STATUS_ID =2 THEN -1 END ) AS Sum from SpiraTestEntities.R_TestRuns as R where R.PROJECT_ID = ${ProjectId} and DiffDays(R.START_DATE, CurrentDateTime()) < 100 group by TruncateTime(R.START_DATE) as DatePart Ive tried converting to strings, etc to remove the T00:00:00 to no avail.

Test Cases Report - Suspect field

Vladimir Plotkin (vladimir_plotkin@oslerdiagnostics.com) — Wed, 16 Sep 2020 13:24:18 -0400

Hi, I need to add the Suspect? column to Test Cases report. What shall I put into the line for it? Thanks

Need help to build custom report to pull the modified testcases

David J (adam.sandman+support@inflectra.com) — Mon, 31 Jan 2022 17:17:54 -0500

Can we build a Custom Report to pull the modified testcases in a given project. We looked into tables for SpiraTestEntities.R_HistoryChangeSets and SpiraTestEntities.R_HistoryDetails but none of the fields are matching to get the modified Testcase id.

Reporting on Steps actual results in requirements detailed

David J (adam.sandman+support@inflectra.com) — Tue, 26 May 2020 22:38:41 -0400

Hi all, Our requirements are mapped to steps. From the requirements detailed report, we are trying to insert the Step Actual Results where they exist in between each requirement the same way the Test Coverage is showing...Any way to do this? The Actual Results of the step and last execution date (can be of the test case) for each requirement is what were missing...Weve tried to use KB397 to create what we need in SQL, but it comes with its own challenges... Thanks

Reporting an artifact's parent in XSL

Vladimir Plotkin (vladimir.plotkin@midiagnostics.com) — Wed, 17 Mar 2021 14:19:39 -0400

Hello: I am wondering if someone can help me identify the appropriate property name of a Requirement that would allow me to report the requirements parent (in XSL)? Thanks -Ed

Querying History of Test Case Automation Engine

Matthew Blyde (mblyde@gme.net.au) — Thu, 11 Feb 2021 04:52:50 -0500

Hi, Im attempting to produce some reports to track our progress as we go about automating our test cases. As part of this, I would like to retrieve the history of the AUTOMATION_ENGINE_NAME field of test cases. However, changes to this do not appear to stored in SpiraTestEntities.R_HistoryDetails. If I change that field then run the query below then there are no results listed when AUTOMATION_ENGINE_NAME is changed: select value H from SpiraTestEntities.R_HistoryDetails as H order by H.CHANGE_DATE desc Is there a recommended way to query changes to the AUTOMATION_ENGINE_NAME? Ultimately Id like to generate a count of cases that have had their AUTOMATION_ENGINE_NAME set, per day.

List of latest test runs per test case

Victoria D (nino.diasamidze@inflectra.com) — Fri, 25 Mar 2022 07:20:53 -0400

Hi folks I would like the have a table with the latest test runs for each test case. Can someone help me with a ESQL query?

EXSLT or date-time functions

David J (adam.sandman+support@inflectra.com) — Mon, 15 Feb 2021 19:44:30 -0500

In my custom report I need to calculate number of days between two dates. How can I implement it? Are any EXSLT libraries installed? like: http://exslt.org/date/functions/difference/date.difference.html or fn:day-from-dateTime( datetime ) ? Otherwise it looks like it is rather complicated... Could somebody write several examples how to deal with it?

ArtifactAssociations Name: can not see "Prerequisite-for" text

Ilia Poliakov (ilya.polyakov@edetek.com) — Wed, 08 Sep 2021 20:01:34 -0400

I am running: select value R from SpiraTestEntities.R_ArtifactAssociations as R where R.SOURCE_ARTIFACT_ID=23741 Where that enity with ID=23741 has a relationship Depends on. So, its partner should have relationship Prerequisite-for (it is how it is shown from Spiras interface, but not in reports) but I can not get text Prerequisite-for from Spira reports. Probably, you should have ARTIFACT_LINK_TYPE_NAME _forward and ARTIFACT_LINK_TYPE_NAME _backward instead of one ARTIFACT_LINK_TYPE_NAME

API for custom reports deployment, proposal

David J (adam.sandman+support@inflectra.com) — Thu, 09 Sep 2021 02:49:56 -0400

As we have started using reports activly, we have found that it takes a lot to deploy a new version of reports to Spira. Could you create an API to let everybody automate process of deployments for already created reports? To make your life simple (and ours too) lets cover next scenario: An user created a cusom report from the Spiras UI That API allows to update aleady created custom report Hope that such scenario wont take a lot.

Out of the box tracebility report, need a sql code of it

David J (adam.sandman+support@inflectra.com) — Fri, 22 Apr 2022 06:16:45 -0400

Hello, I am trying to understand a logic of a default (out of the box) Requirements Traceability report. Could you provide a sql logic that is under it? Especially I am interested what requirements are shown in its Requirements Traceability column.

Example Reports That You’re Proud of

David J (adam.sandman+support@inflectra.com) — Mon, 10 Aug 2020 20:00:21 -0400

Hello, We are considering Spira and I wanted to see if anyone was willing to share their custom reports from Spira that theyre particularly proud of. People love for reports to have their corporate look-and-feel. Spira promises that this is possible but I just cant find enough examples on the website to convince my colleagues. Are you willing to share some example reports with me so I can see whats possible in terms of formatting, content curation, etc? Thank you so much! Danny

Xlst to display a List Value from a Custom Property field

David J (adam.sandman+support@inflectra.com) — Tue, 25 Jan 2022 18:19:43 -0500

Have created an Incident Custom Property which is a list. example List Value # VAlue PV:21916 Functional PV:21919 Regression In Reports created a custom query and want to display the List Value. Only the List Value # is being displayed. select R.CUST_30, COUNT (R.INCIDENT_ID) as COUNT from SpiraTestEntities.R_Incidents as R where R.PROJECT_ID = ${ProjectId} and R.DETECTED_RELEASE_ID in {${ReleaseAndChildIds}} group by R.CUST_30 Default Template Calls This displays the List Value # td> This does not display the List value instead displays blank. What is the xslt syntax to display a list value not the list value number.

Change Colours of Bar Charts?

David J (adam.sandman+support@inflectra.com) — Mon, 01 Mar 2021 20:23:37 -0500

I created a custom graph showing the test status of different modules. X Axis are the modules, the status passed, failed etc build a stacked bar chart. Unfortunately, failed is displayed green, whereas passed is orange. This is pretty counter-intuitive... Can I adjust the colours?

Adding custom cover page to spira reports

D L (dennis.lardenoye@pie.nl) — Mon, 30 Aug 2021 04:50:10 -0400

Hello Team, I am looking for adding about 2-3 custom cover pages with some details on top of Spira reports, If anybody tried please help me with this. Thanks in advance .

Merging word document to spira report

D L (dennis.lardenoye@pie.nl) — Mon, 30 Aug 2021 10:30:19 -0400

I need to append/merge the cover page in docx format to the exported spira report. kindly let me know if anyone has tried to achieve the same or has any idea on merging to spira report. Thanks

Project history report

David J (adam.sandman+support@inflectra.com) — Mon, 15 Mar 2021 09:56:54 -0400

Hi, How do I generate the complete audit trail for the entire project? I know I can go to Project History and check it in there but how do I export it to a document? Thanks

Custom fields in traceability report

Vladimir Plotkin (vladimir.plotkin@midiagnostics.com) — Tue, 16 Mar 2021 13:49:14 -0400

Hi, For the current project our QA doesnt want us to use RQ numbers for traceability but rather QA-governed requirements numbers. These are in form of PRJ-SW-XXXX, where the XXXX is manually assigned requirements number for the subproject. To accomodate this, Ive created a mandatory custom field for requirements, which shall contain these QA numbers. It is fairly easy to include QA numbers for the requirements inside of the current projects but I cant figure out how to do the same for linked requirements? Heres what I want to achieve: QA ID Brief Desc Type Status Release Traced to QA ID 001-SW-001 do something Feature Accepted 1.0 001-PRS-001 001-SW-002 Do something else Feature Accepted 1.1 001-PRS-002 001-PRS-125 To do it, I created a the following template. It works for QA ID column but not for Traced to QA ID one. What am I doing wrong? MDX ID Brief Type Status Release # Test Traceability Traced to MDX ID padding-left: px; 1"> , TC TC 1"> , [PR: ] :

ESQL and custom selection filters

Erik Swedlund (erik.swedlund@ascom.com) — Thu, 15 Apr 2021 10:32:17 -0400

Our requirements have a custom selection filter that specifies type of Requirement Documents (User Needs, Functional Requirements, Regulatory and so on). When generating a standard Requirements Detailed Report I can choose the Req Type (or, more precise, Document type) and generate the required document. However, we are using ESQL to generate a specific format of the report and the selection is no longer available. What is the way to invoke report generating filtering with custom ESQL?

Report formatting issue when requirements imported from Excel

Bibah Ryeq (naveedmunirch@gmail.com) — Wed, 13 Jul 2022 13:39:54 -0400

It seems that when importing requirements from Excel (SpiraImportTemplate2010) there is some hidden formatting that apperas only in the Requirements report, and Remove Format button is disabled in Spira for the requirement. How i get requirements in the report: RQ:2230 A Contratada DEVE disponibilizar o cdigo-fonte final do software. RQ:2231 A Contratada DEVE fazer um workshop de repasse de tecnologia sobre estrutura e organizao do cdigo fonte e uso da soluo desenvolvida. RQ:2232 Testando criao de requisito, ao invs de importar no Excel. Exmplanation: RQ:2232 was created directly in Spira. Format in the report looks good. RQ:2231 was created by import via Excel, but after this was edited in Spira, just to cause another SAVE, no real change. Note: The Remove Format button was disabled in the requirement in Spira. RQ:2229 and 2230 were created by import via Excel. There seems to be a formatting issue because there is no space between RequirementId and Description but the Remove Format button is also disabled in the requirement in Spira. My report code: margin-left: px; - RQ: padding-left: px; RQ: --> RQ:

Pie Chart of Test Run by Status

David J (adam.sandman+support@inflectra.com) — Tue, 27 Feb 2024 14:17:08 -0500

Hi, I have tried to use the recomendation in KB511 but received the following message: You need at least two columns in the query to display as a graph! https://www.inflectra.com/Support/KnowledgeBase/KB511.aspx

Custom report for latest test results per release

David J (adam.sandman+support@inflectra.com) — Tue, 12 Jul 2022 06:53:12 -0400

In order to get the current state of test case execution in specified release, an ESQL query is required. Is there a way to obtain data about all the latest test execution results for all the test sets that have specific release selected? Couple of notes: If a test case belongs to a test set marked with specific release, then latest execution result per that release is required If a test case belongs to a test set marked with specific release and test case has not been executed per that release, it should be marked as such Just a couple defining properties of the test case will be sufficient (TR.TEST_CASE_ID, TR.NAME, TR.RELEASE_ID) Release should be selectable in Custom Section Filter

Reqs Covered in Tests, where the tests are filtered

David J (adam.sandman+support@inflectra.com) — Mon, 27 Nov 2023 19:48:29 -0500

The default test coverage number on a requirement is COVERAGE_COUNT_TOTAL: https://spiradoc.inflectra.com/Reporting/Custom-Report-Tables/#:~:text=COVERAGE_COUNT_TOTAL This number is Test Case Status agnostic - it doesnt care if a requirement is covered in obsolete-status tests, tests in a pending status, tests that have some custom property that qualifies them, or whatever. Personally I think there should be a mechanism to identify which states in the state engine are valid, but in the absence of a built-in feature we can work it out in the graph features raw sql code (and hopefully call the graph from the API later to get these numbers pushed to a remote target at intervals) If you want to express test coverage of a requirement as a percentage, it is (x/y)*100 = % coverage where x = # of valid requirements covered in valid tests y = # of requirements that meet whatever requirement criteria you are using (such as a type) that represents the total valid requirements possible In order to get Y (the easy part), you can run a query like: --Count of traceable requirements SELECT COUNT(Req.REQUIREMENT_ID) AS TotalReqs FROM SpiraTestEntities.R_Requirements AS Req WHERE Req.PROJECT_ID = --your project ID AND Req.REQUIREMENT_TYPE_NAME = 'Feature' --an example of a filter on the total requirements AND Req.IS_DELETED = FALSE AND CAST(Req.CUST_17 AS INT32) = 0000000333 --another example of a filter on the total requirements In order to get X, you join across the junction table to test cases, but be careful to make sure you count DISTINCT requirement ids, otherwise you get a count of every row in the table (requirements multiplied by covering test cases). --count of valid covered traceable requirements SELECT COUNT(DISTINCT RQ.REQUIREMENT_ID) AS qualifiedReqs FROM SpiraTestEntities.R_Requirements AS RQ LEFT JOIN SpiraTestEntities.R_RequirementTestCases as RT on RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID LEFT JOIN SpiraTestEntities.R_TestCases as TC on RT.TEST_CASE_ID = TC.TEST_CASE_ID where RQ.PROJECT_ID = --your project ID AND RQ.REQUIREMENT_TYPE_NAME = 'Feature' --same filter demo as Y query above AND RQ.IS_DELETED = FALSE AND TC.TEST_CASE_STATUS_NAME = "Approved" --new tighter TC filter example to make X a fraction(a percentage) of Y AND TC.IS_DELETED = FALSE AND CAST(RQ.CUST_17 AS INT32) = 0000000333 --same second filter demo as Y query above After you have X and Y, you can build a composite query to sort the percentage: SELECT X.TotalReqs , Y.qualifiedReqs AS CoveredReqs --In the below logic, you have to multiply the values by a decimal to hint that you're looking for decimal division. , ROUND((Y.qualifiedReqs * 1.0) / (X.TotalReqs * 1.0), 2) * 100 AS PercentReqsCovered FROM --subquery is just copy of X query example above (--Count of traceable requirements SELECT COUNT(Req.REQUIREMENT_ID) AS TotalReqs FROM SpiraTestEntities.R_Requirements AS Req WHERE Req.PROJECT_ID = --your project ID AND Req.REQUIREMENT_TYPE_NAME = 'Feature' AND Req.IS_DELETED = FALSE AND CAST(Req.CUST_17 AS INT32) = 0000000333 ) AS X JOIN --subquery is just copy of y query example above (--count of valid covered traceable requirements SELECT COUNT(DISTINCT RQ.REQUIREMENT_ID) AS qualifiedReqs FROM SpiraTestEntities.R_Requirements AS RQ LEFT JOIN SpiraTestEntities.R_RequirementTestCases AS RT ON RQ.REQUIREMENT_ID = RT.REQUIREMENT_ID LEFT JOIN SpiraTestEntities.R_TestCases AS TC ON RT.TEST_CASE_ID = TC.TEST_CASE_ID WHERE RQ.PROJECT_ID = --your project ID AND RQ.REQUIREMENT_TYPE_NAME = 'Feature' AND RQ.IS_DELETED = FALSE AND TC.TEST_CASE_STATUS_NAME = "Approved" AND TC.IS_DELETED = FALSE AND CAST(RQ.CUST_17 AS INT32) = 0000000333 ) AS Y ON TRUE=TRUE --just table join nonsense need to hook the tables together

ESQL String Aggregate

Erik Swedlund (erik.swedlund@ascom.com) — Mon, 23 May 2022 15:47:57 -0400

Hello, I am trying to create an ESQL query that groups the results by the Test Case ID. For example, if I have the following table: Test Case ID Requirement 1 RQ1 1 RQ2 2 RQ3 2 RQ4 I want an ESQL query the groups the Requirements by Test Case ID and outputs: Test Case ID Requirement 1 RQ1, RQ2 2 RQ3, RQ4 For SQL, it appears that the query would be: SELECT TEST_CASE_ID, STRING_AGG(REQUIREMENT_NAME,,) AS REQUIREMENTS FROM SpiraTestEntities.R_RequirementTestCases GROUP BY TEST_CASE_ID However, ESQL removed the STRING_AGG function. Is there another way to do this?

Help to get custom field from test set level on test run report

David J (adam.sandman+support@inflectra.com) — Tue, 19 Apr 2022 11:18:48 -0400

I have one custom field on test set level that is necessary to have on test run report. Can I associate those two things?

Show parent name

David J (adam.sandman+support@inflectra.com) — Fri, 20 May 2022 17:03:49 -0400

In a custom report, I just need to show the name of the parent of a requirement. This is the code I use, but it provides no result: Can anyone clarify me why?

EPIC & User Story mapping report

jason bevis (bevisjason0405@gmail.com) — Mon, 28 Nov 2022 09:27:07 -0500

We have fetched requirement summary report, but we are not able to track which user story is mapped to which EPIC. Can you let us know is there a way we can have this in report.

Subqueries seem being not working

David J (adam.sandman+support@inflectra.com) — Mon, 24 Oct 2022 07:50:06 -0400

I created a query for test reporting where I want to show, per each release, the linked test sets, test cases, last execution run and the total number of active defects linked to each test. I designed a query with a subquery calculating the total number of defects, but the subquery shows nothing in the report (empty column), whilst it shows System.Data.Query.ResultAssembly.BridgeDataReader in the preview results section. I read about an old similar question in this forum, but the answer was not satisfying to me and was not suitable to my case. How can I write the query in ESQL in order to get the information? My sub query is colored below: SELECT RL.name AS Release, TS.release_version_number AS ReleaseVs, ... ( SELECT value COUNT(0) FROM SpiraTestEntities.R_TestCaseIncidents AS BG WHERE BG.test_case_id = TSTC.test_case_id AND BG.is_open_status = True ) AS OpenDefects FROM SpiraTestEntities.R_TestSets AS TS JOIN SpiraTestEntities.R_Releases AS RL ON TS.release_id = RL.release_id AND TS.is_deleted = False ... LEFT JOIN SpiraTestEntities.R_TestRuns AS RN ON TSTC.test_set_test_case_id = RN.test_set_test_case_id AND RN.is_deleted = False ... WHERE ... ORDER BY ... Thanks, Daniele

Header format in AddEdit custom section not recognized in report

David J (adam.sandman+support@inflectra.com) — Tue, 25 Oct 2022 11:48:40 -0400

Hello. I configured a simple header in the Add/Edit custom section of my report, by using a predefined style (selected from the list): a certain font style, size and colour. Like this: Test Report The form permits to format my string further and to do many other things. In any case, it seems I have nothing else to do (not coding) and I would expect to find the header in the output exactly as it appears in the section where I configured it. Instead, no format is applied at all, neither to HTML reports, nor Word or Excel. Where am I wrong? Thanks, Daniele

Spira Custom Reports

Bibah Ryeq (naveedmunirch@gmail.com) — Thu, 05 Jan 2023 06:34:08 -0500

We are using Spirateam / Spiratest for test case management and for managing our kayak for sale direct access to the MSSQL databaes allows custom report generation.

Requirement Summary Graph code

Leonardo Persike Martins (lpn@certi.org.br) — Wed, 15 Feb 2023 15:20:25 -0500

Dear community, I have an application that would be interesting to create a graph similar to the standard graph called Requirement Summary Graph, having the X axis set as Release and grouped by Status. Example: I was wondering if the ESQL code used to generate such graph can be shared to the community. That would be really helpful, in order to use it as a base. Thanks!

Generating Word Reports But With Customize Header And Footer

Victoria D (nino.diasamidze@inflectra.com) — Thu, 08 Jun 2023 22:58:03 -0400

Were using the Reporting facility to create Word documents. Is there a way to do the following: a) Generate customized Header and Footer b) Include a Company cover page with TOC c) Have a Cover page with Word macros

Design traceability report

Victoria D (nino.diasamidze@inflectra.com) — Thu, 13 Mar 2025 14:57:33 -0400

I need to generate the design traceability report, to prove that each requirement has been accounted for in both Software Architecture and Design. Ive create a project which is holding all requirements in there (lets assume that the project consists of only one product for the matter of the question) In this project I have 3 components: 1. Requirement 2. Architecture 3. Design The design and architecture are written in semi-Word way: each requirement (header in Word) contain text, diagrams and is associated with real requirements as Depends On. Requirement No Tasks 3 - Medium Requested Feature Requirements [RQ:2234] My Requirement No Tasks 3 - Medium Requested Feature Requirements [RQ:1790] Very hard requirement Not Covered No Tasks 3 - Medium Requested Feature Requirements [RQ:1789] Great requirement Not Covered No Tasks 3 - Medium Requested Feature Requirements [RQ:2218] Easy requirement Not Covered No Tasks 3 - Medium Requested Feature Requirements [RQ:2228] Just some requirement Not Covered No Tasks 3 - Medium Requested Feature Requirements [RQ:3148] Architecture Not Covered No Tasks 3 - Medium Requested Feature Architecture [RQ:3152] Physical View Not Covered No Tasks 3 - Medium Requested Feature Architecture [RQ:3149] Logical View Not Covered No Tasks 3 - Medium Requested Feature Architecture [RQ:3150] User View Not Covered No Tasks 3 - Medium Requested Feature Architecture [RQ:3151] Design Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3262] HAL: I2C Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3263] HAL:SPI Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3264] Safety features Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3265] Startup Sequence Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3266] Powerdown Sequence Not Covered No Tasks 3 - Medium Requested Feature Design [RQ:3267] In the end I want to get something like this: Design Traceability Report Requirement Name Architecture Design My Requirement Physical view HAL: SPI Very hard requirement Physical view HAL: I2C Great requirement Logical View Safety Features Easy requirement User View Startup Sequence Just some requirement User View Powerdown Sequence The idea is to check for Component name of the artifact linked to the requirement and then decide if it belongs to Architecture or Design column (or something else). So... if the Component is Architecture, then place the linked name into Architecture column. if it is Design, then it belongs to Design column. And if it is anything else, it should be ignored. I cant seem to find a way to get the Component from linked Artifact using XSLT., so not sure how to achieve my goal. Can anyone please help?

Tester Productivity

David J (adam.sandman+support@inflectra.com) — Thu, 06 Nov 2025 23:13:03 -0500

Hi, Has anyone created a Tester Productivity Report? Im looking to track the testers activity in SpiraPlan daily. I can see activity in the Product Association page but would like the ability to be able to generate a report for management. Thanks Gloria

custom report to display all user ids

Victoria D (nino.diasamidze@inflectra.com) — Mon, 03 Feb 2025 09:42:38 -0500

need help to create custom report to display all user ids, status of user ids(active/inactive), last time they login to the system and days they are not using their user ids.

Multiselect list values displaying in custom

James Rivera (James271Rivera@outlook.com) — Fri, 16 Jan 2026 04:14:00 -0500

Hello, I have been trying to modify a custom report which throws an error due to a multiselect list custom propery from test runs and have encountered a similar issue as described in the forum below: https://www.inflectra.com/Support/Forum/spirateam/reports/2265.aspx Ive reviewed the KB that was linked in the responses to the forum ( https://www.inflectra.com/Support/KnowledgeBase/KB638.aspx ) and have attempted to include the join and the Contains, EndsWith and StartsWith set of functions to the report query. The report is no longer throwing the error when generated, however it is only displaying the first value when multiple values are selected. The KB seems to describe creating a graph and Im wondering if since Im updating a report instead does the XSLT template require a modification as well to allow these values to display separated by commas? Here is the original query that was causing errors due to the custom property being a multiselect list: --pulling in the Environment information from the LOV left join (select SubC.CUSTOM_PROPERTY_VALUE_ID, SubC.NAME from SpiraTestEntities.R_CustomListValues as SubC where SubC.PROJECT_ID = ${ProjectId} and SubC.IS_DELETED = False) as CB on CAST(R.CUST_02 as INT32) = CB.CUSTOM_PROPERTY_VALUE_ID Here is what it was updated to: --pulling in the Environment information from the LOV left join (select SubC.CUSTOM_PROPERTY_VALUE_ID, SubC.NAME from SpiraTestEntities.R_CustomListValues as SubC where SubC.PROJECT_ID = ${ProjectId} and SubC.IS_DELETED = False) as CB on Contains(R.CUST_02, Concat(Concat(0, CAST(CB.CUSTOM_PROPERTY_VALUE_ID as Edm.String)), ,)) or EndsWith(R.CUST_02, Concat(0, CAST(CB.CUSTOM_PROPERTY_VALUE_ID as Edm.String))) or StartsWith(R.CUST_02, CAST(CB.CUSTOM_PROPERTY_VALUE_ID as Edm.String)) Also the XML code for the XSLT template for the value: Environment Thank you.