SQL query to extract all manual test cases from HP ALM

If  you have used HP ALM tool to store your test cases and when you want to download all test cases in one excel file at the end then it's not so easy to do that activity.

We can overcome that with below sql query by running in Analysis dashboard tab with Excel Report option in ALM.
This activity has to be completed in 2 phases.

1. To get absolute path of folder from where you would like to download test cases.

'''--- to get folder absolute path---

SELECT  AL_DESCRIPTION, /* Level3 Folder Name */ AL_ABSOLUTE_PATH

FROM All_Lists Where AL_ABSOLUTE_PATH

2. Place the absolute path extracted from the above SQL query in below

'''''---- to extract test cases from specific to folder

SELECT

TEST.TS_TEST_ID as "Test ID" /*Test.Test ID*/,

TEST.TS_NAME as "Test Name" /*Test.Test Name*/,

TEST.TS_STATUS as "Status" /*Test.Status*/,

TEST.TS_USER_27 as "Priority" /*Test.Test Case Priority*/,

TEST.TS_USER_44 as "Module" /*Test.Module*/,

ALL_LISTS.AL_DESCRIPTION as "Folder Name" /*Test Plan Folder.Name*/,

TEST.TS_RESPONSIBLE as "Tester" /*Test.Designer*/,

TEST.TS_DESCRIPTION as "Test Description"/*Test.Description*/,

DESSTEPS.DS_STEP_NAME as "Test Step Name" /*Test Step.Step Name*/,

DESSTEPS.DS_DESCRIPTION as "Step Description"/*Test Step.Description*/,

DESSTEPS.DS_EXPECTED as "Expected Results" /*Test Step.Expected*/

From TEST

Join ALL_LISTS On TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID

Join DESSTEPS On TEST.TS_TEST_ID=DESSTEPS.DS_TEST_ID

Where All_Lists.AL_ABSOLUTE_PATH like'AAAAAPAAAAAK%'

ORDER BY TEST.TS_TEST_ID, DESSTEPS.DS_STEP_ORDER