SQL Server Reporting Services - From query to report!

Sharing Information with SQL Server Reporting Services

If you’ve mastered SQL Server, you’re a database administrator for SQL Server, you’re writing data-driven applications, or you’re just tired of writing SQL statements to retrieve the data your users need and dump them to an Excel worksheet, it’s time to explore a total reporting solution that will enable you to deliver the necessary reports to the right people. SQL Server Reporting Services is such a solution; it’s a flexible, powerful, scalable business intelligence tool. Sure, Reporting Services is also a reporting tool, but this description doesn’t make justice to the product. SQL Server Reporting Services is a system for designing, deploying, managing and delivering reports based on SQL databases.

Reporting Services as a Business Intelligence Tool

SQL Server Reporting Services (SSRS) is a server-based reporting tool and is part of a larger component of SQL Server known as Business Intelligence. SQL Server is a database management system (DMBS) designed to store and retrieve data very efficiently. Designing interfaces to manipulate the data in your database is your responsibility, as a developer, and you can access databases from any language. DBAs manipulate the data in a database using SQL statements. In addition to maintaining information in a database, we should be able to retrieve the data we’re interested in and share it with others. And in most cases, management isn’t interested in long, detailed lists of data. They need aggregated data that convey the most relevant information for their decision making.

The information stored in a database needs to be transformed and shared. And this is where Business Intelligence comes into the picture. The Business Intelligence platform, as it's referred to by Microsoft, contains several components for exploiting and sharing data: Integration Services allow you to integrate SQL Server database with other sources of data, Analysis Services allow you to mine your data in powerful ways and locate facts and patterns that are impossible to infer from raw data. The third component that facilitates the sharing of the information is the Reporting Services.

SSRS is not a tool for building reports (there are many tools in this category). SSRS consists of three main components. To better understand these components let's look at the basic operations of a reporting system. First, it should allow users to create reports. This is the job of the Report Designer. The Report Designer is a developer’s tool for designing reports. Actually, there's no such tool per se, there's an application called SQL Server Business Intelligence Development Studio, which is a development tool for creating all types of Business Intelligence projects. When you create a report, the BI Development Studio is a report designer. The BI Development Studio is a standalone Windows application (it's actually very similar to Visual Studio) that allows you design reports with point and click operations.

You can preview the reports right in the Designer and when you're satisfied with their appearance you can deploy them. Reports are deployed to a server and users can connect to it and view them in their browser. This takes care of the sharing of the information and it's handed by the Report Server. Some of you may be already considering security: should I make my company's information available over the Internet? The Report Server comes with a Web-based interface, the Report Manager, that allows administrators to control which users view which reports and which users are allowed to administer their reports and to what extent. For example, a manager may decide to have certain reports delivered every Monday to his mailbox, or post certain reports in XML format every morning to an FTP server, from which a third party can grab it.

In addition to the Report Designer, you can also download and use a newer report designing tool, the Report Builder. Report Builder has the same functionality as the Report Designer, but it has an Office like interface and it’s addressed to end users (or information workers, as Microsoft likes to call them). Report Builder is not simpler or easier to use, but it doesn’t allow users to access the database directly. A developer must create a data model (which is a DataSet that contains related data) and users can create reports based on this DataSet. The data model abstracts the database and gives users a view of the database that makes sense to them. The users of Report Builder need not understand the structure of the database and perform complicated joins to retrieve the data they desire. The developer designs the necessary queries and populates a DataSet with the information users need to produce their custom reports.

To summarize, the report designing tools allow you to create reports with simple point and click operations and they store the definition of the reports in files with the extension rdl. This is an XML file that describes the report, which is deployed to the Report Server when finished. The Report Server reads this file and renders the report when needed. Obviously, the definition of the report contains no data, just information about the database tables and the SQL statement that retrieve the relevant information. The information about the database and the queries are stored in a so-called data source. The Report Server grabs the up-to-date data when it's asked to render a report, based on the report’s definition file. Then, it renders the data according to the layout stored in the same file. Administrators can manipulate and fine tune the reports and determine who gets to see them through the Report Manager. As you can understand, there are two aspects in developing and deploying reports: the design of the report, which is a developer's task, and the administration of the report, which is the DBA's task. As it frequently happens in small companies, it's the same person that handles both aspects of generating and deploying reports. In this tutorial you'll learn mainly how to design and deploy reports and the basics of administering the Report Server.

Installing the Reporting Services

Reporting Services are installed along with SQL Server 2005/2008. If you haven't installed Reporting Services along with SQL Server, you can run the SQL Server installation program again and select to install additional components. The process is straightforward and Reporting Services will be also configured automatically for you. To install Reporting Services for SQL Server 2005 you must have Internet Information Services installed and running on your computer (or the server on which you will install Reporting Services). After installation, open the SQL Server folder in All Programs, then select\ the Configuration Tools folder under it, locate the Reporting Services Configuration Manager application and run it. This application allows you to configure Reporting Services on your computer and it consists of a single form with 8 tabs, as shown in Figure 1. You shouldn't have to change the default settings, just go to the tab Web Service URL and make a note of the URL of the Report Server's Web Service. You will need to specify this URL in deploying new reports.

Figure 1: Configuring Reporting Services

A URL with the form http://www.servername/ReportServer assumes that you're using the default instance of SQL Server. If you have installed a named instance, it should be listed along in the last part of the URL as follows:

http://servername/SQLInstance$ReportServer

(If you don't know the difference between the default and named instances of SQL Server, you can safely ignore this comment, or ask your database administrator to help you configure Reporting Services).

In this tutorial you will see how to design reports with the Business Intelligence Development Studio. You can also access the Report Builder from within the Report Manager’s interface. The concepts are identical and you don't have to learn two different tools. The skills you acquire with one of the tools will be applied to the other. The Report Builder for SQL Server 2008, however, doesn't come with SQL Server and you will have to download and install from this URL:

http://www.microsoft.com/downloads/details.aspx?FamilyID=cc9acbbd-570c-4712-b74d-85f537a5a75f&displaylang=en

Designing your First Report

Having gone through the basic definitions, let's design a report. We dislike trivial samples that no one will ever use in a practical situation, so your first sample report won't be as simple. Designing a simple report with customer or product names is as simple as dropping a table onto the design surface, but practical reports are never that simple. Our example will contain multiple reports. We'll start with a list of customers alright, but we'll group them by country and city. Each customer's name will be a link to another report with the customer’s orders, and each order number will be a link to the appropriate order, which will be a third report. What you see in the figures are three different reports, linked together with parameters and you can drill down from countries all the way to the details of a specific order. There are hardly any practical reports that don't use parameters to be linked to other reports and a SQL statement of some complexity, so we've decided to show you the approach to building a meaningful, rather than a trivial, report.

Obtain the Northwind Sample Databaseand the sample project

By the way, for this tutorial's samples we'll use the Northwind database, which isn't nearly as complex as the AdventureWorks database. Since we want to focus on the report design process and not get lost in the details of long SQL queries, you should download the Northwind sample database and install it, if you haven’t done so already. Just go to this URL and download an MSI file that you must execute on your computer:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en 

The MSI file will install the scripts for generating the Northwind and pubs sample databases, and you must execute these scripts in SQL Server’s Management Studio.

The sample project we’ll develop in this tutorial in this article is also available for download here (must first Register to download): DataSprings_NWCustomersOrders.zip.

Start the SQL Server Business Intelligence Development Studio application, open the file menu and select File > New > Project. On the New project dialog box that will appear you can select the Report Server Project item to create a new empty report, or the Report Server Project Wizard to have a wizard take you through the steps of generating the report. Click the second option and set the project’s name to NWCustomersOrders. As soon as you click the OK button the wizard will start with a welcome screen. Click Next to see the first screen, where you’re prompted to specify the Data Source (the database from which the data will come from). Create a new data source with the NWDB name and check the box “Make this a shared data source” so you can reuse it in the other projects. If you know the syntax of a connection string, enter it in the appropriate text box. Otherwise, click the Edit button to specify it with another wizard. Assuming that you’re working with the default SQL Server instance on the same machine, the connection string is quite simple:

Data Source=localhost;Initial Catalog=Northwind

There’s no authentication information (user name and password) in this connection string, because it uses Windows Authentication.

Click the Next button and you will see the Design the Query window where you must specify the date you need for your report. If you’d rather work with a visual designer, click the Query Builder button. To speed the process, enter the following SQL statement in the “Query string” text box:

SELECT CustomerID, CompanyName, ContactName, City, Country

FROM Customers

Click Next and in the next screen of the wizard you’ll be prompted to specify the Report Type (the report’s structure), which can be either tabular or matrix. Most reports are tabular, but you’re welcome to experiment with the matrix layout. If the report contained totals a small number of countries, for example, you would select the matrix layout to display all countries as columns.

Click Next and you will see the Design the Table screen of the wizard, where you must specify the placement of the fields of the report. The Page section contains the field that will be displayed on top of the page; every time this field changes value, a new report page will start. To display each country on a separate page, place the Country field into the Page box (just select the Country field and click the Page button). The Group section contains the grouping fields. To group customers per city, add the City field to the Group box. All other fields go the Details section. When you’re done, the Design the Table screen should look like the one shown in Figure X (initially, all fields will be in the Available fields box).

Figure 2: Designing the report table with the Report Wizard

Click Next again and this time you’ll be prompted to enter the layout of the report (whether it will be Stepped or Blocked), whether it will include subtotals and whether it will allow users to drill down into the details. Check the Enable drilldown box, disable totals and leave the other options as they are. Usually, a report with groups includes subtotals at the end of each category, but in our case there are no numeric fields to be totaled (we’ll create a report with subtotals shortly).

On the following screen you’ll be prompted to select a schema for the report’s appearance (what you will see later in the figures with the finished report is the Slate schema) and click Next again. On the Choose the Deployment Location screen, shown next, you must specify the report server and the folder to which the report will be deployed. Leave the default options and click Next to see a summary of the report and specify its name. Set the report’s name to NWCustomers and click Finish.

The wizard will display the new created report in the Designer, shown in Figure 3. The Designer’s surface has two panes: Design and Preview. Just switch to the Preview pane to preview your report. On the Designer view you can adjust the appearance of the report, set each column’s widths, colors, and so on. You probably don’t want to show the CustomerID columns, because users are not interested in IDs. Select the CustomerID column and set its Visibility property to false. Note that we do need the ID value for our report (this is how we’ll select the customer’s orders to display them in a nested report), we just don’t want to display it.

Figure 3: Viewing the NWCustomers report in design mode in the BI Intelligence Development Studio

Figure 4: Previewing the report of Figure 3

The initial report won’t look as good as the one shown in the figure. You’ll have to adjust the widths of the columns, hide the column with the IDs, and most like change the column headers. To change the appearance of a column, just select it with the mouse and look up its properties in the Properties window. Note that you can select the properties of a report column by clicking the gray handle of the column, the column header by clicking the colored cell with the column’s title, or the detail field displayed in a column by clicking the cell with the field name.

A table with customer names, even grouped by country and city, is neither impressive, nor practical. How about adding another report that displays the orders of a customer and link the new report to the corresponding customer of the first report? Right-click the Reports folder in the Solution Explorer and select Add New Report. The report wizard will start again. Use the shared data source you created earlier and specify the following SQL statement to select a specific customer’s orders:

SELECT Orders.OrderID, Orders.OrderDate,

Employees.LastName + ' ' + Employees.FirstName

AS EmployeeName,

SUM(([Order Details].Quantity *

[Order Details].UnitPrice) *

(1 - [Order Details].Discount)) AS OrderTotal

FROM Orders INNER JOIN

Employees ON Orders.EmployeeID = Employees.EmployeeID

INNER JOIN [Order Details]

ON Orders.OrderID = [Order Details].OrderID

WHERE (Orders.CustomerID = @CustID)

GROUP BY Orders.OrderID, Orders.OrderDate,

Employees.LastName + ' ' + Employees.FirstName

The remaining steps are the same as before and we won’t repeat them here. When you specify the design of the table, insert the EmployeeName field in the Group box, so that orders by the same employee are grouped together. You must also include the totals. Moreover, make sure that the date column on the report is formatted with the format string “MM/dd/yyyy” (by default, the date includes the time) and the numeric column is formatted with the format string “#,###.00”.

Finally, design a third report with the details of an order, using the following SQL statement:

SELECT [Order Details].ProductID, Products.ProductName,

[Order Details].UnitPrice, [Order Details].Quantity,

[Order Details].Discount,

([Order Details].UnitPrice * [Order Details].Quantity) *

(1 - [Order Details].Discount) AS LineTotal

FROM [Order Details] INNER JOIN Products

ON [Order Details].ProductID = Products.ProductID

WHERE ([Order Details].OrderID = @OrderID)

GROUP BY [Order Details].ProductID, Products.ProductName,

[Order Details].UnitPrice, [Order Details].Quantity,

[Order Details].Discount

When you preview the parameterized reports in the Designer, you will see that SSRS prompts you to enter the corresponding parameter’s value. Enter a customer ID like ALFKI, or an existing order’s number, and then press the View button to view the report. You can change the parameter prompt, its data type and even specify a source for its value. For example, you can display all customer names in a combo box and when the user selects a customer by name, retrieve the customer’s ID and pass it as parameter to the child report. To see how you can adjust the report parameters, open the Parameters section in the Report Data window, right-click on a parameters name and select Parameter Properties to see the Report Parameter Properties dialog box. To add a new parameter to an existing report, right-click the Parameters item in the Report Data window and select Add Report Parameter.

Linking the Reports

So far we created three reports and added a parameter to two of them. You can preview each report independently by specifying a value for its parameter (except for the first one, of course). To link the reports together, we must pass the proper parameter value from the parent to the child report. In other words, when the user clicks the customer name in the Customers report, we must pass the ID of the selected customer to the CustomerOrders report. Likewise, when the user clicks the ID of an order in the CustomerOrders report, we must call the OrderDetails report passing as parameter value the ID of the selected order.

To link the customers report to the orders report, go to the Customers report and select the CompanyName field. Switch to the Properties window, locate the Action property and click the button with the ellipsis next to the property name. This action will bring up the TextBox Properties dialog box, where you can specify an action for the Click operation, as shown in Figure 5. Check the “Go to report” button, select the destination report, which is the CustomerOrders report, and click the Add button to add a parameter that will be passed to the target report. As soon as you click the Add button, a new parameter will be added to the lower pane of the dialog box. The target report’s parameters will be displayed in a combo box and since there’s only one parameter, the CustID parameter, expand the list and select it. If you expand the list of values now, you will see the names of all the fields in the parent report. Select the CustomerID field. Every time the user click a company name, the CustomerOrders report will be called with the ID of the selected customer as its report parameter. The end result will be a report with the selected customer’s orders. 

Figure 5: Linking two reports by calling the child report when an item in the parent report is clicked.

Deploying the Project

So far you’ve been previewing the report in the Designer. You can also press F5 to run the report as a standalone application. It is possible to integrate your SSRS reports in a Visual Studio solution, like a VB or C# data-driven application, as long as you have installed a full version of Visual Studio. The Business Intelligence Development Studio won’t allow you to build applications of any other type, short of BI applications and deploy them on the local Web server. For the purposes of this tutorial we’ll deploy our three reports to the Report Server. To deploy a SSRS project, right-click the project’s name in the Solution Explorer window (not the solution’s name) and from the context menu select Deploy. Before you deploy the project, however, make sure you’ve got the deployment settings correct. Open the Project menu and select the last command, the NWCustomersOrders Properties command to see the following dialog box.

Figure 6: Setting the URL of the Report Server, where the report will be deployed

Here you must set the URL of the report server (property TargetServerURL) and the folder where the project will be deployed. If you have a folder with similar reports, you can specify an existing folder’s name. Notice that the BI Development Studio can’t access the directly the server’s folder structure (the Report Server could be a remote machine and it’s accessed through a Web service, not directly) and, as a result, you can’t select the folder on the Open dialog box; instead, you have to type the folder’s name. Once you’ve verified the deployment settings, you can deploy the project. It will take a few seconds to deploy the project and you can now open the reports through your browser. To view the report in your browser, start Internet Explorer and direct it to the following URL:

http://localhost/ReportServer/NWCustomersOrders

As soon as you open this page, you will see a list of all reports in the specified virtual folder. Click the name of the top report, the Customers report, to view the customers and drill down to a customer’s orders and then to an order’s details. If you open one of the child report, you must specify the appropriate parameter’s value and then click the View button to see the report.

Administering a Report Server with Report Manager

In addition to viewing reports deployed at the Report Server, you can also administer them. Open the Report Manager by pointing your browser to the following URL: http://localhost/Reports and you will see a page like the following. The Report Manager is a Web based application for managing the report server. This application allows you to view reports, set their properties (such as default parameter values, scheduled execution, and so on), as well as create subscriptions and manipulate users and roles. Explaining the features of Report Manager would take a much longer tutorial, but we should give you an idea of the features and capabilities of Report Manager.

Figure 7: Managing your Report Server with the Report Manager

The Data Sources folder is where the shared data sources are stored. The Models folder stores data models, which are used by the Report Builder to create reports. The Report Builder is meant for end users, who aren't familiar with the structure of the database and shouldn't be given direct access to the database. As developer you can build data models (DataSets that encapsulate the complexity of the database and expose aggregates as columns), on which end users will base their reports. Finally, there's one folder per report. Some of the report folders may contain multiple reports, or a folder structure with reports.

Click the Edit button in front of a report's name to see the settings of a report, shown in Figure 8. The Properties page contains six tabs: General, Parameters, Data Sources, Execution, History and Security. The figure shows the Execution tab and as you can see a report can fetch its data from the database the moment it's a executed or from a cache which is refreshed after an interval you specify on this tab. On the history tab you can set the properties of the history of the reports (how many snapshots of a report will be stored in the database and how often). Finally, on the Security tab you can assign roles to each user and tasks to each role. The security model of the Report Server is based on roles and each role may or may not perform certain tasks, such as viewing and managing reports, data sources and folders, creating subscriptions and so on. There are five predefined roles: Browser, Content Manager, My Reports, Publisher and Report Builder. If you click the name of a role, you will see a long list of tasks and you can add/remove tasks to a role at will. You must first understand the tasks and then determine whether a role should be allowed to perform a task or not.

Figure 8: The Properties page of a report

Figure 9: Managing roles in the Edit Role Assignment page

Figure 10: Managing a role's tasks in the Edit Role page

On the toolbar-like zone of the Home page of the Report Manager, there's a link called Report Builder, which will start the Report Builder application. This application is a simplified report generation tool for end users, based on the data model you create and post at the Report Server. The odd thing about Report Builder is that it's a Windows based application deployed as a Click Once application. You don't have to install it yourself; if you click the Report Builder button, the application will be installed on your computer (a process that will take a few seconds) and after that it will be available like any other Windows application. Version 2.0 of Report Builder is available as a standalone application from the URL given earlier in this article.

To summarize

SQL Server Reporting Services is a server based system for creating, deploying and administering reports. The Business Intelligence Development Studio allows you to create reports and deploy them to a Report Server, either on the local machine, or a remote server. Each report's definition is stored in an RDL file, which is an XML file. If you examine the schema of such a file, you'll realize that it's straightforward to build report definition files from within your code. As far as the report generation process, in this tutorial we simply scratched the surface, but you saw that it's fairly easy to build reports, including grouping and aggregates, and link reports together to create a drill-down hierarchy. Reports are based on DataSets, which are populated with SQL statements. The data that populates the DataSets comes from a database, which is defined as a Data Source and you can share the same Data Source among multiple reports. This way, if the database is moved to a different server, you only need to change a single Data Source for the entire project. You may also choose to incorporate the reports in standard Windows application and not deploy them to a Report Server.

Once a report has been deployed to the Report Server, users can access it through their browser. A site with many reports needs to be administered, especially if you want to distribute the reports to groups of users with different needs and privileges. The tool for administering the contents of Report Server is the Report Manager, which is a Web-based application. Besides reports, this tool allows you to administer roles and assign roles to users.

Download Article Source Code / Sample Reports below (must be registered to download, registration is free!)