Building Custom Reporting Services Reports for SQL Server Management Studio - Page 2

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:

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:

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:

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