ACS Commons Reporting Query

[rakshashetty]

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!!

[dvnSudheer]

rakshashetty​,

If you are just trying to get the workflow instances which are specific custom model, you can always use the modelId Property to retrieve them and add the status property to get only the completed instances or In progress.

This below query will give you the results which are of a specific custom workflow model and the status which are not in completed state.

Once you got the data, you can always iterate through them and get the specific instances if you are looking only for a specific path.

[rakshashetty]

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!

[dvnSudheer · Accepted solution]

rakshashetty, yes you can search all the properties both in parent and child nodes. Are you specifically looking for SQL2 query?

Please find the below examples in Querybuilder format and xpath queries. I have used status property, you can change it to starttime and test based on your requirement. The below example provides all the workflow instances which has a payload path as "/content/dam/imageName.jpg" and status as "completed"

Below are the query parameters in Querybuilder

XPATH Query

Sample Example with date condition

There are few variants where you can choose to search on the date range from and to dates. Refer to these properties