Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Comments posted to this topic are about the item Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query

  • fwaelti

    SSC Journeyman

    Points: 83

    Hi Stan 
    Nice Query and report. 
    I didn't get it to run on our system though.
    What is the 

    [AccountsReceivable]

     - Database?
    Flurin

  • Br. Kenneth Igiri

    SSCarpal Tunnel

    Points: 4614

    Impressive. I am wondering what inspired you to dig into this. Thanks

    Br. Kenneth Igiri
    www.scribblingsage.com
    All nations come to my light, all kings to the brightness of my rising

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    fwaelti - Monday, March 20, 2017 11:55 PM

    Hi Stan 
    Nice Query and report. 
    I didn't get it to run on our system though.
    What is the 

    [AccountsReceivable]

     - Database?
    Flurin

    I had made an update to fix that, but evidently they didn't make the change.
    I will try to update it again.
    In the meantime try commenting out that part, or using the query in the article.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Br. Kenneth Igiri - Tuesday, March 21, 2017 2:05 AM

    Impressive. I am wondering what inspired you to dig into this. Thanks

    I have a couple of hundred reports in production and was asked for documentation. Doing it one-by-one would have taken months and been out of date before I finished. This is just a first attempt, but I think it has potential. I want to be able to list the tables queried, but I haven't quite figured out how to extract them cleanly yet.

  • dracowiz

    SSC Journeyman

    Points: 90

    Stan Kulp-439977 - Monday, March 20, 2017 10:23 PM

    Stan,
    One question in your code you used the construct :
    BEGIN TRY
    DROP TABLE #ReportList
    END TRY
    BEGIN CATCH
    END CATCH
    My question is why is the begin end catch at the end, rather than encapsulating the block such as
    BEGIN CATCH
    BEGIN
    TRY
    DROP TABLE #ReportList
    END TRY
    END CATCH

    It seems to me that initially the catch statements would have no effect - What am I missing?

    Comments posted to this topic are about the item Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query

  • Tim Toennies

    SSC Veteran

    Points: 248

    The report is giving me the error in the attachment.  I can see that some of your specific reports need a date and it seems like that's the problem.  Thanks for looking into this.

  • Rodan

    SSC Veteran

    Points: 220

    te acountsreceivables part of the query is pretty odd...

  • andrea4618

    SSC Enthusiast

    Points: 117

    I took the code from the article and attempted to run it.  It ran for over 3 minutes before I canceled it.  Is that a normal runtime for this query or is something not working right?

  • Lowell

    SSC Guru

    Points: 323450

    kudos to you sir, nice job. playing with xquery and xml takes a bit of work!
    I made some trivial changes to the query for the dataset, in order to fit it to what i see in my environment:

    1. Changed three part names from ReportServer.dbo.Catalog to two part,as my shop renamed the database to something else.
    2. Changed the references  to sub queries from a specific table to default to getdate()
    3. Changed Cross Apply to Outer Apply, as some reports with no parameters showed no details, not even the report name.
    4. Commented out "WHERE Parameter IS NOT NULL"/"WHERE FIELDs IS NOT NULL" to guarantee all report sappear in the result set.

    --my shop renames the ReportServer database for some reason, removed explicit three part references to Reportserver.dbo.Catalog to two part name
    BEGIN TRY
        DROP TABLE #ReportList
    END TRY
    BEGIN CATCH
    END CATCH

    BEGIN TRY
        DROP TABLE #ReportParameters
    END TRY
    BEGIN CATCH
    END CATCH

    BEGIN TRY
        DROP TABLE #ReportFields
    END TRY
    BEGIN CATCH
    END CATCH

    SELECT
        Name
        ,Path
    INTO #ReportList
    FROM dbo.Catalog
    WHERE Content IS NOT NULL
    ORDER BY Name;

    SELECT DISTINCT Name as ReportName
        ,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)')
         ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)')
         ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
         ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
         ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
         ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
         ,ParameterPrompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
         ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
         ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
         ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
    INTO #ReportParameters
    FROM (
            SELECT top 1000 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
            FROM dbo.Catalog C
            WHERE C.Content is not null
            AND Name NOT LIKE '%SUB%'
            AND C.Type = 2
         ) a
    --changed to outer apply to guarantee data results
    OUTER APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )
    ORDER BY ReportName,ParameterName;

    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT DISTINCT ReportName        = name
      ,DataSetName        = x.value('(@Name)[1]', 'VARCHAR(250)')
         ,DataSourceName    = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
         ,CommandText        = x.value('(Query/CommandText)[1]','VARCHAR(250)')
         ,Fields            = df.value('(@Name)[1]','VARCHAR(250)')
         ,DataField        = df.value('(DataField)[1]','VARCHAR(250)')
         ,DataType        = df.value('(rd:TypeName)[1]','VARCHAR(250)')
         ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
    INTO #ReportFields
    FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM dbo.Catalog C
      WHERE C.Content is not null
      AND C.Type = 2
         ) a
      --changed to outer apply to guarantee data results
    OUTER APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
    OUTER APPLY x.nodes('Fields/Field') f(df)
    ORDER BY name

    SELECT
        a.Name AS ReportName
        ,a.Path
        ,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
        ,'http://ReportCenter/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
      --References to specific data removed, defaulted to getdate by Lowell
        ,getdate() AS AcctRecMinTransDate
        ,getdate() AS AcctRecMaxTransDate
        ,'User Input' AS FieldType
        ,b.ParameterPrompt AS DataSetOrPromptName
        ,b.ParameterName AS FieldOrParameterName
    FROM #ReportList a
    LEFT OUTER JOIN #ReportParameters b ON a.Name = b.ReportName
    --Commented out By Lowell, so that all repoorts, even without Parameters, appear in the report.
    --WHERE b.ParameterName IS NOT NULL
    UNION
    SELECT
        a.Name AS ReportName
        ,a.Path
        ,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
        ,'http://ReportCenter/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
        ,getdate() AS AcctRecMinTransDate
        ,getdate() AS AcctRecMaxTransDate
        ,'Data Point' AS FieldType
        ,b.DataSetName AS DataSetOrPromptName
        ,b.Fields AS FieldOrParameterName
    FROM #ReportList a
    LEFT OUTER JOIN #ReportFields b ON a.Name = b.ReportName
    --Commented out By Lowell, so that all repoorts, evne without Parameters, appear in the report.
    --WHERE b.Fields IS NOT NULL
    ORDER BY Name,Path,FieldType,ParameterPrompt,ParameterName

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Lowell - Tuesday, March 21, 2017 10:19 AM

    kudos to you sir, nice job. playing with xquery and xml takes a bit of work!
    I made some trivial changes to the query for the dataset, in order to fit it to what i see in my environment:

    1. Changed three part names from ReportServer.dbo.Catalog to two part,as my shop renamed the database to something else.
    2. Changed the references  to sub queries from a specific table to default to getdate()
    3. Changed Cross Apply to Outer Apply, as some reports with no parameters showed no details, not even the report name.
    4. Commented out "WHERE Parameter IS NOT NULL"/"WHERE FIELDs IS NOT NULL" to guarantee all report sappear in the result set.

    --my shop renames the ReportServer database for some reason, removed explicit three part references to Reportserver.dbo.Catalog to two part name
    BEGIN TRY
        DROP TABLE #ReportList
    END TRY
    BEGIN CATCH
    END CATCH

    BEGIN TRY
        DROP TABLE #ReportParameters
    END TRY
    BEGIN CATCH
    END CATCH

    BEGIN TRY
        DROP TABLE #ReportFields
    END TRY
    BEGIN CATCH
    END CATCH

    SELECT
        Name
        ,Path
    INTO #ReportList
    FROM dbo.Catalog
    WHERE Content IS NOT NULL
    ORDER BY Name;

    SELECT DISTINCT Name as ReportName
        ,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)')
         ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)')
         ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
         ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
         ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
         ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
         ,ParameterPrompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
         ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
         ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
         ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
    INTO #ReportParameters
    FROM (
            SELECT top 1000 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
            FROM dbo.Catalog C
            WHERE C.Content is not null
            AND Name NOT LIKE '%SUB%'
            AND C.Type = 2
         ) a
    --changed to outer apply to guarantee data results
    OUTER APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )
    ORDER BY ReportName,ParameterName;

    WITH XMLNAMESPACES ( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
    SELECT DISTINCT ReportName        = name
      ,DataSetName        = x.value('(@Name)[1]', 'VARCHAR(250)')
         ,DataSourceName    = x.value('(Query/DataSourceName)[1]','VARCHAR(250)')
         ,CommandText        = x.value('(Query/CommandText)[1]','VARCHAR(250)')
         ,Fields            = df.value('(@Name)[1]','VARCHAR(250)')
         ,DataField        = df.value('(DataField)[1]','VARCHAR(250)')
         ,DataType        = df.value('(rd:TypeName)[1]','VARCHAR(250)')
         ,ConnectionString = x.value('(ConnectionProperties/ConnectString)[1]','VARCHAR(250)')
    INTO #ReportFields
    FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
      FROM dbo.Catalog C
      WHERE C.Content is not null
      AND C.Type = 2
         ) a
      --changed to outer apply to guarantee data results
    OUTER APPLY reportXML.nodes('/Report/DataSets/DataSet') r ( x )
    OUTER APPLY x.nodes('Fields/Field') f(df)
    ORDER BY name

    SELECT
        a.Name AS ReportName
        ,a.Path
        ,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
        ,'http://ReportCenter/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
      --References to specific data removed, defaulted to getdate by Lowell
        ,getdate() AS AcctRecMinTransDate
        ,getdate() AS AcctRecMaxTransDate
        ,'User Input' AS FieldType
        ,b.ParameterPrompt AS DataSetOrPromptName
        ,b.ParameterName AS FieldOrParameterName
    FROM #ReportList a
    LEFT OUTER JOIN #ReportParameters b ON a.Name = b.ReportName
    --Commented out By Lowell, so that all repoorts, even without Parameters, appear in the report.
    --WHERE b.ParameterName IS NOT NULL
    UNION
    SELECT
        a.Name AS ReportName
        ,a.Path
        ,SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)) AS ReportFolder
        ,'http://ReportCenter/Reports/Pages/Report.aspx?ItemPath='+REPLACE(SUBSTRING(a.Path,1,LEN(a.Path)-LEN(a.Name)),'/','%2f')+REPLACE(a.Name,' ','+') AS ReportLink
        ,getdate() AS AcctRecMinTransDate
        ,getdate() AS AcctRecMaxTransDate
        ,'Data Point' AS FieldType
        ,b.DataSetName AS DataSetOrPromptName
        ,b.Fields AS FieldOrParameterName
    FROM #ReportList a
    LEFT OUTER JOIN #ReportFields b ON a.Name = b.ReportName
    --Commented out By Lowell, so that all repoorts, evne without Parameters, appear in the report.
    --WHERE b.Fields IS NOT NULL
    ORDER BY Name,Path,FieldType,ParameterPrompt,ParameterName

    That's really nice! Thanks!

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    fwaelti - Monday, March 20, 2017 11:55 PM

    Hi Stan 
    Nice Query and report. 
    I didn't get it to run on our system though.
    What is the 

    [AccountsReceivable]

     - Database?
    Flurin

    The webmin had not put in my latest update. It is in now. Try downloading the attachment again.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    andrea4618 - Tuesday, March 21, 2017 10:15 AM

    I took the code from the article and attempted to run it.  It ran for over 3 minutes before I canceled it.  Is that a normal runtime for this query or is something not working right?

    The webmin had not put in my latest update. It is in now. Try downloading the attachment again.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Tim Toennies - Tuesday, March 21, 2017 8:34 AM

    The report is giving me the error in the attachment.  I can see that some of your specific reports need a date and it seems like that's the problem.  Thanks for looking into this.

    The webmin had not put in my latest update. It is in now. Try downloading the attachment again.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9977

    Tim Toennies - Tuesday, March 21, 2017 8:34 AM

    The report is giving me the error in the attachment.  I can see that some of your specific reports need a date and it seems like that's the problem.  Thanks for looking into this.

    The webmin had not put in my latest update. It is in now. Try downloading the attachment again.

Viewing 15 posts - 1 through 15 (of 25 total)

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