select 
	rv.ViewDescription as 'View Name', filtergroup.GroupCode as Folder, filtergroup.ShortDescription as 'Filter Group', rft.ColumnName as 'Column Name', rv.ViewStatusCode as 'View Status'
from 
	reportfieldtemplate rft join reportfieldtemplate filtergroup
	on rft.GroupCode = filtergroup.FieldTemplateId
	
	join reportview rv 
	on rft.ViewId = rv.ViewId
	
	join
	(
		SELECT 
			distinct 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

	where ViewStatusCode not in('DELETED', 'REPLACED')