How to create Custom Reports for SQL Server Management Studio

In this article I will describe in details (including some examples) how you can build your own SQL Server Management Studio reports that are loaded from Object Explorer. The reports can easily be used on SQL Server 2005, 2008 and 2008R2 environments.

1. Requirements

Firs of all let me start with the basic requirements of the environment you need setup so you can produce such reports. Do not get too scared, it is not that big list :) It is actually just one simple application called BIDS (ha ha, yes, this is Microsoft’s Business Intelligence Development Studio, included in SQL Server installation media). But not just any BIDS, but 2005. SSMS have never been upgraded to use the 2008 report viewer component, so the only way your report to be loaded is if it is written on BIDS 2005. For further reference you can check this Connect article.

2. Behind the scenes

One of the most important things you should know about building custom reports is that at runtime (when the report is rendered/executed in SSMS) there are several predefined parameters passed from Object Explorer (OE) to the report and which you can use freely. The parameters and their short explanation you can find below:


Parameter Name

CLR data type

Comments

ObjectTypeName

String

The type of object. For example, “Database”, “Login”, “Functions”.

ObjectName

String

The name of the object. For example, “Foo”, “AdventureWorksDW”, “GetUserIDFromName”, etc.

ErrorText

String

Used in the Default report to show error information.

Filtered

Boolean

This was used to indicate whether the dataset being passed from OE is filtered or not. We will respect the filters the user has in place in OE and this parameter allows us to indicate on the list reports whether the list is filtered.

ServerName

String

Name of the server and instance currently connected. In the form of “serverinstance” for a named instance and “server” for a default instance.

Prompt Name: ServerName

Allow Null:    checked

Allow Blank:  checked

Avail Values: none

Defaults:       none

FontName

String

Name of the font to be used to display the report.

Defaults:       Non-Queried – “Tahoma”

DatabaseName

String

Name of the database containing the current object. If the object is not database scoped, this value will be an empty string.

 

 

As you can see those parameters are the key to create dynamic reports on lower level than SQL Server instance (like database or table).

3. How to start

First you can start with creating a BIDS Report Server project. Having that done, create a Shared datasource to the master database of your SQL instance. Name it “master.rds“.

Second – create a report template, that you can use for further starting point for each report. In this template you have to only setup the parameters. Go to Report -> Parameters and insert all parameters using the info at the above table. Make sure you use the same names and caption. After you are done with that task, your parameters screen should look something like this:

clip_image001

You can also use the below XML, paste it into a text file and rename it to .rdl, add it to your BIDS solution and use it as a template.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <InteractiveHeight>11in</InteractiveHeight>
  <ReportParameters>
    <ReportParameter Name="ObjectTypeName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ObjectTypeName</Prompt>
    </ReportParameter>
    <ReportParameter Name="ObjectName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ObjectName</Prompt>
    </ReportParameter>
    <ReportParameter Name="ErrorText">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ErrorText</Prompt>
    </ReportParameter>
    <ReportParameter Name="Filtered">
      <DataType>Boolean</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>Filtered</Prompt>
    </ReportParameter>
    <ReportParameter Name="ServerName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>ServerName</Prompt>
    </ReportParameter>
    <ReportParameter Name="FontName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <DefaultValue>
        <Values>
          <Value>"Tahoma"</Value>
        </Values>
      </DefaultValue>
      <AllowBlank>true</AllowBlank>
      <Prompt>FontName</Prompt>
    </ReportParameter>
    <ReportParameter Name="DatabaseName">
      <DataType>String</DataType>
      <Nullable>true</Nullable>
      <AllowBlank>true</AllowBlank>
      <Prompt>DatabaseName</Prompt>
    </ReportParameter>
  </ReportParameters>
  <rd:DrawGrid>true</rd:DrawGrid>
  <InteractiveWidth>8.5in</InteractiveWidth>
  <rd:GridSpacing>0.25cm</rd:GridSpacing>
  <rd:SnapToGrid>true</rd:SnapToGrid>
  <RightMargin>2.5cm</RightMargin>
  <LeftMargin>2.5cm</LeftMargin>
  <BottomMargin>2.5cm</BottomMargin>
  <rd:ReportID>ddeb9983-9825-4554-9cc9-545bb2680e52</rd:ReportID>
  <PageWidth>21cm</PageWidth>
  <Width>4.75cm</Width>
  <Body>
    <ColumnSpacing>1cm</ColumnSpacing>
    <ReportItems>
      <Textbox Name="textbox1">
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Style>
          <Color>SteelBlue</Color>
          <FontFamily>Tahoma</FontFamily>
          <FontSize>20pt</FontSize>
          <FontWeight>700</FontWeight>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
        <CanGrow>true</CanGrow>
        <Height>0.91429cm</Height>
        <Value>00 Template</Value>
      </Textbox>
    </ReportItems>
    <Height>3.15476cm</Height>
  </Body>
  <Language>en-US</Language>
  <TopMargin>2.5cm</TopMargin>
  <PageHeight>29.7cm</PageHeight>
</Report>

4. Standard SSMS report sources

Finally – from this link you can download all reports that are currently available in Management Studio. Some of those reports can run outside SSMS but some of them cannot due to different reasons. However, inside those reports there are quite valuable queries that you can use in your day-to-day tasks or add them to your script bank.

7 Replies to “How to create Custom Reports for SQL Server Management Studio”

  1. The link to the the zipped file of all the reports you provided us with are very helpful. Does it matter if we use it with 2005 or 2008?
    Thanks!

    1. Hi Komal,
      The only thing that matters is that if you design your own reports you should use BIDS 2005 because of the rendering controls integrated in SQL Server Management Studio. As for the zipped reports – they are already part of the standard reports that you can find in SSMS and work on both SQL Server 2005 and 2008!

      /ivan

  2. Have you tried connecting a query parameter to a custom report parameter with a default value.

    I have a query that has a date parameter, @startDate, this is connected to a report parameter called startDate with a default value of 1 day ago. All works fine in BIDS but in SSMS 2005 you get an error say that ‘startDate’ cannot be found. Works in SSMS 2008.

    1. Well the thing is that when using a query parameter, it is expected this parameter to be somehow entered as an input (in BIDS you can have the Input part when you run the report) while in SSMS everything is predefined and you can use only what is already there. Unfortunately I do not believe there is possibility to add such custom parameters simply because in SSMS the report rendering does not include an Input screen part or something like that.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.