15 November 2006 14:35
Custom reports in management studio
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
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
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
(your datasource name and datasourceid will probably be
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
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)