SELECT 
	rv.ViewDescription as 'View Name', rh.ReportName as 'Report Name', rft.GroupCode as Folder, rft.ColumnName as 'Column Name', rh.ReportStatusCode as '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 rh 
	on rh.ReportId = base.report

	where rh.ReportStatusCode != 'DELETED'
		AND rh.RoleCode != 'SUBQUERY';