Video Screencast Help

Custom Query - Field client with no view connected required

Created: 17 Sep 2013 | 1 comment

Hello,

Our existing report indicates all clients and their through the view attached project. We would like another report which shows the clients which have no attached project.

Can somebody help please?

SELECT DISTINCT
"E2"."name" as "Client Name",
"E1"."name" as "Project"
FROM ( ( ( ( ( (  "DBA"."domain_Entity" AS E2
JOIN "DBA"."view_Node" AS N2 ON E2.id = N2.entityId )
JOIN "DBA"."view_Node" AS N1 ON N2.parentNodeId = N1.id )
JOIN "DBA"."domain_Entity" AS E1 ON E1.id = N1.entityId )
JOIN "DBA"."view_Node" AS N0 ON N1.parentNodeId = N0.id )
JOIN "DBA"."domain_Entity" AS E0 ON E0.id = N0.entityId )
JOIN "DBA"."domain_JobArchive" on E2.id = "DBA"."domain_JobArchive".clientId )
WHERE N2.treeId IN (SELECT id FROM view_tree WHERE name = 'Project' ) AND N2.level = 2
AND "DBA"."domain_JobArchive"."endTime" > NOMTimeToUTCBigint( '2012-05-01 00:00:00' )
ORDER by "Client Name", "Project"

Operating Systems:

Comments 1 CommentJump to latest comment

opscentuser's picture

Hello,

Since I didn't get any answer and I didn't find the solution yet I try to explain the problem clearer.

We need a custom query that indicates all abjects which are not listed in the selected view. So where ther is no entry at the view_node.

Could anyone help to modify the existing report in that way?