April 20, 2011 at 4:53 pm
Is there a way to get a report of all the fields used in each of the reports deployed to our Report Server? I am interested in creating a table which shows parameters, dataset fields, table and matrix fields for each report in our catalog. Thanks.
April 21, 2011 at 8:23 am
Take a look at the reporting services database catalog table. It has pretty much everything you want already. Not in the most usable format, but it is all there.
April 21, 2011 at 9:26 am
Thanks. I have been working parsing the XML that is in ReportCatalog.Content, but I was just hoping someone else had already done this. More importantly, I don't want to figure all this out and then find out that Microsoft delivers something that does what I am looking for. Until then, I will continue the arduous process of parsing that XML.
April 21, 2011 at 10:36 am
You can find bits and pieces of the XML disected online, but I have never seen the whole thing. I have read on the MS social/support site that MS does not support direct access or queries against the reporting services database and reserves the right to change things without notice in the future. I agree that your endeavour is worth while, it does come with some long term risk. Good luck. If you work it all out. Please post it back here or write a blog about it somewhere.
April 21, 2011 at 10:59 am
I forgot where I found this. This gives you the sps and ad hoc statements.
Still a effing ling way to go to get parse the tables and get the columns, but that may be of help... hopefully :w00t:.
;WITH XMLNAMESPACES (
DEFAULT
'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT
name,
x.value('CommandType[1]', 'VARCHAR(50)') AS CommandType,
x.value('CommandText[1]','VARCHAR(50)') AS CommandText
FROM (
select name,
CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
from ReportServer.dbo.Catalog
) a
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet/Query') r(x)
WHERE x.value('CommandType[1]', 'VARCHAR(50)') = 'StoredProcedure'
AND NOT patindex('%.%', name) > 0
ORDER BY CommandText, name
April 21, 2011 at 11:09 am
Just to share what I have figured out so far, here are the nodes or paths that have the metadata I want and the .value paths as well.
-- DataSet name and field names
, x1.value ('@Name', 'VARCHAR(100)') AS DataSetName
, x1.value('data(Fields/Field)[1]','VARCHAR(max)') AS DataSetFieldName
CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') r1(x1)
-- Matrix Column
, x2.value('data(Value)[1]','varchar(100)') as ColGroupExpression
, x2.value ('@Name', 'VARCHAR(100)') AS ColGroupName
CROSS APPLY reportXML.nodes('Report/Body/ReportItems/Matrix/ColumnGroupings/ColumnGrouping/DynamicColumns/ReportItems/Textbox') r2(x2)
-- Matrix Row
, x3.value('data(Value)[1]','varchar(100)') as RowGroupExpression
, x3.value ('@Name', 'VARCHAR(100)') AS RowGroupName
CROSS APPLY reportXML.nodes('Report/Body/ReportItems/Matrix/RowGroupings/RowGrouping/DynamicRows/Grouping/GroupExpressions/GroupExpression') r3(x3)
-- Matrix Cell
, x7.value('data(Value)[1]','varchar(100)') as MatrixCellExpression
, x7.value ('@Name', 'VARCHAR(100)') AS MatrixCellName
CROSS APPLY reportXML.nodes('Report/Body/ReportItems/Matrix/MatrixRows/MatrixRow/MatrixCells/MatrixCell/ReportItems/Textbox') r7(x7)
-- Parameters
, x4.value ('@Name', 'VARCHAR(100)') AS ParmName
, x4.value('data(Prompt)[1]','VARCHAR(max)') AS ParmPrompt
CROSS APPLY reportXML.nodes('/Report/ReportParameters/ReportParameter') r4(x4)
-- Text boxes
, x5.value('.','varchar(100)') as Textbox
CROSS APPLY reportXML.nodes('/Report/Body/ReportItems/Textbox/Value') r5(x5)
April 21, 2011 at 11:12 am
Can you post the full thing? Maybe you'll catch another passenger that's willing to get on this ride!
April 21, 2011 at 11:21 am
Ninja's_RGR'us (4/21/2011)
Can you post the full thing? Maybe you'll catch another passenger that's willing to get on this ride!
Nevermind, I figured out what you meant in the code :-P.
April 21, 2011 at 11:21 am
Here is the SQL I use to get the Table details, along with the corresponding header. This uses a table of numbers, called Tally, to look at specific positions for details and headers, without using a cursor.
;WITH XMLNAMESPACES (
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition',
'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd
)
SELECT Name, Path
,p.N Position
, x6.value('data(ReportItems/Textbox/Value)[1]','varchar(100)') as Detail
, x9.value('data(ReportItems/Textbox/Value)[1]','varchar(100)') as Header
FROM (
SELECT name
, Path
, CAST(CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML
FROM ReportServer.dbo.Catalog
WHERE content IS NOT NULL
AND type = 2
) a
CROSS APPLY Tally p
CROSS APPLY reportXML.nodes('/Report/Body/ReportItems/Table/Details/TableRows/TableRow/TableCells/TableCell[position()=sql:column("N")]') r6(x6)
CROSS APPLY reportXML.nodes('/Report/Body/ReportItems/Table/Header/TableRows/TableRow/TableCells/TableCell[position()=sql:column("N")]') r9(x9)
where p.N < 200
The output for this would look like
Name Path Position Detail Header
XML Test/Mike Test/XML Test1=Fields!ReportingPlanKey.Value Reporting Plan Key
XML Test/Mike Test/XML Test2=Fields!SourceKey.Value Rptg Plan SK
XML Test/Mike Test/XML Test3=Fields!Description.Value Description
XML Test/Mike Test/XML Test4=Fields!ShortDescription.Value Short Description
XML Test/Mike Test/XML Test5=Fields!RptgOrgId.Value Rptg Org Id
April 21, 2011 at 11:26 am
It's a good start, but I'd find it hard to backtrack to owner.tablename.basecolumn
especially since I use mainly sps in my code.
April 21, 2011 at 12:30 pm
I have not attacked a listing of all the database fields used, but my theory on that is that I could get the CommandText, as you outline above, and then dynamically create a view using the SELECT statement. Then I could use the sp_Depends() procedure to get all the dependencies. I have not tried this yet, or played with sp_depends() enough to know whether this could even work. My hope would be that I could then get the tables and columns used in the queries for each report.
April 21, 2011 at 2:25 pm
I was thinking along the same lines. The problem for me is that the same field name is used across a LOT of tables.
More to the point, my report's queries ofter run over 1000 lines of code and 10+ tables, not withstanding temp tables. And also the fact that I rename the columns to something actually meaningfull. The work to backtrack all that seems almost impossible. Or more to the point not worth all the work. At least for me.
Moreover, I haven't played with dependencies since 2000 where there was a lot to be desired for back then. I heard it had improved but I don't know by how much and if the columns or included now.
I once tested a trial of Red-Gate's dependencies tracker and it seemed to have pretty much solved that problem. Maybe you could check that option as well.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply