Having started creating a couple of reports that are a DBA related, I thought it might be interesting to add them to Management Studio. That brought with it a couple of interesting questions..

How do you make it dynamic and read from the current server / database that im using (Guess its to do with parameters! but what are the names!)?

Do the reports have to go in a specific folder?

Well the answers were actually very easy to find and setting up / updating a report to work with SSMs is a short simple process. Microsoft has got some good documentation on it which you can find at the bottom of the page.

From here, ill take you through the steps that I toke to change the CHECKDB duration report (https://sqlnotesfromtheunderground.wordpress.com/2013/11/08/knowing-your-checkdb-duration-times-using-ola-hallengren-maintenance-scripts-report ) and set it up to work as a custom report in SSMS.

Download the original CheckDBDuration.rdl from the blog post above.
http://sdrv.ms/IDGQuS

Open an existing project with your DBA Reports or create a new Project.

Add an Existing file to the project and add the downloaded CheckDBDuration file
Right click on Reports in the solution explorer -> Add -> Existing Item… (Shift + Alt +A)

Open the data sources and Edit DynamicSource, Update it to

Data Source=.

CustomReport1

Save the File and Exist SSDT

Thats it. we have updated the data source to follow how the custom reports work with SSMS and that answers Question 1, Note there are options that you can make dynamic

Parameter name Data type
ObjectName String
ObjectTypeName String
Filtered Boolean
ServerName String
FontName String
DatabaseName String

(http://technet.microsoft.com/en-us/library/bb153743.aspx)

Next lets open it in SSMS

Open SSMS

Connect to a server in Object Explorer

CustomReport2

Right click on the Instance -> Reports -> Custom Reports

CustomReport3

Change the path location to where you project folder is and select the CheckDBDuration.rdl

CustomReport4

You will get the below warning. Click Run if you are happy to run the report:

CustomReport5

You should then see the report load up for the Instance that you ran it against!

CustomReport6

(Not on this dev box i hadnt set up the Ola’s DatabaseIntegrityCheck on a schedule so only have 1 result set.)

This answers Question 2. You can read the custom reports from any location. (Properly not the best idea to have them scattered around though so either create your own custom report folder or place them with other reports (ie location of the Server Performance Reports.)

Remember this report is based off Ola Hallengren’s maintenance script  (http://ola.hallengren.com/) so if your not using them, or using them and not logging to table, the report wont work and you will see an error saying it cant the table. Master.dbo.CommandLog

If you want to get the updated SSMS custom report version of CheckDBDuration, you can here: http://sdrv.ms/1eVahEp

Links:

Add a Custom Report to Management Studio

http://technet.microsoft.com/en-us/library/bb153687.aspx

Use Custom Reports with Object Explorer Node Properties

http://technet.microsoft.com/en-us/library/bb153743.aspx

Microsoft Example Custom Reports

http://msftrsprodsamples.codeplex.com/

Advertisements