One of the most useful tools shipped with SQL Server Management Studio is the standard reports. Owning the source code in these reports could satisfy a number of needs in your organization. Perhaps you want to:
Publish these reports in the Reporting Services site so users will be able to view them without having to install SQL Server Management Studio.
Allow users, without all the required permissions at the SQL instance and database level, to view the reports.
Modify such a report according to specific needs.
Maybe you want to add more customized reports to those already existing in SQL Server Management Studio. One example of this could be a report that imitates the "Taskpad" view of a database, a feature available in SQL Server 2000 Enterprise Manager and no longer exists in the Management Studio.
In the following steps you'll see how to get the reports' source code and how to add custom reports to the Management Studio.
How to view the reports from Management Studio
First let's see how to run the reports from the Management Studio. You can view reports at several levels: SQL Server instance level, database level, logins, replication agents and more.
Prior to SP2, under the Summary folder, click Reports:
For example, at the SQL Server instance you will get the following reports:
In SP2, right click on a node in the Object Explorer, choose Reports → Standard Reports.
At the database level, right click on the database:
Downloading the reports for Reporting Services
In this SQL Server Manageability Team Blog there is a link to a SSMS Standard Reports zip file where you can download the source code of these reports and more.
After downloading and extracting the files from the .zip file, follow these steps:
- Open the solution (.sln) file in the SQL Server Business Intelligence Development Studio.
- Open the Shared Data Source master.rds and change it to connect to your SQL Server 2005 instance.
Now that you have the sources, you can use them to: build and deploy a Reporting Services solution, run reports, publish reports, change reports, etc.Adding customized reports to the Management Studio reports
In SQL Server 2005 SP2 you can also add more reports to Management Studio.
In the object explorer of Management Studio, right click a node, choose Reports → Custom Reports.
For example, at the database level, right click a specific database:
Get more information about custom reports in Management Studio here.
Microsoft allows you to download the solution and .rdl files of the SQL Server Management
Studio standard reports. Since the release of SP2, you can also add your own reports to the existing ones in the Management Studio.
Problems to expect in the future are related to applying new patches and SQL versions. Are we going to find the new source code of the standard reports after being modified in future patches? When we apply a new patch or version, will we still be able to find our custom reports in place?
To "stay on the safe side," keep all the rdl files on-hand (backed-up or in a version control system) and search for new versions when a new patch is applied. Also, document all the custom reports added to the Management Studio for future reference, if needed.
| ABOUT THE AUTHOR: |
|
Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the last 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, administrative and infrastructure tools development, reporting services and more.
Copyright 2007 TechTarget
|
|
