This article is an extension to the blog entry “SQL Server 2005 Performance Dashboard Reports” by Rakesh. In his blog, Rakesh introduces to you about an add-in which contains a set of dashboard reports predesigned and made available by Microsoft. I am extending the same to show you how we can design our own custom reports and use them as dashboard reports in SQL Server Management Studio (SSMS).
For the demo, I am using the query in the blog post “How to find the free space in Database files?“ by Sarabpreet which shows the free space in the data and log files for a particular database.
As the purpose of this article is just to show a trick to access a custom report through SSMS, I am not showing here in detail how to create a report. Instead, I assume that the readers are familiar with using reporting services to design simple reports. The report which I have designed is available on the site in the files section with the name “DBSpaceUtilization.rdl” and you can download the same from here.
Before showing the trick, would like to ask the audience if they have ever noticed that these dashboard reports show the information of the server in the context of which they run. Meaning, how do you access these reports; by right-clicking the server / database name and selecting the Reports –> Custom Reports option. Then the report shows the information pertaining to that server or database. So the question is how the report knows on which server it should run and from which database it should fetch the information and using which credential.
The trick is that once you design and test the report, just remove the connection string and save the report. This rdl file you can now access using the Reports –> Custom Reports option in SSMS which is available on right-clicking the objects in Object Explorer.
Following steps shows the changes to be made in the Connection string in the reports .rdl file to make the trick work. Open the .rdl file using Business Intelligence development Studio and follow the below steps,
Step 1 – Open the Data tab, select the dataset and click on the button besides the dataset dropdown to open the Dataset dialog box.
Step 2 – In the Dataset dialog box click on the button besides the Data source dropdown to open the Data Source dialog box.
Step 3 – Remove the connection string entry in the “Connection string” text box and click on the OK.
Step 4 – An error message box will be displayed informing the connection string issue. Ignore the message and click on OK.
You are now ready with the report. Just save the report and close the project.
To run the report,
- Copy the report .rdl file to the folder where you have kept all the custom reports you would like to access using SSMS. (This is for the management purpose only and is not a required step)
- Open SSMS and connect to the server in Object Explorer using the credentials which has enough rights to run the report’s query.
- Expand the Databases folder to view all the databases available.
- Right click on any database name and select Reports –> Custom Reports option from the shortcut menu.
- Browse the report and click on Open in the Open File dialog box. (This you have to do for the first time only. From the next time the report name will start appearing in the Reports sub menu.
The report executes using the credentials provided in step 2 to connect to the server and executes the query within the database selected in step 4. (I have selected the AdventureWorks database to run the report.)
You have to understand here the context in which you should run the report designed. Meaning this report in demo is to be run in the context of a particular database. Similarly some of the performance reports should be run by right-clicking the server name and so on.
Hope you enjoy this. I know that many of you must be having good scripts to monitor server / database state or performance etc. and you can present the results using your own custom reports in SSMS.
Regards
Amit Karkhanis
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
