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 Im
going to show you how you can use additional object node properties to build a
more meaningful report.
First, lets 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 wouldnt be have been populated
because I wasnt on, or under a database node when I opened my Hello World
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
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 Im 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 Im 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 Im 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 Im 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
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
Pearsons 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