15 November 2006 14:35 antxxxx

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 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)

Comments

# re: Custom reports in management studio

17 November 2006 02:40 by Paul Mestemaker (MSFT)

Nice post... thanks for evangelizing one of our new features.  Just FYI, you should change the details described above.  We will be accepting .RDL files in the future, *not* .RDLC.  It was a bug we couldn't fix before the November CTP.  I will be posting a blog on this in the next couple of weeks when I wrap up a couple of other projects in flight.

For the November CTP, you should make an .RDL and rename it to .RDLC.  However, in the final version, we will accept .RDL and not .RDLC.  We have a books online topic that will be posted with our final version about the limited support we have for some of the Reporting Services functionality (i.e. SSMS doesn't support SubReports, expressions in report parameters, and maybe a couple of other items).

Thanks!

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

# Custom SSMS Reports in SP2 - Database Permissions

21 November 2006 18:58 by sqldbatips.com blog

Once of the great new enhancements in Management Studio in SP2 (get the CTP here ) is the ability for

# Jasper Smith, Colin Leversuch Roberts, Anthony Brown and Neil Hambly now blogging on SQLBlogCasts.com

22 November 2006 08:40 by Tony Rogerson's ramblings on SQL Server

Anthony Brown talks about the new custom reports in management studio that is a new feature in the SQL

# Custom reports in SQL 2005 SP2

29 June 2009 17:16 by SimonS Blog on SQL Server Stuff

You may have come across the fact that in SP2 you can deploy custom reports that are accessible in management