Thread

Skip Navigation LinksForums > SpiraTeam Forums > SpiraTeam Issues & Questi... > Custom Reporting Queries

Custom Reporting Queries RSS Feed

Wednesday, October 21, 2015
I have modified the standard requirements summary report to give the output I require for a specific project. However, I now want this same template to pull the same information from all projects or all all projects in a specific group, however I am struggling with the query detail. I tried replicating the standard report as a starting point using the requirements query below:

select value R from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId}

But using the query above, the report only returns the standard fields, it does not include the custom fields and does not populate any data from the project.

Can you tell me what the standard query is for the Requirements Summary Report and I can use this as a basis to modify further?


Thanks,
Robbie
6 Replies
Robbie ShortallRobbie Shortall
re: Robbie Shortall on Wednesday, October 21, 2015
Wednesday, October 21, 2015
Or is there a guide as to how to build queries like this?
Robbie ShortallRobbie Shortall
re: Robbie Shortall on Wednesday, October 21, 2015
Wednesday, October 21, 2015
OK, so further to this, I can use select value R from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId} and the default template it supplies to get the information I need. I can modify this as necessary with specific fields, etc but I'm having trouble with the custom fields area here. Any custom field that is a "list" returns the id number rather than the text value. Other custom fields like date and text return the values.

This example of custom field 30 returns the id for each possible selection rather than the value (or text name) of the selection: <td><xsl:value-of select="CUST_30"/></td> 

How can I modify this so that it will return the list value in text as opposed to the id number?
Elise B.Elise B.
re: Robbie Shortall on Wednesday, October 21, 2015
Wednesday, October 21, 2015
Hi Robbie,

Yes, there is a Knowledge Base article that addresses your specific question about getting the actual values for custom lists. See https://www.inflectra.com/Support/KnowledgeBase/KB74.aspx.

You can also find three general articles about customizing reports on our blog. Here is a link to the first one: Writing Custom Reports with Spira (Part 1).

Let us know if you have any further questions.

Regards,
Elise
Robbie ShortallRobbie Shortall
re: Elise B. on Wednesday, October 21, 2015
Thursday, October 22, 2015
Thanks Elise, I used the knowledge base article to build this:

select R.PROJECT_NAME, R.NAME, R.RELEASE_VERSION_NUMBER, R.OWNER_NAME, R.CUST_30 from SpiraTestEntities.R_Requirements as R inner join SpiraTestEntities.R_CustomListValues as C on R.CUST_30 = cast (C.CUSTOM_PROPERTY_VALUE_ID as string) where R.PROJECT_GROUP_ID = ${ProjectGroupId}

However, it still returns the 'id' value rather than the 'NAME' value from the column in CustomListValues. I have tried various ways of adding C.NAME, etc to try and get it to appear but none seem to work.

Could you offer any assistance here?
Elise B.Elise B.
re: Robbie Shortall on Thursday, October 22, 2015
Thursday, October 22, 2015
Hi Robbie,

Since this is getting very specific, could you please log a Help Desk ticket?

Thanks!
Elise
Elise B.Elise B.
re: Robbie Shortall on Thursday, October 22, 2015
Friday, November 6, 2015
Following-up on this thread. The solution was to add the highlighted section to the query:

select R.PROJECT_NAME, R.NAME, R.RELEASE_VERSION_NUMBER, R.OWNER_NAME, R.CUST_30, C.NAME as CUSTOM_PROPERTY_VALUE_NAME from SpiraTestEntities.R_Requirements as R inner join SpiraTestEntities.R_CustomListValues as C on R.CUST_30 = cast (C.CUSTOM_PROPERTY_VALUE_ID as string) where R.PROJECT_GROUP_ID = ${ProjectGroupId}
Tagged
Statistics
  • Started: 10/21/2015
  • Last Reply: 11/6/2015
  • Replies: 6
  • Views: 358
Powered by KronoDesk v1.1.0.15 | © Copyright Inflectra Corporation 2011-2016 | Licensed to Inflectra Corporation.