Building Custom Reporting Services Reports for SQL Server Management Studio

Wednesday Jan 30th 2008 by Gregory A. Larsen
Share:

Learn how to build Custom Reports, without installing Reporting Services, using a new feature found in Microsoft Service Pack 2 (SP2) for SQL Server 2005.

When Microsoft released Service Pack 2 (SP2) for SQL Server 2005, they added a new feature called Custom Reports. This new feature allows you to build Reporting Services reports that can be rendered within SQL Server Management Studio (SSMS). There is no requirement to install Reporting Services to use the Custom Reports feature of SP2, this feature is built into SQL Server 2005. With this new feature, you are able to build your own reports to easily enhance the reporting capabilities of SSMS. In this article, I will show you how to build your own Custom Reports.

Hello World Custom Report

You create a custom report just as you would any other Reporting Services report. You start by opening up a Visual Studio Reporting Services Project. The following screen shot shows the options I have selected for my new Visual Studio project for building the Hello World Custom Report:

Click for larger image

Here I have named my project “CustomReportDemo” which will be saved in the “C:\Temp\” directory. By clicking on the “OK” button, my project folder will be created and the following screen is displayed:

Click for larger image

To build my report I have to add a new report to my project. To do this I right click on the “Reports” item in the “Solution Explorer” window. When I do this, I have three different options for adding reports: “Add New Report”, “Add”, or “Import Reports”. The “Add New Report” option will allow me to create a report using the Report Wizard. The “Add” option allows me to create an empty new item or I can add an existing Reporting Services report to my project. The “Import Reports” option allows me to import Microsoft Access Reports. For my Custom Reporting demo, I will be creating new Report Services report from scratch so I will be using the “Add”-“New Item” option.

For my first Custom Report demo, I will create the all too famous “Hello World” report. In order to do this I need to add the report to my solution. So, I select the “Add” item followed by the “New Item” option to create an empty report. When I do this the following “Add New Item” dialog window is displayed where I can specify my report options:

Click for larger image

As you can see, I selected the “Report” template item and then named my new empty report “HelloWorld.rdl”. Clicking the “Add” button will add this empty report to my solution. Upon doing so the following screen will be displayed:

Now that I have an empty report, I need to determine what I want in my report. For my Hello World custom report I want to display the phrase “Hello World from <server name>” where <server name> is replaced with the name of the SQL Server instances in which my custom report is run against. In order to create my report I will need a textbox and a report parameter. First, let me create the report parameter.

The report parameter will be used to hold the name of the server in which my report is run. To create a report parameter for my HelloWorld.rdl report I use the “Report” item on the main menu, than select the “Report Parameters…” from the drop down menu. When I do this, the following screen is displayed:

On this screen, I can create my report parameter, which I will call ServerName. To do this I click on the Add button. When I do this the “Properties” items become available, and I enter “ServerName” in the “Name:” item. I don’t need to specify anything else, so I click on the “OK” button. Now to finish my report I just need to create a textbox and populate it with my dynamic hello world string. To do this I drag a textbox item from the toolbox onto my report body surface, and stretch it a little to hold my hello world string. When I’m done my screen looks like this:

Now to populate that text box I need to build the expression that will render my hello world string in the textbox. To do this I click on the textbox to bring it into focus and then right click and select the “Expression…” item from the list of options. When I do this, the following screen is displayed:

Now I can build my hello world string by first typing “Hello World from “ followed by a plus sign (+) next to the equal sign (=) in the upper portion of the “Edit Expression” dialog window. When I do this my dialog window looks like this:

With the cursor to the right of the plus sign (+) I can add my ServerName parameter variable. To do this I click on the “Parameters” item in the lower left hand pane, which then displays my parameter in the lower right pane of the above screen shot. To add my ServerName parameter to the expression I just need to double click on it. After doing this the Edit Expression window looks like this:

Now I just click on the “OK” button to complete the building of my expression that will populate my text box when I render my HelloWorld.rdl file.

At this point, I am done building my Hello World report. I now save my report, and exit out of Visual Studio.

To display my custom Hello World report I open SSMS and bring up the Object Explorer view. To demonstrate how my report dynamically changes the Server Name I connect to two different instances running on my laptop: SDSSQL01 and SDSSQL01\SQLEXPRESS as the following screen shows:

To render my custom report I will right click on one of my SQL Server nodes: SDSSQL01\SQLEXPRESS. Then I hover over the “Report” item and another drop down is displayed. In this second drop down, I click on the “Custom Report…” item, which displays an “Open File” dialog box as shown below:

Here I can browse to where I saved my HelloWorld.rdl file, which in my case is “C:\temp\CustomReportDemo\CustomReportDemo\HelloWorld.rdl”. Once I have selected my rdl file I click on the Open button in the “Open File” dialog. This brings up the following warning:

To render my Hello World report I click on the “Run” button. It takes a few seconds to render the report. Once the rendering is done, the following report is displayed:

Here you can see my report says “Hello World from SDSSQL01\SQLEXPRESS”. Now if I change the context of my report, to my default instance named “SDSSQL01”, and bring up my custom Hello World report again the message displayed will be “Hello World from “SDSSQL1”. How does this occur? The Custom Report feature of SP2 provides the facility to dynamically populate a number of different report parameters, one of those being ServerName, more on this in the next section. Therefore, every time I render this report the report determines what Object Explorer node I rendered it from and sets the ServerName parameter appropriately.

Using Object Explorer Node Properties in Custom Reports

Like most Hello World examples, they are not very useful in the real world but only show a small example of how something works. In my Hello World example I showed how to use a single Object Explorer node property, “ServerName”, to dynamically display different SQL Server instance names depending on what node I was on when I rendered my Hello World report. Now I’m going to show you how you can use additional object node properties to build a more meaningful report.

First, let’s review what object node properties are available that you can use in your reports. Here is a list of the object node parameters that SSMS populates:

Parameter Name

Data Type

ObjectName

String

ObjectType

String

Filtered

Boolean

ServerName

String

FontName

String

DatabaseName

String

When you open up a custom report, each parameter name is populated with the appropriate value based on where you are at in the SSMS object nodes when you open the Custom Report. So if you open your custom report at the server level, as I did in my above example the ServerName parameter was populated with the current SQL Server instance name I was on. However, if I was to use the DatabaseName parameter it wouldn’t be have been populated because I wasn’t on, or under a database node when I opened my Hello World report.

To better understand how this works let me build a report that uses a few of these parameters. My new report will display ObjectName, ObjectType , ServerName, and DatabaseName. My new report will be called “DisplayObjectNode.rdl”. You can download it from here: DisplayObjectNode.rdl if you want to test it out.

To started building this report I add a new report to my Visual Studio project and named it “DisplayObjectNode.rdl”. I then added parameters to my report. After I’m done adding parameters the Report Parameter window looks like this:

Click for larger image

To use these parameters I will create a table of textboxes in the body of my report. Each row in my table will show the value of one of these parameters when my report is rendered. After I create my table my reporting body looks like this:

Click for larger image

As you can see, I used the “Parameter” reference for each of the different parameters in the appropriate row within my table. Now when I render this report I will be able to see what SSMS populates in each parameter based on the node that I’m on when I open the report.

When I display this custom report from the Server Node, the following report is displayed:

Here you can see that “Object Name” and “Server Name” are the same. This is because I was at the server level node of the SQSSQL01 instances when I open the “DisplayObjectNode.rdl” report. Notice the object type for this node is “Server”.

Below is what is displayed when I run my report from the Database node level:

So now, when I’m on the database folder you can see the ObjectName and Server name are still “SDSSQL01”, but the “Object Type Name” is now “Folder”. No DatabaseName is displayed because I’m not on a specific database node. If I expand the database folder and bring up my report while an actual database is highlighted this is what this report displays:

Now it shows that the “Object Name” is “AdventureWorks”, which is the same as the database node I am on when I opened the custom report. In addition, the “Object Type Name” is now “Database”.

The report parameters are set dynamically depending on where you are at in the Object Explorer node when you open your custom report. You can use this information to help dynamically control what your report displays. In the next section, I will provide a report that takes advantage of Object Explorer Node properties to display different report content.

Displaying Record Counts

I have developed a Reporting Services report that displays record counts for each table in a database. This report is called RecordCounts.rdl and can be downloaded from here (RecordCounts.rdl). When I run this report from a node within the “AdventureWorks” database, it will display record counts for tables within the “AdventureWorks” database. When I open up this report from the context of the “master” database, my report will display record counts for all the tables within the master database.

Here is what the report looks like within Visual Studio:

When I run this report against the “AdventureWorks” database this is what it displays:

As you can see, it shows one row of output for each table in the AdventureWorks database.

If I were to run it against another database is would display a list of record counts for whatever database I was on when I opened up the report.

Conclusion

Using the custom report convention, you can build your own SSMS reporting solutions. In fact, Microsoft has built a tool called the Performance Dashboard that uses the Custom report feature to bring performance reports to the DBA. To learn more about the performance dashboard read William Pearson’s article “Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I”. If you have a need to develop some reports to help you better manage your SQL Server environment, then you might consider trying to build them using Reporting Services. Doing this will allow you to integrate your reports directly into SSMS.

» See All Articles by Columnist Gregory A. Larsen

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved