ACS Commons Reporting Query

[#1]

Hi All,

I have a requirement to generate a report which lists out all the assets of a particular project submitted for a given custom workflow . I will be using ACS Commons reporting tool and i have come up with a query like -

SELECT * FROM [cq:Payload] As node WHERE ISDESCENDANTNODE (node, '/var/workflow/instances')

but this lists all of the workflows and not specifically my custom workflow . To refine this query to pick values that i require , I need to have a query which would let me fetch values from nodes at level 1,3 and 4 as indicated below :

I tried with following query  and this lets me pick values from 1,2 (direct parent and child) or 2,3 (direct parent and child) or 2,4 (direct parent and child).

SELECT parent.* FROM [cq:Workflow] AS parent

INNER JOIN [cq:WorkflowData] AS child

ON ISCHILDNODE(child,parent)

WHERE ISDESCENDANTNODE(parent, [{{path}}]) AND parent.status= '{{status}}'

Kindly help me come up with a query which would let me fetch values from level 1,3 and 4 for my requirement .

Thanks in advance!!

[#2]

dvnSudheer​ , thank you for this input.

But i want to fetch the property value like startTime which is available at parent node (workflow node i.e 1 in the pic attached ) and payload path which is available at grandchild level node ( payload node i.e 4 in the pic attached ) . Would this be possible using a single query ?

Thanks!