Building a Simple Custom Report
All that is needed to build a custom report is to create an .rdl file that contains your report. An rdl file is just a file that contains a report definition, where the extension is .rdl. You could build this text file with Notepad if you understood the .rdl tags that make up a Reporting Services report. But I prefer to use “SQL Server Business Intelligence Development Studio (BIDS)”. The BIDS tool is a stripped down version of Visual Studio that is installed when you load the SQL Server 2005 Client Tools. Let me walk you through building a simple custom report that will be display in SSMS.
To start the BIDS tool, I click on the “Start” button and then navigate to the “Microsoft SQL Server 2005” item under “All Programs”. In the drop down menu I click on the “SQL Server Business Intelligence Development” item. This brings up the Visual Studio application. From the “File” menu, within Visual Studio, I then expand the “New” item and click on the “Project” item in the drop down menu. Doing this brings up the “New Project” dialog window, which on my machine looks like this:
On the screen I will select the “Report Server Project” template, under the “Business Intelligence Projects” item. Once I entered the appropriate information in the Name, Location and Solution items I then click on the OK button. When I do this Visual Studio development window comes up.
On the Visual Studio window I make sure the “Solution Explorer” pane is displayed. If isn’t already displayed I can make it visible by selecting it from the “View” menu or using the Ctrl-Alt-L shortcut keystroke. To start building a report I right click on the “Reports” items and then select the “New Item …” option from the drop down. This will bring up the following New Item window:
On this window I have the option to use the “Report Wizard” or the “Report” item to create a new report. For my demo I will just be building a simple report, that I want to format myself, so I will select the “Report” item and give my new item a name of Demo1.rdl. Once I’ve added my new item my visual studio window looks like this:
For my demo report I will display a very simple report, that just display the SSMS object explorer node information that I’m on when I request my Demo1 custom report to be rendered. In order to accomplish this I will use some pre-defined custom report parameters. These pre-defined custom report parameters can be used in any custom report to help determine what node within the Object Explorer tree I am on when I right click and render a custom report. Here is a complete list of the pre-defined Object Explorer report parameters and data types available to a SSMS reports:
|
Parameter Name |
Data Type |
|
ObjectName |
String |
|
ObjectTypeName |
String |
|
Filtered |
Boolean |
|
ServerName |
String |
|
FontName |
String |
|
DatabaseName |
String |
For my demo report, I will display the name of the database, the object name and the object type of the node used to launch my custom Demo1 report. Let me walk though what it takes to use these pre-defined parameters to display the database name, object name, and object type information in my report.
To add these pre-defined parameters I first click on the “Layout” tab for the “Demo1.rdl” report item to bring it into focus. I then select the “Report Parameters…” item under the “Report” item from the menu. When I do that the following screen is displayed:
On the above screen, I use the ADD button to add each of the pre-defined parameters I want to use. After I have added all three parameters my “Report Parameters” screen looks like this:
