SELECT 
	rv.ViewDescription as 'View Name', rhParent.ReportName as 'Report Name', rhChild.ReportName as SubQuery, rft.GroupCode as Folder, rft.ColumnName as 'Column Name', rhParent.ReportStatusCode as 'Parent Report Status'
FROM 
	reportfieldtemplate rft join reportview rv 
	on rft.ViewId = rv.ViewId
	
	join
    (
		SELECT 
			c.ReportId as report, c.ViewLevelCachedFilterGroupId, c.FieldTemplateId as field
		FROM
			reportformat a join reportformat b
			ON a.EntityId = b.EntityId
				AND a.FormatTypeCode = 'FILTERVALUES'
				AND a.FormatCode in ('CACHED', 'CACHED_ONDEMAND', 'QUERY', 'QUERY_ONDEMAND')
				AND b.FormatTypeCode = 'DISPLAYSTYLE'
				AND 
				(
					b.FormatCode = 'IMAGEANDTEXT'
					OR b.FormatCode = 'COLOURANDTEXT'
				)
			join reportfilter c
			ON a.EntityId = c.FilterId

			where b.EntityId not IN 
			(
				SELECT 
					EntityId
				FROM
					reportformat
				WHERE
					FormatTypeCode = 'LINKPARENTDATA'
					AND FormatCode = 'true'
			)
			
            and c.ViewLevelCachedFilterGroupId = 0
    ) base
	on rft.FieldTemplateId = base.field
	
	join reportheader rhChild
	on rhChild.ReportId = base.report
	
	join reportheader rhParent 
	on rhParent.ReportId = rhChild.ParentReportId

	where rhChild.ReportStatusCode != 'DELETED'
		and rhParent.ReportStatusCode != 'DELETED'
		AND rhChild.RoleCode = 'SUBQUERY';