Report Server Diagnostic Reports

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

    Any ideas anyone?

  • 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...

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

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

  • 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?

  • 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...

  • 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

  • awesome,

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

    these are very nice report..

    thanks,

  • >> denglishbi

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

    Roger

  • Thank you for sharing your work. Well done!

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

  • 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

  • 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!!!

  • 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.

  • 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.

  • 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.

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

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