Anthony Brown

One of the things I really like in sql 2005 is the management reports available from within management studio.

In sp2, there is the ability to add your own report. However the documentation seems a bit sparse on how to do this, so here is a quick overview from what I have managed to find out. A lot of the information I found out by looking at the existing reports which I downloaded from http://blogs.msdn.com/sqlrem/archive/2006/08/30/SSMS-Reports-3.aspx

The reports need to be in rdlc format which is the format created when you create one for a report viewer control (ie a report embedded in another application). There are some notes on how to convert between rdl and rdlc files at http://msdn2.microsoft.com/en-us/library/ms252109(VS.80).aspx. I have found that for importing reports into management studio, you can just create an rdl file and then change the extension to rdlc.

There are some parameters passed to the report when you run it. You can use these to customize or get data for your report based on where in management studio the report was executed from. The parameters that I know about are

ObjectName

ObjectType

ServerName

FontName

DatabaseName

Most of them are fairly self explanatory.

The ObjectType parameter is annoying in that it gives you the type of the object (according to management studio) from where you ran the report. This means that if you run a report against, for example, the tables folder, instead of passing in tables as the object type, it returns folder.

The fontname just seems to be included to allow easier customization of the reports.

No matter what you set in the connection string, when you run the reports in management studio, they always seem to run against the server and database you are connected to. In fact, you can manually edit the rdlc file and remove all details from the connectString part of the data source so you are left with something like

    <DataSource Name="DataSource1">

      <ConnectionProperties>

        <ConnectString></ConnectString>

        <DataProvider>SQL</DataProvider>

      </ConnectionProperties>

      <rd:DataSourceID>b9ec3aac-9b6d-4f5e-96da-e43d7a094bfd</rd:DataSourceID>

    </DataSource>

(your datasource name and datasourceid will probably be different).

I have uploaded a simple report called TestReport.rdl. The report just lists the parameters passed into it and runs select @@servername, db_name() against the server to show where it is running.

You can view the design for this in visual studio 2005 as you would any other report.

To run the report in management studio, you must have sp2 installed.

First, rename the file so it has a .rdlc extension. Then right click somewhere in object explorer and select reports->custom reports. You will get an open file dialog box so just browse to where the file is and open it and the report will run.

One nice feature is once you have run a custom report from a specific place (eg against one table, or against one server), the reports stay in a recently used list against that object type, so you don’t have to keep selecting custom reports and browsing for the file if you want to run the report against another object of the same type.

You can run this report pretty much everywhere to see examples of the information passed into it (you can even run it against certain nodes on a sql 2000 database)