parsing metadata about reports from Reporting service database

  • using code from https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2cadf5ce-edfc-4ec9-90b3-a82f91e689a3/reports-documentation-report-metadata-revisited-2008?forum=sqlreportingservices

    to parse reports metadata from 1000+ reports

    I modified code to get additional info about fields and input parameters

    WITH

    XMLNAMESPACES

    (

    DEFAULT

    'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'

    AS rd

    )

    SELECT

    name

    ,

    x.value('CommandType[1]', 'VARCHAR(8000)') AS CommandType,

    x.value('CommandText[1]','VARCHAR(8000)') AS CommandText,

    x.value('DataSourceName[1]','VARCHAR(8000)') AS DataSource,

    q.value('@Name','VARCHAR(8000)') AS DataSetName,

    replace(q.value('Fields[1]','varchar(250)'),'System.String', ',') as fields,

    x.value('QueryParameters[1]','varchar(250)') as InputParams

    FROM

    (

    SELECT

    name,

    CAST

    (CAST(content AS VARBINARY(MAX)) AS XML) AS reportXML

    FROM

    ReportServer.dbo.Catalog

    WHERE

    content IS NOT NULL

    AND

    TYPE != 3 ) a

    CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') d(q)

    CROSS APPLY q.nodes('Query') r(x)

    WHERE

    q.value('CommandType[1]', 'VARCHAR(8000)') IS NULL

    --= 'CommandText'

    and name ='my DBA report example'

    ORDER BY

    name

    if I don't use "replace" function fields and InputParams columns return following strings

    fields

    ipNetNameSystem.StringisClusterSystem.StringdaySystem.StringcaptureDateSystem.StringstartHourSystem.ByteendHourSystem.ByteavgCPUSystem.DecimalmedianCPUSystem.DecimalmaxCPUSystem.Decimal

    InputParams

    =Parameters!ipNetName.Value=Parameters!startDate.Value=Parameters!endDate.Value=Parameters!startHour.Value=Parameters!endHour.Value=Parameters!excludeWeekendsFlag.Value

    bellow is format of XML report

    <DataSet Name="DataSet1">

    <Fields>

    <Field Name="ipNetName">

    <DataField>ipNetName</DataField>

    <rd:TypeName>System.String</rd:TypeName>

    </Field>

    <Field Name="isCluster">

    <DataField>isCluster</DataField>

    <rd:TypeName>System.String</rd:TypeName>

    </Field>

    <Field Name="day">

    and

    Query>

    <DataSourceName>ds_Perfmon</DataSourceName>

    <CommandType>StoredProcedure</CommandType>

    <CommandText>getDailyHourRangeCpu</CommandText>

    <QueryParameters>

    <QueryParameter Name="@ipNetName">

    <Value>=Parameters!ipNetName.Value</Value>

    </QueryParameter>

    <QueryParameter Name="@startDate">

    <Value>=Parameters!startDate.Value</Value>

    Question : how to modify code in query without using "replace" function return only fields and inputparam names ?

    example for fields

    for

    ipNetName, isCluster,day ,captureDate

    and for input params

    ipNetName, startDate, endDate

  • I queried your sample Fields XML. It seems pretty straightforward.

    I took out the rs namespace from the sample, and added a root so it would be well-formed, but the query is what actually matters.

    Does this get you what you need?

    DECLARE @XML XML = '

    <Whatever>

    <DataSet Name="DataSet1">

    <Fields>

    <Field Name="ipNetName">

    <DataField>ipNetName</DataField>

    <TypeName>System.String</TypeName>

    </Field>

    <Field Name="isCluster">

    <DataField>isCluster</DataField>

    <TypeName>System.String</TypeName>

    </Field>

    <Field Name="day">

    <DataField>isCluster</DataField>

    <TypeName>System.String</TypeName>

    </Field>

    </Fields>

    </DataSet>

    </Whatever>';

    SELECT X.Field.query('.').value('(/Field/@Name)[1]','VARCHAR(MAX)')

    FROM @XML.nodes('Whatever/DataSet/Fields/Field') AS X(Field);

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • The query below will give you the field names in a column and the data type in a separate column.

    WITH

    XMLNAMESPACES

    (

    DEFAULT

    'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition',

    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'

    AS RepDef

    )

    SELECT

    ReportName = Name

    ,DataSetName = R.value('@Name', 'VARCHAR(8000)')

    ,FieldName = df.value('(DataField)[1]', 'varchar(250)')

    ,FieldType = df.value('(RepDef:TypeName)[1]', 'varchar(250)')

    FROM

    (SELECT

    Name

    ,CAST

    (CAST(Content AS VARBINARY(MAX)) AS XML) AS reportXML

    FROM

    ReportServer.dbo.Catalog

    WHERE

    Content IS NOT NULL

    AND Type != 3

    ) a

    CROSS APPLY reportXML.nodes('/Report/DataSets/DataSet') Ds(R)

    CROSS APPLY R.nodes('Fields/Field') f(df)

    ORDER BY

    Name;

  • thank you, both queries return fields name, but in row format

    ipNetName

    isCluster

    day

    but I need output in following format (since I will run it for all reports)

    name CommandTypeCommandTextDataSetNamefieldsiputparms

    ---- ----------- ----------- ---------- ------------------ -------

    Report1 StoredProceduresp_getSomedata1DataSet1 Server, db, host Day ipNetName, startDate,endDate

    Report2 StoredProceduresp_getSomedata2DataSet1 Cpu,Memeory,AVG_Cpu ipNetName, myparam1,endDate

    ..

    Report333 StoredProceduresp_getSomedata3DataSet1 ipNetNameS,isCluster,version ipNetName, startDate,myparam5

  • There's an easy way to use For XML Path to turn a set of rows into a list. For some reason, this site isn't letting me post a sample of it. Blows up every time I click "Post Reply".

    Or, judging by what's happening when I try to link to other sites with samples, it may be either my computer or something in my employers browsing rules or something of that sort.

    Search "for xml path" and look for articles about turning data into strings or lists. There are lots of good walk-throughs available.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks a lot

    I used you solution with row based presentation of fields

    query was ported to excel and it was much easier to present it when I grouped result in pivot table

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply