Parameter Driven Reports
To demonstrate how you might use the object node parameters to make a parameter driven report, I will show you a report I built that displays the record counts for every table in a database. This report detects which database you have highlighted in the Object Explorer when you bring up this custom report, and then reports on the record counts for each table in that database. Now I could show you a couple of screen shots on how to build and use this report, but instead I would suggest you try the “RecordCount.rdl” file yourself to report on the record counts in one of your SQL Server 2005 SP2 databases.
In order to perform this demo, you must first save the “RecordCount.rdl” in the above hyperlink to a location where you can get at if from SSMS. After you have saved my rdl file, go to SSMS and right click on a database for which you what to display record counts. When the task list is shown hover over the “Reports” item, and click on the “Custom Reports” item from the drop down window. When the “Open File” window is displayed browse to the “ReportCount.rdl” file you saved and open it. This will start the rendering process for this report. Once the report is rendered you should see the record counts for the database you where on when you initiated the custom report process. Here is what this report looks like on my machine when I bring up this report when I have the AdventureWorks database is in focus:
To see the record counts for another database, navigate to a different database node in SSMS, and bring up the custom “RecordCount.rdl” report again. This time you should see a similar report, but the records count being displayed will be for the tables in the new database you selected.
There is a limitation in using parameters. Parameters are only populated for the first report rendered. If you try to use one of the node parameters in a drill down report it will not be populated with a value when the report is rendered. The work around for this limitation is to pass any object node parameters you need in your drill down report from the .rdl report that is first rendered when you select a custom report.
Another unusual behavior I found regarding custom reports is that the data source and database you define in your reports are not used. The custom report process overrides your data source information with the node information from SSMS you are on when rendering the report. In fact you don’t even need to define a data source for a report if you don’t want to. This functionality is great for building reports where you want to select data based on the database context when rendering the report. But if you want your query to run against a specific database this is a problem. The work around for this is to use a three part naming convention (<databases>.<owner>.<object>) to fully qualify the objects you reference in your report.
To find out more about custom reports and other limitations I would suggest you read the “Custom Reports in Management Studio” section (http://msdn2.microsoft.com/en-us/library/bb153684.aspx) in Books Online.
Conclusion
I think one of the best enhancements that came with SP2 of SQL Server 2005 is the ability to easily incorporate Reporting Services reports into SSMS, without having to install Reporting Services. Now DBA’s can build their own reports to augment those provided with SQL Server 2005. There is no longer a need to jump out of SSMS to produce a report. You can just incorporate custom reports right into SSMS. The next time you have a need to build a custom report, build it using Reporting Services and bring it up in SSMS by using the custom report option.
]]>