Creating Custom Reports for SSMS

With the release of SQL Server 2005 SP2, Microsoft added a new feature to SQL Server called “Custom Reports”. ). These RDL files are rendered as tabbed documents within SSMS. Reporting Services reports are rendered without even having Reporting Services installed. This new feature allows DBAs to extend the reporting capabilities within SSMS. In this article I will show you how to build a custom reports and how to use Object Explorer node parameters to build context sensitive custom report.

 

Building a Simple Custom Report

All that is needed to build a custom report is to create an .rdl file that contains your report. An rdl file is just a file that contains a report definition, where the extension is .rdl. You could build this text file with Notepad if you understood the .rdl tags that make up a Reporting Services report. But I prefer to use “SQL Server Business Intelligence Development Studio (BIDS)”. The BIDS tool is a stripped down version of Visual Studio that is installed when you load the SQL Server 2005 Client Tools. Let me walk you through building a simple custom report that will be display in SSMS.

To start the BIDS tool, I click on the “Start” button and then navigate to the “Microsoft SQL Server 2005” item under “All Programs”. In the drop down menu I click on the “SQL Server Business Intelligence Development” item. This brings up the Visual Studio application. From the “File” menu, within Visual Studio, I then expand the “New” item and click on the “Project” item in the drop down menu. Doing this brings up the “New Project” dialog window, which on my machine looks like this:

On the screen I will select the “Report Server Project” template, under the “Business Intelligence Projects” item. Once I entered the appropriate information in the Name, Location and Solution items I then click on the OK button. When I do this Visual Studio development window comes up.

On the Visual Studio window I make sure the “Solution Explorer” pane is displayed. If isn’t already displayed I can make it visible by selecting it from the “View” menu or using the Ctrl-Alt-L shortcut keystroke. To start building a report I right click on the “Reports” items and then select the “New Item …” option from the drop down. This will bring up the following New Item window:

On this window I have the option to use the “Report Wizard” or the “Report” item to create a new report. For my demo I will just be building a simple report, that I want to format myself, so I will select the “Report” item and give my new item a name of Demo1.rdl. Once I’ve added my new item my visual studio window looks like this:

For my demo report I will display a very simple report, that just display the SSMS object explorer node information that I’m on when I request my Demo1 custom report to be rendered. In order to accomplish this I will use some pre-defined custom report parameters. These pre-defined custom report parameters can be used in any custom report to help determine what node within the Object Explorer tree I am on when I right click and render a custom report. Here is a complete list of the pre-defined Object Explorer report parameters and data types available to a SSMS reports:

Parameter Name	Data Type
ObjectName	String
ObjectTypeName	String
Filtered	Boolean
ServerName	String
FontName	String
DatabaseName	String

 

For my demo report, I will display the name of the database, the object name and the object type of the node used to launch my custom Demo1 report. Let me walk though what it takes to use these pre-defined parameters to display the database name, object name, and object type information in my report.

To add these pre-defined parameters I first click on the “Layout” tab for the “Demo1.rdl” report item to bring it into focus. I then select the “Report Parameters…” item under the “Report” item from the menu. When I do that the following screen is displayed:

On the above screen, I use the ADD button to add each of the pre-defined parameters I want to use. After I have added all three parameters my “Report Parameters” screen looks like this: