March 20, 2017 at 10:23 pm
Comments posted to this topic are about the item Dynamic SSRS report documentation via a ReportServer.dbo.Catalog query
March 20, 2017 at 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
March 21, 2017 at 2:05 am
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
March 21, 2017 at 7:11 am
fwaelti - Monday, March 20, 2017 11:55 PMHi Stan
Nice Query and report.
I didn't get it to run on our system though.
What is the- Database?[AccountsReceivable]
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.
March 21, 2017 at 7:15 am
Br. Kenneth Igiri - Tuesday, March 21, 2017 2:05 AMImpressive. 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.
March 21, 2017 at 7:21 am
Stan Kulp-439977 - Monday, March 20, 2017 10:23 PMStan,
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 CATCHIt 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
March 21, 2017 at 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.
March 21, 2017 at 9:24 am
te acountsreceivables part of the query is pretty odd...
March 21, 2017 at 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?
March 21, 2017 at 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:
--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
March 21, 2017 at 2:36 pm
Lowell - Tuesday, March 21, 2017 10:19 AMkudos 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:
- Changed three part names from ReportServer.dbo.Catalog to two part,as my shop renamed the database to something else.
- Changed the references to sub queries from a specific table to default to getdate()
- Changed Cross Apply to Outer Apply, as some reports with no parameters showed no details, not even the report name.
- 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 CATCHBEGIN TRY
DROP TABLE #ReportParameters
END TRY
BEGIN CATCH
END CATCHBEGIN TRY
DROP TABLE #ReportFields
END TRY
BEGIN CATCH
END CATCHSELECT
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 nameSELECT
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!
March 22, 2017 at 1:35 pm
fwaelti - Monday, March 20, 2017 11:55 PMHi Stan
Nice Query and report.
I didn't get it to run on our system though.
What is the- Database?[AccountsReceivable]
Flurin
The webmin had not put in my latest update. It is in now. Try downloading the attachment again.
March 22, 2017 at 1:36 pm
andrea4618 - Tuesday, March 21, 2017 10:15 AMI 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.
March 22, 2017 at 1:37 pm
Tim Toennies - Tuesday, March 21, 2017 8:34 AMThe 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.
March 22, 2017 at 1:37 pm
Tim Toennies - Tuesday, March 21, 2017 8:34 AMThe 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