Creating a Custom Report for SSMS
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.
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
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|
Next lets open it in SSMS
Connect to a server in Object Explorer
Right click on the Instance -> Reports -> Custom Reports
Change the path location to where you project folder is and select the CheckDBDuration.rdl
You will get the below warning. Click Run if you are happy to run the report:
You should then see the report load up for the Instance that you ran it against!
(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