Custom Reporting Queries

Wednesday, October 21, 2015 11:31:00 AM
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
Wednesday, October 21, 2015 11:36:48 AM
Avatar
re: Robbie on Wednesday, October 21, 2015 11:31:00 AM
Or is there a guide as to how to build queries like this?
Wednesday, October 21, 2015 2:18:59 PM
Avatar
re: Robbie on 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?
Wednesday, October 21, 2015 5:57:24 PM
Avatar
re: Robbie on 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
Thursday, October 22, 2015 11:44:32 AM
Avatar
re: inflectra.elise on Wednesday, October 21, 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?
Thursday, October 22, 2015 5:17:00 PM
Avatar
re: Robbie on Thursday, October 22, 2015
Hi Robbie,

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

Thanks!
Elise
Friday, November 6, 2015 4:33:18 PM
Avatar
re: Robbie on Thursday, October 22, 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}
Statistics
  • Started: Wednesday, October 21, 2015 11:31:00 AM
  • Last Reply: Friday, November 6, 2015 4:33:18 PM
  • Replies: 6
  • Views: 427