Sorting test scripts by linked defects

[Zbyrd]

Hi

I've been using QC for about a week now and I'm still getting used to it. I'm trying to create a report that sort ALL the test scripts in QC by their linked defects.

Is that possible? I would greatly appreciate any help.

Thank you!

[Zbyrd]

I'm using QC 10 if that helps. So I need a report that shows all test scripts in qc with linked defects.

[Trudy Claspill]

Review the information in the documentation about the pre-built reports found under the Analysis menu, and the documentation regarding the Dashboard module, where custom reports can be created.

[Warriorary]

Hi,

If you are want to extract a report of defects link to Test Scripts in QC 10.0. 

1) Go To dashboard

2) Choose a New Excel report

3) In Configuration Tab Under query post the below query It might help -

SELECT  BUG.BG_BUG_ID As "Defect ID",
        BUG.BG_SUMMARY As "Defect Summary",
        BUG.BG_PRIORITY As "Defect Priority",
        TEST.TS_NAME As "Test Name",
        TEST.TS_TEST_ID As "Test ID",
         LINK.LN_ENTITY_TYPE As "Entity Type"

FROM   LINK, BUG, TEST
Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID
and    LINK.LN_ENTITY_TYPE = 'TEST'
and    LINK.LN_ENTITY_ID = TEST.TS_TEST_ID

Union
SELECT  BUG.BG_BUG_ID As "Defect ID",
        BUG.BG_SUMMARY As "Defect Summary",
        BUG.BG_PRIORITY As "Defect Priority",
        BUG.BG_USER_01 As "Defect Status",
        BUG.BG_USER_02  As "Region",
        BUG.BG_USER_03 As "Assigned Team",
        TEST.TS_NAME As "Test Name",
         LINK.LN_ENTITY_TYPE As "Entity Type"

FROM   LINK, BUG, TEST, DESSTEPS
Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID
and    LINK.LN_ENTITY_TYPE = 'STEP'
and    LINK.LN_ENTITY_ID = DESSTEPS.DS_ID
and    DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID

Union
SELECT  BUG.BG_BUG_ID As "Defect ID",
        BUG.BG_SUMMARY As "Defect Summary",
        BUG.BG_PRIORITY As "Defect Priority",
        BUG.BG_USER_01 As "Defect Status",
        BUG.BG_USER_02 As "Region",
        BUG.BG_USER_03 As "Assigned Team",
        TEST.TS_NAME As "Test Name",
        TEST.TS_USER_02 As "Defect Phase",
        LINK.LN_ENTITY_TYPE As "Entity Type"

FROM   LINK, BUG, TEST, RUN
Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID
and    LINK.LN_ENTITY_TYPE = 'RUN'
and    LINK.LN_ENTITY_ID = RUN.RN_RUN_ID
and    RUN.RN_TEST_ID = TEST.TS_TEST_ID

Union
SELECT  BUG.BG_BUG_ID As "Defect ID",
        BUG.BG_SUMMARY As "Defect Summary",
        BUG.BG_PRIORITY As "Defect Priority",
        BUG.BG_USER_01 As "Defect Status",
        BUG.BG_USER_02 As "Region",
        BUG.BG_USER_03 As "Assigned Team",
        TEST.TS_NAME As "Test Name",
        TEST.TS_USER_02 As "Defect Phase",
         LINK.LN_ENTITY_TYPE As "Entity Type"

FROM   LINK, BUG, TEST, TESTCYCL
Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID
and    LINK.LN_ENTITY_TYPE = 'TESTCYCL'
and    LINK.LN_ENTITY_ID = TESTCYCL.TC_TESTCYCL_ID
and    TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID

[Zbyrd]

Thanks for all the help, but the script isn't working. Is there a way to differentiate between a defect associate with a test case and a test script in a defect? Is there a specific filter that needs to be used?

[BetsyDroege]

Hi, I've modified the query parts slightly to acheive the fields that I need, but the Union bit is causing QC to throw an error.

My Query:

SELECT  BUG.BG_BUG_ID As "Defect ID",

        BUG.BG_SUMMARY As "Defect Summary",

        BUG.BG_USER_02 as "Defect Item Type",

        BUG.BG_USER_28 as "Defect Item Sub Type",

        BUG.BG_USER_22 As "Defect Team Ranking",

        BUG.BG_SEVERITY as "Defect Severity",

        BUG.BG_USER_21 as "Defect Project #",

        BUG.BG_STATUS as "Defect Status",

        BUG.BG_USER_53 as "Theme",

        TEST.TS_NAME As "Test Name",

        TEST.TS_TEST_ID As "Test ID",

        LINK.LN_ENTITY_TYPE As "Entity Type",

        TEST.TS_TEST_ID  as "Test ID",

        TEST.TS_EXEC_STATUS as "Test Execution Status"

FROM   LINK, BUG, TEST

Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID

and    LINK.LN_ENTITY_TYPE = 'TEST'

and    LINK.LN_ENTITY_ID = TEST.TS_TEST_ID

and    BUG.BG_USER_21='141022'

and    BUG.BG_USER_20='Open'

Union

SELECT  BUG.BG_BUG_ID As "Defect ID",

        BUG.BG_SUMMARY As "Defect Summary",,

        BUG.BG_USER_02 as "Defect Item Type",

        BUG.BG_USER_28 as "Defect Item Sub Type",

        BUG.BG_USER_22 As "Defect Team Ranking",

        BUG.BG_SEVERITY as "Defect Severity",

        BUG.BG_USER_21 as "Defect Project #",

        BUG.BG_STATUS as "Defect Status",

        BUG.BG_USER_53 as "Theme",

        TEST.TS_NAME As "Test Name",

        LINK.LN_ENTITY_TYPE As "Entity Type",

        TEST.TS_TEST_ID  as "Test ID",

        TEST.TS_EXEC_STATUS as "Test Execution Status"

FROM   LINK, BUG, TEST, DESSTEPS

Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID

and    LINK.LN_ENTITY_TYPE = 'STEP'

and    LINK.LN_ENTITY_ID = DESSTEPS.DS_ID

and    DESSTEPS.DS_TEST_ID = TEST.TS_TEST_ID

and    BUG.BG_USER_21='141022'

and    BUG.BG_USER_20='Open'

Union

SELECT  BUG.BG_BUG_ID As "Defect ID",

        BUG.BG_SUMMARY As "Defect Summary",,

        BUG.BG_USER_02 as "Defect Item Type",

        BUG.BG_USER_28 as "Defect Item Sub Type",

        BUG.BG_USER_22 As "Defect Team Ranking",

        BUG.BG_SEVERITY as "Defect Severity",

        BUG.BG_USER_21 as "Defect Project #",

        BUG.BG_STATUS as "Defect Status",

        BUG.BG_USER_53 as "Theme",

        TEST.TS_NAME As "Test Name",

        LINK.LN_ENTITY_TYPE As "Entity Type",

        TEST.TS_TEST_ID  as "Test ID",

        TEST.TS_EXEC_STATUS as "Test Execution Status"

FROM   LINK, BUG, TEST, RUN

Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID

and    LINK.LN_ENTITY_TYPE = 'RUN'

and    LINK.LN_ENTITY_ID = RUN.RN_RUN_ID

and    RUN.RN_TEST_ID = TEST.TS_TEST_ID

and    BUG.BG_USER_21='141022'

and    BUG.BG_USER_20='Open'

Union

SELECT  BUG.BG_BUG_ID As "Defect ID",

        BUG.BG_SUMMARY As "Defect Summary",,

        BUG.BG_USER_02 as "Defect Item Type",

        BUG.BG_USER_28 as "Defect Item Sub Type",

        BUG.BG_USER_22 As "Defect Team Ranking",

        BUG.BG_SEVERITY as "Defect Severity",

        BUG.BG_USER_21 as "Defect Project #",

        BUG.BG_STATUS as "Defect Status",

        BUG.BG_USER_53 as "Theme",

        TEST.TS_NAME As "Test Name",

        LINK.LN_ENTITY_TYPE As "Entity Type",

        TEST.TS_TEST_ID  as "Test ID",

        TEST.TS_EXEC_STATUS as "Test Execution Status"

FROM   LINK, BUG, TEST, TESTCYCL

Where  LINK.LN_BUG_ID = BUG.BG_BUG_ID

and    LINK.LN_ENTITY_TYPE = 'TESTCYCL'

and    LINK.LN_ENTITY_ID = TESTCYCL.TC_TESTCYCL_ID

and    TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID

and    BUG.BG_USER_21='141022'

and    BUG.BG_USER_20='Open'

Error Message:

"The SQL query <Query1> has failed to run, with the following error: 

[Mercury][Oracle JDBC Driver][Oracle]ORA-00936:missing expression 

..."

Any help that you can provide for me to get this running I would appreciate.