SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Server Diagnostic Reports


Report Server Diagnostic Reports

Author
Message
kevriley
kevriley
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5532 Visits: 2646
Excellent stuff!

Have downloaded, installed, and now ready to play. Thank you for sharing these.


Kev
David-Leibowitz
David-Leibowitz
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 78
Several points to note:
1. 2005 is different than 2008 in schema and the type of metrics available. 2008 has more information.
2. If your solution does not use a datawarehouse and goes directly to the ezecutionlog, the usage metrics are only as good as the history. The default is 60 days.

A solution exists on codeplex called sqlscrubs to extract all of that information from reporting services 2008 to drive actionable report optimization. It includes the necessary datawarehouse, ssis package and set of starter reports.

Http://scrubs.codeplex.com



.

SQLScrubs: SSRS Audit, Optimization & Management reports
denglishbi
denglishbi
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3987 Visits: 1364
Very nice article and great job on the reports. Wasn't sure if you were aware of it at all, but with the SSRS Samples on CodePlex there is an SSIS package and Reports that you can leverage, Report Execution Log Files. Doesn't provide everything you have provided, but you might want to check it out if you haven't seen this before. The only reason that you would want this is because you will want to offload the Execution information since the default setting is to only keep 60 days of history.

There are other Report Packs available for SSRS 2005 that you can check out here on the Microsoft downloads SQL Server 2005 Report Packs.

Thanks for sharing your work. Once again very nice.

----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57991 Visits: 8824
Thanks for sharing your work!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
INNOCENT GUMBO
INNOCENT GUMBO
SSC-Enthusiastic
SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)SSC-Enthusiastic (178 reputation)

Group: General Forum Members
Points: 178 Visits: 469
Michael, we used to fight with Develpoers on why the report has failed. Now you can see on the Subreport "ReportErrors". Now we can see who went on to give himself power to run and edit reports.


A very nice piece of Work. Especially on your introduction of how you used to take time to establish the problem, now you have made a master piece which can manifest within seconds.

I did test with SQL 2005 and it seems as if I did develop these reports on my own!!!:-P

If possible just give me any hints about Reporting Services. I am not much concentrating on Reporting on my Work but today I have changed my mind.

Michael thanks for changing other people's minds also. I will be on Reporting from now on.

Thanks
roger_os
roger_os
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 568
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
michael_davis2
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 163
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
AlAndrew
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 59
Wonderful addition to my reporting. Easy to install and use. Thanks for sharing!



Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)SSC Guru (78K reputation)

Group: General Forum Members
Points: 78638 Visits: 14969
Great article and useful information. As was shared earlier, by default SSRS only stores execution information for 60 days.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
roger_os
roger_os
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 568
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search