Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > SpiraTeam Issues & Qu... > Custom Reporting Queries

Custom Reporting Queries

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

Wednesday, October 21, 2015

Robbie

re: Robbie Wednesday, October 21, 2015

Or is there a guide as to how to build queries like this?

Wednesday, October 21, 2015

Robbie

re: Robbie 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

inflectra.kat

re: Robbie 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

Robbie

re: inflectra.kat 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

inflectra.kat

re: Robbie 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

inflectra.kat

re: Robbie 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}

Spira Helps You Deliver Quality Software, Faster and With Lower Risk

And if you have any questions, please email or call us at +1 (202) 558-6885

Statistics

Started: Wednesday, October 21, 2015
Last Reply: Friday, November 6, 2015
Replies: 6
Views: 2981

Custom Reporting Queries

Spira Helps You Deliver Quality Software, Faster and With Lower Risk

Edit Thread

Edit Message

Search Results