Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > SpiraTeam Issues & Qu... > Reporting queries... or a...

Reporting queries... or a "Spira db for Dummies"

Friday, January 3, 2014
reporting
esql
queries
Avatar

Where is the best place to find a reference guide to the Spira db?

We have created a number of Reporting queries, usually pretty straightforward ones, but we are curious about what else we could create too if we knew more about the db.

Something fully searchable, that shows all the tables/fields/links etc. Even if it's based on an older version then it'd give us a good basic model.
(To Reply to this thread you first need to Sign-In )
3 Replies
Friday, January 3, 2014
Avatar
inflectra.jimx
 re: astrium_tm Friday, January 3, 2014
Hi Andy

I have checked and we don't currently have a reference guide for the reportable entities that are queryable from within Spira. The best reference in terms of the data and fields is just to add the entity to the query using the default select value R from ..... syntax and then in the Preview Results window you'll see all the columns/fields and a sample of the types of data returned.

Regards

Jim
Wednesday, January 8, 2014
Avatar
re: inflectra.jimx Friday, January 3, 2014

SpiraTeam DB is different from what is reportable from the reporting module.  It is easy to find the fields through the SpiraTeam Query and copy paste and transpose to excel to available fields.  I hope that helps.

 

Incidents Fields - R.Incidents Test Run Fields - R_TestRuns  Test Case Fields - R_TestCases History Change Set  - Fields User Fields - R_Users Test Step Fields - R.TestSteps Comment Fields - R_Comments Requirement Fields - R_Requirements  Test Set Fields - R_TestSets  Release Fields - R_Releases  Project Groups Fields - R_ProjectGroups Requirement Incidents - R_RequirementIncidents Test Run Step fields - R_TestRunSteps  History Details - R_HistoryDetails Task Fields - R_Tasks
INCIDENT_ID TEST_RUN_ID TEST_CASE_ID CHANGESET_ID USER_ID TEST_STEP_ID ARTIFACT_TYPE_ID REQUIREMENT_ID TEST_SET_ID RELEASE_ID PROJECT_GROUP_ID REQUIREMENT_ID TEST_RUN_STEP_ID ARTIFACT_HISTORY_ID TASK_ID
PROJECT_ID TEST_CASE_ID EXECUTION_STATUS_ID USER_ID USER_NAME TEST_CASE_ID ARTIFACT_ID AUTHOR_ID PROJECT_ID PROJECT_ID NAME INCIDENT_ID EXECUTION_STATUS_ID FIELD_NAME TASK_STATUS_ID
PRIORITY_ID PROJECT_ID TEST_CASE_PRIORITY_ID ARTIFACT_TYPE_ID EMAIL_ADDRESS EXECUTION_STATUS_ID CREATOR_ID OWNER_ID RELEASE_ID CREATOR_ID DESCRIPTION DETECTED_RELEASE_ID TEST_CASE_ID OLD_VALUE PROJECT_ID
SEVERITY_ID NAME PROJECT_ID ARTIFACT_ID IS_ACTIVE DESCRIPTION COMMENT_TEXT RELEASE_ID TEST_SET_STATUS_ID NAME ACTIVE_YN OPEN_STATUS_YN TEST_STEP_ID FIELD_CAPTION REQUIREMENT_ID
INCIDENT_STATUS_ID DESCRIPTION AUTHOR_ID CHANGE_DATE CREATION_DATE LINKED_TEST_CASE_ID CREATION_DATE PROJECT_ID CREATOR_ID VERSION_NUMBER DEFAULT_YN   TEST_RUN_ID NEW_VALUE RELEASE_ID
INCIDENT_TYPE_ID ESTIMATED_DURATION NAME CHANGETYPE_ID LDAP_DN POSITION CREATOR_NAME SCOPE_LEVEL_ID OWNER_ID DESCRIPTION WEBSITE   DESCRIPTION OLD_VALUE_INT CREATOR_ID
OPENER_ID ACTUAL_DURATION OWNER_ID PROJECT_ID FIRST_NAME EXPECTED_RESULT ARTIFACT_TYPE_NAME IMPORTANCE_ID AUTOMATION_HOST_ID INDENT_LEVEL     POSITION OLD_VALUE_DATE OWNER_ID
OWNER_ID TEST_RUN_TYPE_ID INDENT_LEVEL REVERT_ID LAST_NAME SAMPLE_DATA   NAME TEST_RUN_TYPE_ID CREATION_DATE     EXPECTED_RESULT NEW_VALUE_INT TASK_PRIORITY_ID
TEST_RUN_STEP_ID TESTER_ID DESCRIPTION ARTIFACT_DESC MIDDLE_INITIAL ATTACHMENTS_YN   CREATION_DATE RECURRENCE_ID LAST_UPDATE_DATE     SAMPLE_DATA NEW_VALUE_DATE NAME
INCIDENT_NAME EXECUTION_STATUS_ID EXECUTION_DATE CHANGETYPE_NAME DEPARTMENT LAST_UPDATE_DATE   INDENT_LEVEL NAME ACTIVE_YN     ACTUAL_RESULT CHANGESET_ID DESCRIPTION
DESCRIPTION START_DATE CREATION_DATE USER_NAME LAST_UPDATE_DATE IS_DELETED   DESCRIPTION DESCRIPTION SUMMARY_YN     EXECUTION_STATUS_NAME FIELD_ID CREATION_DATE
CREATION_DATE END_DATE LAST_UPDATE_DATE ARTIFACT_TYPE_NAME TIMEZONE EXECUTION_STATUS_NAME   LAST_UPDATE_DATE INDENT_LEVEL ATTACHMENTS_YN     TEST_CASE_NAME CUSTOM_PROPERTY_ID LAST_UPDATE_DATE
CLOSED_DATE RUNNER_NAME FOLDER_YN   LAST_OPENED_PROJECT_ID TEST_CASE_NAME   SUMMARY_YN CREATION_DATE ITERATION_YN     PROJECT_ID ARTIFACT_ID START_DATE
LAST_UPDATE_DATE RUNNER_TEST_NAME ACTIVE_YN     PROJECT_ID   ATTACHMENTS_YN PLANNED_DATE START_DATE     PROJECT_NAME USER_ID END_DATE
DETECTED_RELEASE_ID RUNNER_ASSERT_COUNT AUTOMATION_ENGINE_ID     PROJECT_NAME   COVERAGE_COUNT_TOTAL LAST_UPDATE_DATE END_DATE     PROJECT_GROUP_ID ARTIFACT_TYPE_ID COMPLETION_PERCENT
RESOLVED_RELEASE_ID RUNNER_MESSAGE AUTOMATION_ATTACHMENT_ID     PROJECT_GROUP_ID   COVERAGE_COUNT_PASSED FOLDER_YN RESOURCE_COUNT       CHANGE_DATE ESTIMATED_EFFORT
ATTACHMENTS_YN RUNNER_STACK_TRACE FOLDER_COUNT_PASSED     CUST_01   COVERAGE_COUNT_FAILED ATTACHMENTS_YN DAYS_NON_WORKING       CHANGER_NAME ACTUAL_EFFORT
START_DATE ATTACHMENTS_YN FOLDER_COUNT_FAILED     CUST_02   COVERAGE_COUNT_CAUTION IS_DELETED PLANNED_EFFORT       CHANGE_NAME ATTACHMENTS_YN
COMPLETION_PERCENT EXECUTION_STATUS_NAME ATTACHMENTS_YN     CUST_03   COVERAGE_COUNT_BLOCKED CONCURRENCY_DATE AVAILABLE_EFFORT       CHANGETYPE_ID PROJECTED_EFFORT
ESTIMATED_EFFORT TESTER_NAME TEST_STEPS_YN     CUST_04   PLANNED_EFFORT RELEASE_VERSION_NUMBER COUNT_BLOCKED         REMAINING_EFFORT
ACTUAL_EFFORT TEST_RUNS_PENDING_ID FOLDER_COUNT_CAUTION     CUST_05   TASK_COUNT PROJECT_NAME COUNT_CAUTION         IS_DELETED
REMAINING_EFFORT RELEASE_ID FOLDER_COUNT_BLOCKED     CUST_06   TASK_ESTIMATED_EFFORT TEST_CASE_COUNT COUNT_FAILED         TASK_STATUS_NAME
PROJECTED_EFFORT TEST_SET_ID FOLDER_COUNT_NOT_RUN     CUST_07   TASK_ACTUAL_EFFORT TEST_SET_STATUS_NAME COUNT_NOT_APPLICABLE         OWNER_NAME
IS_DELETED TEST_SET_TEST_CASE_ID FOLDER_COUNT_NOT_APPLICABLE     CUST_08   TASK_PROJECTED_EFFORT CREATOR_NAME COUNT_NOT_RUN         CREATOR_NAME
BUILD_ID RELEASE_NAME ESTIMATED_DURATION     CUST_09   TASK_REMAINING_EFFORT OWNER_NAME COUNT_PASSED         TASK_PRIORITY_NAME
VERIFIED_RELEASE_ID RELEASE_VERSION_NUMBER IS_DELETED     CUST_10   TASK_PERCENT_ON_TIME PROJECT_ACTIVE_YN TASK_COUNT         PROJECT_NAME
PRIORITY_NAME TEST_SET_NAME EXECUTION_STATUS_NAME     CUST_11   TASK_PERCENT_LATE_FINISH AUTOMATION_HOST_NAME TASK_PERCENT_ON_TIME         PROJECT_GROUP_ID
PRIORITY_COLOR TEST_RUN_TYPE_NAME TEST_CASE_PRIORITY_NAME     CUST_12   TASK_PERCENT_NOT_START TEST_RUN_TYPE_NAME TASK_PERCENT_LATE_FINISH         RELEASE_VERSION_NUMBER
SEVERITY_NAME AUTOMATION_HOST_ID AUTHOR_NAME     CUST_13   TASK_PERCENT_LATE_START RECURRENCE_NAME TASK_PERCENT_NOT_START         CUST_01
SEVERITY_COLOR AUTOMATION_HOST_NAME OWNER_NAME     CUST_14   IS_DELETED PROJECT_GROUP_ID TASK_PERCENT_LATE_START         CUST_02
INCIDENT_STATUS_NAME AUTOMATION_ENGINE_ID AUTOMATION_ENGINE_NAME     CUST_15   SCOPE_LEVEL_NAME CUST_01 TASK_ESTIMATED_EFFORT         CUST_03
INCIDENT_STATUS_OPEN_STATUS_YN BUILD_ID PROJECT_NAME     CUST_16   AUTHOR_NAME CUST_02 TASK_ACTUAL_EFFORT         CUST_04
INCIDENT_TYPE_NAME BUILD_NAME PROJECT_GROUP_ID     CUST_17   OWNER_NAME CUST_03 TASK_PROJECTED_EFFORT         CUST_05
OPENER_NAME TEST_RUN_FORMAT_ID CUST_01     CUST_18   IMPORTANCE_NAME CUST_04 TASK_REMAINING_EFFORT         CUST_06
OWNER_NAME PROJECT_NAME CUST_02     CUST_19   RELEASE_VERSION_NUMBER CUST_05 IS_DELETED         CUST_07
DETECTED_RELEASE_VERSION_NUMBER PROJECT_GROUP_ID CUST_03     CUST_20   PROJECT_NAME CUST_06 CREATOR_NAME         CUST_08
RESOLVED_RELEASE_VERSION_NUMBER CUST_01 CUST_04     CUST_21   PROJECT_GROUP_ID CUST_07 FULL_NAME         CUST_09
VERIFIED_RELEASE_VERSION_NUMBER CUST_02 CUST_05     CUST_22   CUST_01 CUST_08 PROJECT_NAME         CUST_10
PROJECT_ACTIVE_YN CUST_03 CUST_06     CUST_23   CUST_02 CUST_09 PROJECT_GROUP_ID         CUST_11
PROJECT_GROUP_ID CUST_04 CUST_07     CUST_24   CUST_03 CUST_10 CUST_01         CUST_12
INCIDENT_TYPE_ISSUE_YN CUST_05 CUST_08     CUST_25   CUST_04 CUST_11 CUST_02         CUST_13
BUILD_NAME CUST_06 CUST_09     CUST_26   CUST_05 CUST_12 CUST_03         CUST_14
INCIDENT_TYPE_RISK_YN CUST_07 CUST_10     CUST_27   CUST_06 CUST_13 CUST_04         CUST_15
PROJECT_NAME CUST_08 CUST_11     CUST_28   CUST_07 CUST_14 CUST_05         CUST_16
CUST_01 CUST_09 CUST_12     CUST_29   CUST_08 CUST_15 CUST_06         CUST_17
CUST_02 CUST_10 CUST_13     CUST_30   CUST_09 CUST_16 CUST_07         CUST_18
CUST_03 CUST_11 CUST_14         CUST_10 CUST_17 CUST_08         CUST_19
CUST_04 CUST_12 CUST_15         CUST_11 CUST_18 CUST_09         CUST_20
CUST_05 CUST_13 CUST_16         CUST_12 CUST_19 CUST_10         CUST_21
CUST_06 CUST_14 CUST_17         CUST_13 CUST_20 CUST_11         CUST_22
CUST_07 CUST_15 CUST_18         CUST_14 CUST_21 CUST_12         CUST_23
CUST_08 CUST_16 CUST_19         CUST_15 CUST_22 CUST_13         CUST_24
CUST_09 CUST_17 CUST_20         CUST_16 CUST_23 CUST_14         CUST_25
CUST_10 CUST_18 CUST_21         CUST_17 CUST_24 CUST_15         CUST_26
CUST_11 CUST_19 CUST_22         CUST_18 CUST_25 CUST_16         CUST_27
CUST_12 CUST_20 CUST_23         CUST_19 CUST_26 CUST_17         CUST_28
CUST_13 CUST_21 CUST_24         CUST_20 CUST_27 CUST_18         CUST_29
CUST_14 CUST_22 CUST_25         CUST_21 CUST_28 CUST_19         CUST_30
CUST_15 CUST_23 CUST_26         CUST_22 CUST_29 CUST_20          
CUST_16 CUST_24 CUST_27         CUST_23 CUST_30 CUST_21          
CUST_17 CUST_25 CUST_28         CUST_24   CUST_22          
CUST_18 CUST_26 CUST_29         CUST_25   CUST_23          
CUST_19 CUST_27 CUST_30         CUST_26   CUST_24          
CUST_20 CUST_28 CONCURRENCY_DATE         CUST_27   CUST_25          
CUST_21 CUST_29           CUST_28   CUST_26          
CUST_22 CUST_30           CUST_29   CUST_27          
CUST_23             CUST_30   CUST_28          
CUST_24                 CUST_29          
CUST_25                 CUST_30          
CUST_26                            
CUST_27                            
CUST_28                            
CUST_29                            
CUST_30                        
Wednesday, January 8, 2014
Avatar
re: dgee Tuesday, January 7, 2014

I stand corrected. The reporting objects can also be found in the SpiraTeamDB in the "Views" section.

 

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: Friday, January 3, 2014
  • Last Reply: Wednesday, January 8, 2014
  • Replies: 3
  • Views: 10486
Tel: 1-866-572-5878 | Help Desk | Legal Notices | Privacy Policy
Powered by KronoDesk v4.0.1 help desk software | © Copyright 2006-2024 | Licensed to Inflectra Corporation.