Report Server Diagnostic Reports

Question

Post reply

Report Server Diagnostic Reports

Viewing 15 posts - 16 through 30 (of 117 total)

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

roger_os SSC Eights! Points: 831 More actions · Answer 1

Still can't find where I can see what the name of the stored procedure that each report uses is?

Any ideas anyone?

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 2

Roger...

Typically I like developing reports with a procedure (kept within the database) and an RDL file (which executes the procedure). To make these as easy for deployment as possible to the greater audience, I embedded the query directly into the RDL file. The most simple way to view them would be to do the following:

1) Open the RDL (not in visual studio, but in the XML view. If you are not sure how to do that, just put the file somewhere, like your desktop, and double click it).

2) Search for the text string "<Query>" (do not include the double quotes - only what is inside the quotation marks).

3) You should be taken to the section in the RDL file that contains the query (some of these report have multiple queries). Just past the <DataSourceName> and <CommandText> tags begins the SQL code. That should be what you are looking for...

AlAndrew SSC Veteran Points: 216 More actions · Answer 3

Wonderful addition to my reporting. Easy to install and use. Thanks for sharing!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Great article and useful information. As was shared earlier, by default SSRS only stores execution information for 60 days.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

roger_os SSC Eights! Points: 831 More actions · Answer 5

Thanks for the reply Michael, yes that is how I do it at the moment, but it would be great if I could query all my reports and see what the stored procedure for each one was in one go .. how possible is this?

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 6

Ahh - now I understand. If you are capable of it, take a look at the "Content" column in the ReportServer.Catalog field. I would imagine it would be there. Unfortunately, it is an encrypted field.

I haven't investigated what is necessary to view these contents in a meaningful way -- if anyone else here has, I welcome their thoughts...

denglishbi SSCrazy Eights Points: 8831 More actions · Answer 7

Here is how you can get at the RDL for the report in the Content column. Once you have done this you can query this information to look for reports that contain particular items or references.

Quick Tip: Retrieving Report Definitions from the Catalog Table in the ReportServer Database

SELECT [Name],

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

FROM ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK)

WHERE --get only reports, not folders or other resources

[Type] = 2

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com

Rolf van Rijn Newbie Points: 8 More actions · Answer 8

awesome,

I had already created an execution history report, but I might as well throw that one out..

these are very nice report..

thanks,

roger_os SSC Eights! Points: 831 More actions · Answer 9

>> denglishbi

Thankyou so much that is fantastic, I now have all I need, thanks all

Roger

Francis Apel SSC Eights! Points: 918 More actions · Answer 10

Thank you for sharing your work. Well done!

Francis
-----------------
SQLRanger.com

Scott Arendt SSCrazy Eights Points: 8218 More actions · Answer 11

Wow, the timing on this is perfect. We are introducing SSRS later this month and it will be great to be able to show all of these diagnostics.

Scott

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 12

Thanks denglishbi - I expanded upon your statement to show the FIRST query in a report:

;with RS as

(

SELECT [Name],

CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML,

CAST(CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS Varchar(max)) AS reportText

FROM [Catalog] WITH (NOLOCK)

WHERE --get only reports, not folders or other resources

[Type] = 2

)

selectname,

reportXML,

substring(reportText, charindex('<commandText>',reportText)+13, charindex('</commandText>',reportText)-charindex('<commandText>',reportText)-13)

from RS

Thanks for the collaboration - I'll be updating the report pack to include this!!!

Josh Duncan SSC Enthusiast Points: 145 More actions · Answer 13

Great work! These reports are rich in detail and are very useful. I went a step further and added a dynamic datasource: ="data source=" & Parameters!Server.Value & ";initial catalog= ReportServer", because I have more than one Report Server. Thanks for the source code.

meyer.richard.s SSC Veteran Points: 255 More actions · Answer 14

Thanks for the nifty reports, they are working great! One caveat: Our ReportServer database does a cleanup/purge of the execution log table, so it only has about 2 months worth of report execution log data as a result. We implemented the RSExecutionLog solution from the SS2005 Samples download that provides a data mart and SSIS pkgs for population from the ReportServer DB to get around this problem, so we have longer execution history.

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 15

It is possible to change the limit for the purge of the execution log. Shown in the images for the Diagnostic Reports are the configurations for one of our development servers (which has the default 60 day setting). Our other servers are set to 180 days.

Someone with administrator level access should be able to click on the "Site Settings" link in the upper right corner of Report Manager. Within that section is an area that allows one to change the number of days. Obviously usage of one's server has an impact on the value to use -- for us, 180 has been fine.