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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 30, 2008

Building Custom Reporting Services Reports for SQL Server Management Studio

By Gregory A. Larsen

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:

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:

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:

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.

MS SQL Archives

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