Creating Custom Reports for SSMS

Now I click on the “OK” button to return to the layout view. To have the report display the DatabaseName, ObjectName and ObjectType when rendered, I dragging the Textbox item from the Toolbox onto the layout surface 6 different times. The first three textboxes are formatted with labels. When I’m done dragging the textboxes, aligning them and formatting the label textboxes my layout tab looks like this:

Next I need to use the DatabaseName, ObjectName and ObjectTypeName parameters to populate the other three textboxes in my report. To do this I right click in the first empty text box under the DatabaseName heading and select the “Expression…” item. When I do this the following screen is displayed:

The object node parameters I created earlier can be found under the Parameters item in the lower left hand pane. To add them I first expand the Parameter item by clicking on it. When I do that the above window is changed to look like this:

Now I can see the three different parameters I created in the right most pane at the bottom of this window. All that is needed to populate the textbox expression is to double click on the DatabaseName item. When I do that the expression for DatabaseName populates the textbox cell with the following value “=Parameters!DatabaseName.Value”. To complete the operation of setting the value of the DatabaseName textbox I just click on the OK button. I repeat this procedure to populate the other empty text boxes with the ObjectName, and ObjectTypeName parameters. Another alternative to populating the value of these cells would be to just type in the value expression in the property for each textbox.

At this point my report is ready to go, so I just close out of Visual Studio and save my report definition (Demo1.rdl) to my project folder. To display the Demo1.rdl report, I must first bring up SSMS, expand the Object Explorer tree, and then expand the Database item. Once the database item is displayed, I then right click on the AdventureWorks database (I could have clicked on any database), hover the mouse over the “Report” item, and then select the “Custom Report” item. This brings up the “File Open” window which I use to browse to where I stored the Demo1.rdl file. Once I find and select the Demo1.rdl file I click on the “Open” button. Doing this brings up my custom report in SSMS. When I do this on my laptop machine my Demo1 report looks like this:

As you can see my report shows the “Database Name” as “AdventureWorks”, the “Object Name” as “AdventureWorks” and the “Object Type” as “Database”. These parameter values will be set appropriately depending on what node of the object tree is used to open the custom report. So to show how my report will change based on the Object Explorer node, here is the Demo1 report that was generated when I open it from the “dbo.Customer” table node from inside the AdventureWorks database from within the Object Explorer:

Here you can see that now my Demo1 report shows the “Object Name” as “[dbo].[Customer]” and the “Object Type” is “Table”.

By using the Object Explorer node parameters you can customize your reports to be parameter driven and function differently depending on the node you are on when you render a custom report. In the next section of this article I will show you how to use these parameters to build an Object Explorer parameter driven report.

If you want test out my Demo1.rdl file for yourself the code can be found here: demo1.rdl. Just save this code on your machine and then open it with the custom report option from within Object Explorer.