July 18, 2011 at 8:53 am
I am trying to find data on what reports I have "Hidden in List View" on my report server. Is there a column of flag that identifies this state in the report server database somewhere?
July 18, 2011 at 8:59 am
This can get you started. I don't know xquery enough to do it for you.
This query finds the dataset sources. Use a similar logic to find datalists objects and then drilldown for visibility option.
;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name, Path,
x.value('CommandType[1]', 'VARCHAR(128)') AS CommandType,
x.value('CommandText[1]','VARCHAR(2000)') AS CommandText
FROM (
select C.name, C.[Path],
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM dbo.Catalog C
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(128)') IS NULL --= 'StoredProcedure'
AND NOT patindex('%.%', name) > 0
--AND x.value('CommandText[1]','VARCHAR(2000)') NOT LIKE '%SET TRANSACTION ISOLATION LEVEL SNAPSHOT%'
ORDER BY CommandText, name
--ORDER BY Path, name
July 18, 2011 at 11:27 am
Thanks, but unfortunately I do not know xquery at all. Your post did, however direct me to the catalog table where there is a 'Hidden' column that gets populated with a true/false bit value. Thank you.
July 18, 2011 at 11:33 am
Ha, I understood that you wanted the list of reports that had an hidden list on it :w00t:.
HTH!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply