Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 30, 2008

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

By Gregory A. Larsen

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:

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM