Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Getting IndentLevel for T...
When we create a requirements document/report (custom), I use the IndentLevel field in the Requirements table to make sure the requirements appear in the document in the same order an hierarchy as they do in the Spira GUI (using Word heading levels). IndentLevel will look something like 'AAAAAAC', signifying both the order and the 'nesting level' in the tree of a specific item.
If I want to do the same for a Test Specification, based on the Test Case Folders, this does not seem to be so simple. The default Test Case Folder table does not include an IndentLevel field, only a ParentFolder field. ParentFolder is not so easy to use when building up a document, since you need to reconstruct the hierarchy of folders before being able to do anything.
Now the Standard section available in Spira called 'Test Case List' does have a property IndentLevel, which is what I need. However, I cannot use Standard sections because my documents consist of multiple custom sections and adding a standard section will always automatically put its contents first in the document, before all other sections, messing up the document order.
So I'd like to know, is there an easy way to reconstruct the IndentLevel for test case folders using SQL or XSLT? I'm not THAT great with SQL, so any help would be appreciated!
Hi DL
Unfortunately not really.
We have an internal SQL Server stored procedure that populates a cache table of the test case folder hierarchy (TST_TEST_CASE_FOLDER_HIERARCHY) but it is not currently exposed as a reportable entity for ESQL. You could raise a support ticket and ask our team to add TST_TEST_CASE_FOLDER_HIERARCHY as a reportable entity?
Regards
David
Using SQL. This approach involves creating a CTE that iterates through the folder hierarchy. The CTE joins the TestCaseFolder table with itself, looking for matching ParentFolder IDs. With each iteration, a counter variable (like Level) is incremented to represent the nesting depth. The final query selects the folder data along with the calculated Level as the IndentLevel.
geometry dash lite
For example:
WITH FolderHierarchy AS ( SELECT FolderID, ParentFolderID, 0 AS Level FROM TestCaseFolder AS Root WHERE ParentFolderID IS NULL UNION ALL SELECT F.FolderID, F.ParentFolderID, H.Level + 1 FROM TestCaseFolder AS F INNER JOIN FolderHierarchy AS H ON F.ParentFolderID = H.FolderID ) SELECT FolderID, ParentFolderID, Level AS IndentLevel FROM FolderHierarchy;
Or you can use XSLT:If you’re working with exported data (e.g., from a Spira database), you can transform it using XSLT.Here’s a simplified example of how you might approach this:Assume you have an XML representation of test case folders.Write an XSLT stylesheet that:Parses the XML.Recursively processes the folders.Determines the depth of each folder.Constructs the IndentLevel based on the depth.Outputs the transformed XML.
Example
<TestFolders> <Folder name="Root"> <Folder name="Folder A"> <Folder name="Subfolder A1"> <!-- Test cases go here --> </Folder> <!-- Other subfolders --> </Folder> <!-- Other top-level folders --> </Folder> </TestFolders>
You can get more guides here: https://learn.microsoft.com/en-us/sql/relational-databases/sqlxml-annotated-xsd-schemas-xpath-queries/net-framework-classes/applying-an-xsl-transformation-sqlxml-smash karts-managed-classes?view=sql-server-ver16
Thanks David. In the mean time, I solved the problem with XSLT, which turns out the same as the solution proposed by lyleclemons in this thread.
And if you have any questions, please email or call us at +1 (202) 558-6885
