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:

Now I click on the “OK” button to return to the layout view. To have the report display the DatabaseName, ObjectName and ObjectType when rendered, I dragging the Textbox item from the Toolbox onto the layout surface 6 different times. The first three textboxes are formatted with labels. When I’m done dragging the textboxes, aligning them and formatting the label textboxes my layout tab looks like this:

Next I need to use the DatabaseName, ObjectName and ObjectTypeName parameters to populate the other three textboxes in my report. To do this I right click in the first empty text box under the DatabaseName heading and select the “Expression…” item. When I do this the following screen is displayed:

The object node parameters I created earlier can be found under the Parameters item in the lower left hand pane. To add them I first expand the Parameter item by clicking on it. When I do that the above window is changed to look like this:

Now I can see the three different parameters I created in the right most pane at the bottom of this window. All that is needed to populate the textbox expression is to double click on the DatabaseName item. When I do that the expression for DatabaseName populates the textbox cell with the following value “=Parameters!DatabaseName.Value”. To complete the operation of setting the value of the DatabaseName textbox I just click on the OK button. I repeat this procedure to populate the other empty text boxes with the ObjectName, and ObjectTypeName parameters. Another alternative to populating the value of these cells would be to just type in the value expression in the property for each textbox.

At this point my report is ready to go, so I just close out of Visual Studio and save my report definition (Demo1.rdl) to my project folder. To display the Demo1.rdl report, I must first bring up SSMS, expand the Object Explorer tree, and then expand the Database item. Once the database item is displayed, I then right click on the AdventureWorks database (I could have clicked on any database), hover the mouse over the “Report” item, and then select the “Custom Report” item. This brings up the “File Open” window which I use to browse to where I stored the Demo1.rdl file. Once I find and select the Demo1.rdl file I click on the “Open” button. Doing this brings up my custom report in SSMS. When I do this on my laptop machine my Demo1 report looks like this:

As you can see my report shows the “Database Name” as “AdventureWorks”, the “Object Name” as “AdventureWorks” and the “Object Type” as “Database”. These parameter values will be set appropriately depending on what node of the object tree is used to open the custom report. So to show how my report will change based on the Object Explorer node, here is the Demo1 report that was generated when I open it from the “dbo.Customer” table node from inside the AdventureWorks database from within the Object Explorer:

Here you can see that now my Demo1 report shows the “Object Name” as “[dbo].[Customer]” and the “Object Type” is “Table”.

By using the Object Explorer node parameters you can customize your reports to be parameter driven and function differently depending on the node you are on when you render a custom report. In the next section of this article I will show you how to use these parameters to build an Object Explorer parameter driven report.

If you want test out my Demo1.rdl file for yourself the code can be found here: demo1.rdl. Just save this code on your machine and then open it with the custom report option from within Object Explorer.

Parameter Driven Reports

To demonstrate how you might use the object node parameters to make a parameter driven report, I will show you a report I built that displays the record counts for every table in a database. This report detects which database you have highlighted in the Object Explorer when you bring up this custom report, and then reports on the record counts for each table in that database. Now I could show you a couple of screen shots on how to build and use this report, but instead I would suggest you try the “RecordCount.rdl” file yourself to report on the record counts in one of your SQL Server 2005 SP2 databases.

In order to perform this demo, you must first save the “RecordCount.rdl” in the above hyperlink to a location where you can get at if from SSMS. After you have saved my rdl file, go to SSMS and right click on a database for which you what to display record counts. When the task list is shown hover over the “Reports” item, and click on the “Custom Reports” item from the drop down window. When the “Open File” window is displayed browse to the “ReportCount.rdl” file you saved and open it. This will start the rendering process for this report. Once the report is rendered you should see the record counts for the database you where on when you initiated the custom report process. Here is what this report looks like on my machine when I bring up this report when I have the AdventureWorks database is in focus:

To see the record counts for another database, navigate to a different database node in SSMS, and bring up the custom “RecordCount.rdl” report again. This time you should see a similar report, but the records count being displayed will be for the tables in the new database you selected.

There is a limitation in using parameters. Parameters are only populated for the first report rendered. If you try to use one of the node parameters in a drill down report it will not be populated with a value when the report is rendered. The work around for this limitation is to pass any object node parameters you need in your drill down report from the .rdl report that is first rendered when you select a custom report.

Another unusual behavior I found regarding custom reports is that the data source and database you define in your reports are not used. The custom report process overrides your data source information with the node information from SSMS you are on when rendering the report. In fact you don’t even need to define a data source for a report if you don’t want to. This functionality is great for building reports where you want to select data based on the database context when rendering the report. But if you want your query to run against a specific database this is a problem. The work around for this is to use a three part naming convention (..