About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Tuesday, June 09, 2015

Search for fields on ReportServer 


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
 
 
/*
 * Get a list of reports in a specific path/folder
 */
;WITH ItemContentBinaries AS
(
 SELECT
 ItemID
 ,Name
 ,[Type]
    ,CASE [Type]
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
 FROM ReportServer.dbo.[Catalog] with (nolock)
 WHERE [PATH] LIKE '/Department Level/%'
)
---ADD WORDS, USED IN FIELDS, TO SEARCH FOR 
,WordSearchCTE AS
(
 SELECT 'SSN' AS Word
 UNION ALL
 SELECT 'AGE'
 UNION ALL
 SELECT 'DOB'
 UNION ALL
 SELECT 'EPMP'
 UNION ALL
 SELECT 'PAY'
 UNION ALL
 SELECT 'COMP'
)
--This CTE strips off the BOM if it exists...
,ItemContentNoBOM AS
(
 SELECT
    ItemID
 ,Name
 ,[Type]
 ,TypeDescription
    ,CASE WHEN LEFT(Content,3) = 0xEFBBBF THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
          ELSE Content
     END AS Content
 FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
 SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
--and only select from those that contain the search terms 
SELECT * FROM
(
 SELECT 
 ItemID,Name,[Type],TypeDescription,ContentXML
 ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
 ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
 FROM ItemContentXML
 --Get all the Query elements (The "*:" ignores any xml namespaces)
 CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
)x
JOIN 
(
 select '%'+Word+'%' as pattern
 from WordSearchCTE
) w on CommandText like w.pattern

Posted by at
Labels: , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)