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

• Stan Kulp-439977

  SSChampion

  Points: 10062

  More actions

  March 20, 2017 at 10:23 pm

  #341790

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

• fwaelti

  SSC Journeyman

  Points: 83

  More actions

  March 20, 2017 at 11:55 pm

  #1934110

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

  [AccountsReceivable]

   - Database?
  Flurin

• Kenneth Igiri

  SSCarpal Tunnel

  Points: 4998

  More actions

  March 21, 2017 at 2:05 am

  #1934115

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

  Br. Kenneth Igiri
  https://kennethigiri.com
  All nations come to my light, all kings to the brightness of my rising

• Stan Kulp-439977

  SSChampion

  Points: 10062

  More actions

  March 21, 2017 at 7:11 am

  #1934145

  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

  SSChampion

  Points: 10062

  More actions

  March 21, 2017 at 7:15 am

  #1934146

  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

  More actions

  March 21, 2017 at 7:21 am

  #1934148

  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: 251

  More actions

  March 21, 2017 at 8:34 am

  #1934164

  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

  More actions

  March 21, 2017 at 9:24 am

  #1934178

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

• andrea4618

  SSC Enthusiast

  Points: 117

  More actions

  March 21, 2017 at 10:15 am

  #1934185

  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: 323504

  More actions

  March 21, 2017 at 10:19 am

  #1934186

  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

  SSChampion

  Points: 10062

  More actions

  March 21, 2017 at 2:36 pm

  #1934221

  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

  SSChampion

  Points: 10062

  More actions

  March 22, 2017 at 1:35 pm

  #1934377

  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

  SSChampion

  Points: 10062

  More actions

  March 22, 2017 at 1:36 pm

  #1934378

  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

  SSChampion

  Points: 10062

  More actions

  March 22, 2017 at 1:37 pm

  #1934379

  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

  SSChampion

  Points: 10062

  More actions

  March 22, 2017 at 1:37 pm

  #1934381

  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.