Excel Report – Test with full tree path

  • Questions
  • Excel Report - Test with full tree path
Question ID: 107107
0
0

I was looking to build an Excel Report and would like to be able to include the full tree path in the report. I have found that I can use the ALL_LISTS.AL_ABSOLUTE_PATH to filter to the specific folder, but I am not able to find anywhere to extract the actual path from the tree using SQL. We are on SQL Server 2014, BTW. Does anyone have any suggestions?

Marked as spam
Posted by (Questions: 122, Answers: 3)
Asked on September 22, 2016 3:49 pm
30 views
Answers (1)
0
Private answer

You can do this using a hierarchical query. An example of this in SQL Server would be to use Common Table Expressions (CTE).

You would first need to add two site parameters to your site admin - site configuration tab.

In order to create Excel Reports you would need:

Param: ENABLE_CREATE_LEGACY_EXCEL_REPORT
Value: Y
Then to use the Common Table Expressions (CTE) SQL statements:

Param: SQL_QUERY_VALIDATION_ENABLED
Value: N

Now we can use the Common Table Expressions query along with the ''STUFF'' function and a little manipulation of the ''FOR XML'' clause to build the path.

WITH w1 ( FolderID, FatherID, FolderName, level ) AS
(SELECT AL_ITEM_ID, AL_FATHER_ID, AL_DESCRIPTION, 0 AS level
FROM ALL_LISTS, TEST
WHERE TEST.TS_SUBJECT = ALL_LISTS.AL_ITEM_ID AND
TEST.TS_TEST_ID = '67'

UNION ALL

SELECT AL_ITEM_ID, AL_FATHER_ID, AL_DESCRIPTION, level + 1
FROM ALL_LISTS
JOIN w1 ON w1.FatherID = ALL_LISTS.AL_ITEM_ID)

SELECT TS_TEST_ID As ''TestID'',
TS_NAME As ''Test Name'',
(SELECT STUFF((SELECT '/' + FolderName
FROM w1
ORDER BY level DESC
FOR XML PATH ('')) , 1, 1, '')) As ''Test Path''
FROM TEST
WHERE TS_TEST_ID = '67'

Then your result should look like this:

![alt text][1]

[1]: /storage/temp/489-capture.jpg

Marked as spam
Posted by (Questions: 3, Answers: 168)
Answered on September 22, 2016 4:41 pm
EyeOnTesting