When Legacy Excel Reports were enabled in ALM 11.00, I was able to create Test Plan and Test Lab reports with a column for the hierarchical folder tree path, so I could then sort the results by folder path. I Googled around and found that DQL is mostly ASNI SQL 9.2 (http://alm-help.saas.hpe.com/en/12.50/online_help/Content/Admin/cust_business_views_dql.htm) and found an article on an alternate way to write it (https://technology.amis.nl/2009/09/01/oracle-rdbms-11gr2-goodbye-connect-by-or-the-end-of-hierarchic…), but I’m at a loss for how to re-write it from SQL.

So how do I recursively create the folder path using DQL in Business Views? Below are the SQL-based queries I used to be able to run in SQL.

=======================================================
Test Cases By Folder (Test Plan)
=======================================================
SELECT
Path as "Folder Path",
TS_NAME as "Test Name",
TS_EXEC_STATUS as "Execution Status"
FROM
(SELECT
AL_ITEM_ID,
SYS_CONNECT_BY_PATH(AL_DESCRIPTION, ‘\\’) Path
FROM
ALL_LISTS
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
START WITH AL_FATHER_ID = 0 AND AL_DESCRIPTION = ‘Subject’)
LEFT JOIN TEST ON TS_SUBJECT = AL_ITEM_ID
ORDER BY Path, TS_NAME

=======================================================
Execution Report (Test Lab)
=======================================================
SELECT
TC_CYCLE_ID as "Test Set ID",
TC_TESTCYCL_ID as "Test Instance ID",
TC_TEST_ORDER as "Test Instance Order",
SUBSTR((SELECT TABLEPATH.PTH FROM
(SELECT in_cf.CF_ITEM_ID, sys_connect_by_path(in_cf.CF_ITEM_NAME,’\\’) PTH
FROM CYCL_FOLD in_cf CONNECT BY PRIOR in_cf.CF_ITEM_ID = in_cf.CF_FATHER_ID
START WITH in_cf.CF_FATHER_ID = 0) TABLEPATH
LEFT JOIN CYCLE in_cy on (in_cy.CY_FOLDER_ID = TABLEPATH.CF_ITEM_ID)
WHERE in_cy.CY_CYCLE_ID = ts.CY_CYCLE_ID), 0,
INSTR((SELECT TABLEPATH.PTH FROM
(SELECT in_cf.CF_ITEM_ID, sys_connect_by_path(in_cf.CF_ITEM_NAME,’\\’) PTH
FROM CYCL_FOLD in_cf CONNECT BY PRIOR in_cf.CF_ITEM_ID = in_cf.CF_FATHER_ID
START WITH in_cf.CF_FATHER_ID = 0) TABLEPATH
LEFT JOIN CYCLE in_cy on (in_cy.CY_FOLDER_ID = TABLEPATH.CF_ITEM_ID)
WHERE in_cy.CY_CYCLE_ID = ts.CY_CYCLE_ID), ‘\\’, -1)) as "Folder Path",
CF_ITEM_NAME as "Test Scenario",
CY_CYCLE as "Test Set",
TS_NAME as "Test Case",
TC_STATUS as "Status",
TC_TESTER_NAME as "Responsible Tester",
TC_PLAN_SCHEDULING_DATE as "Planned Exec Date",
TC_ACTUAL_TESTER as "Actual Tester",
TC_EXEC_DATE as "Actual Exec Date",
FROM TESTCYCL tstest
JOIN CYCLE ts ON ts.CY_CYCLE_ID = tstest.TC_CYCLE_ID
JOIN CYCL_FOLD tsfolder ON tsfolder.CF_ITEM_ID = ts.CY_FOLDER_ID
JOIN TEST pt ON pt.TS_TEST_ID = tstest.TC_TEST_ID
WHERE CF_ASSIGN_RCYC = 1021
ORDER BY "Folder Path", CY_CYCLE, TC_TEST_ORDER