Building and deploying basic reports

If ever there was job for "real" developers to shuffle to their juniors, it is building reports. It's not real development anyway; it's more like glorified formatting. Besides, the esoteric tools that you use to build reports try to provide an abstraction layer, which often prevents you from having any real control over the report. I've heard all these arguments before. If this sounds like a conversation you've had by the water cooler, then I invite you to take a closer look at SQL Server 2005 Reporting Services (SSRS).

SSRS 2005 is the latest version of SQL Reporting Services, and ships with all versions of SQL Server 2005. SSRS allows you to quickly and easily create reports from multiple database sources. The finished reports can be presented directly from the reporting services website, or they can be displayed in your web- or Windows-based applications. Reports can be exported to multiple formats, including comma delimited text, XML, portable document format (pdf) and Microsoft Excel.

This article is the first of four parts, which will provide in-depth coverage of SQL Server 2005 Reporting Services:

·  Part 1covers the basics of the SSRS. It demonstrates how to build quick, simple reports and will familiarize you with the basic Report Designer environment

·  Part 2will cover use of custom functions, aggregate functions, sub-reporting, the matrix control, drill-downs, and sorting.

·  Part 3will take an in-depth look at the charting control

·  Part 4digs into the actual Report Definition Language (RDL) and the new Report Builder tool.

Installing and configuring Reporting Services

After much debate, we decided that full installation and configuration details were outside the scope of an article focused on how to build basic reports using SSRS, especially as there are already several online resources that treat this area in detail. See, for example:

http://msdn2.microsoft.com/en-us/library/ms143736.aspx

and

http://www.awprofessional.com/articles/article.asp?p=357694&seqNum=1&rl=1

Well worth reviewing are the details in the latter on the need to install SSL on any machine hosting SSRS.

---Editor's installation notes---
In order to test the reports in this article, I went through a single-machine installation on Windows XP (meaning that SQL Server 2005, Reporting Services, and much more, are all installed on my poor laptop). Since I didn't install SSRS when I installed SQL Server 2005 (I didn't have IIS installed at the time), I had to first install IIS 5.1, and then Reporting Services (from the SS2005 disks). I then jumped immediately into Reporting Services Configuration Manager tool (All Programs ¬ SQL Server 2005 ¬ Configuration tools). The left panel acts as a sort of checklist of items to configure:

All went well until theDatabase Setup. Because I had not installed SSRS at the same time that I installed SQL Server, theReportServerandReportServerTempDBwere not created by default. These databases hold all the report definitions and reporting server configuration information. Connecting to SQL Server usingService Credentials, the databases appeared to be created, but I received an error suggesting that I didn't have permissions to perform the requested task, and a red cross remained next to theDatabase Setupicon. This had me stumped for a while – eventually I appeared to solve it by hopping to theServer Statussection, stopping the Report Server, opening SSMS and deleting both theReportServerandReportServerTempDBdatabases, and then restarting the Report Server and trying again! Even though I swear this didn't work the first time I tried it, it seemed to do the trick the second time (while I was on the phone moaning to Steve that I couldn't get it to work!). However, I'm still not clear why I had the problem in the first place – or why it suddenly went away.

This wasn't quite the end of my problems. Installing SSRS creates two new virtual directories under the default website on IIS. By default, they are namedReportsandReportServer. TheReportServersite hosts a web service for running and managing reports. TheReportssite allows you upload reports from a browser and run reports over the web. When I tried to navigate tohttp://localhost/ReportServerI received the following error: "Failed to access IIS metabase". Fortunately, the information on the error page is quite useful (for a change) and this, coupled with a quick Google search, led me to grant access to the metabase to the ASPNET user account by executing the following from the directory housing theaspnet_regiisexecutable (WINDOWS\Microsoft.NET\Framework\v2.0.50727, in my case):

spnet_regiis -ga ASPNET

This did the trick, and I was up and running. I received some raised eyebrows here when I proposed to install SSRS. It made my long-suffering sys admin team particularly nervous. The 2000 version of SSRS seems to have a very bad reputation among the Red Gate team, both as a performance nightmare, and in one case for "completely annihilating" a developer's machine. However, the above slight quirks aside, the SSRS 2005 experience seems pretty smooth and I've had no real performance, or other, issues. So far.
-----End of Editor's note----

Once you've completed the installation and configuration process, the first thing to do is download the code file for this article (see the Code Download link in the speech bubble to the right of the article title). The code file contains a sample Visual Studio project and a SQL Script for creating theReportDemodatabase. You will need this database if you want to follow the examples in this series of articles so go ahead and create the database using theReportingDemoDatabaseScript.sqlscript or, alternatively, by restoring the provided ReportingDemo.bak file.

If you are using SQL Server Authentication on your SQL Server you will also need to set up a login with permissions to theReportDemodatabase (for the examples, I set up a login calledDemoUseras a DBO on my ReportDemo database).

Using the Report Wizard

There are two ways to create SSRS Reports. You can build the report manually, or you can use the Report Wizard to give yourself a head start. For this first report, we are going to take advantage of the wizard. Start a new Business Intelligence project in Visual Studio 2005. Then, from the menu, selectProject > Add New Item. Select theReport Wizardoption and call the file "FirstReportWZ.rdl". ClickAdd. ClickNexton the Welcome screen and this will bring you to theSelect the Data Sourcescreen:

Select theNew data sourceradio button and give the data source a name. SelectMicrosoft SQL Serveras the type.

Note:
Use of theShared data sourceoption is actually a better option in most cases but, for the sake of this example, we will just create a new data source. We're going to set up a shared data source later in this article.

Click theEditbutton to bring up the Connection Properties dialog:

Enter or select a server name. The default is to log on usingWindows Authentication. If you are usingSQL Server Authentication, choose that setting, and enter the username and password. Finally, select a database, in this caseReportingDemo. Make sure you test the connection before you clickOK.

Next up is the Design the Query screen. You can use the Query Builder by clicking the button at the top left, but in this case simply enter "Select * From Customer" into theQuery stringtext box and click Next. On theSelect the Report Typesimply select the type you prefer (tabular or matrix) and hitNext.

This brings up theDesign the Tablescreen:

SelectStatein theAvailable fieldsbox and click theGroupbutton. This will group the data in the result set byState. SelectNext. On theChoose the Table Layoutpage, select theSteppedoption and check theEnable Drilldowncheckbox. On the next screen, choose a style for your report (I choseOcean) and clickNext.

This brings up the final screen,Completing the Wizard:

It shows a summary of your report options, and gives you an opportunity to rename the report and to preview it. ClickFinishto end the wizard.

You should be shown the report in the standardReport Designer:

There are three tabs:Data,LayoutandPreview. You should be on theLayouttab. Click on thePreviewtab to view the report. Since this report doesn't take any arguments, it will run as soon as you click on the tab.

Use the+/-symbols next to theStateabbreviations to expand and collapse the grouping of the report.

Manual report creation

The previous section, while perhaps overly simplistic, does show you how to get a report up and running with zero code and zero property setting. Obviously, such reports will not meet most development requirements, but do not discount this option as a starting point for more complicated reports. Once you've created a report with theReport Wizard, you are free to make any modifications to it.

In this section, we are going to create a more realistic report from scratch, including how to create and use a shared data source object, stored procedures, and how to format your report, set report properties and use report parameters.

Creating a shared data source

Before we start the report, we are going to build a shared data source i.e. a data source that is common to, and can be used by, all of the reports on a reporting server. While each report can contain its own connection information, it is good practice to use shared data sources, as it will save you a lot time and headaches. This way, you only have to set the connection information once. For example, let's say your company has Development, Test and Production environments. If you put the connection information into each report, you will have to change it each time when you publish to Development, Test and Production. If you use a shared data source, you still have to set up a data source object for each of three environments, but you can simply publish the report to each environment, and they will automatically use the connection information associated with that environment.

From the menu selectProject > Add New Item > Select Data Source. On theGeneraltab, call the data source "ReportsDB". Leave the Type asMicrosoft SQL Server. Click theEditbutton on the right, and enter the connection information for Reporting Demo, as before. ClickOKon theShared Data Sourcescreen and the data source is done.

Add a new report

From the menu selectProject > Add New Item. SelectReportand name it "FirstReportMan.rdl". A new report will be added to the project, and theReport Designerwill open at theDatatab. At this point, let's take a closer look at theReport Designertool. At the top of the Report Designer window are three tabs:Data,LayoutandPreview. TheDatatab is used to build data sources for your report. TheLayouttab is the physical report designer where you set up the header, the footer and the data presentation of the report. ThePreviewtab allows you to actually run the report from Visual Studio 2005, without having to publish it to a report server first. If your report takes parameters, thePreviewtab will ask you to fill them out before it runs the report.

The first thing we need to do is get data into our report. At the top of theDatatab choose<New Dataset…>from the dropdown list:

TheDatasetdialog opens:

Name the dataset "ReportData", and select theReportsDBdata source from the dropdown. SelectStoredProcedureas the command type, and enter "spr_CustomerSelectAll" in the query string box. ClickOKto close the dialog.

Note:

spr_CustomerSelectAll is a very simple, parameter-less stored procedure that selects all of the columns from the Customer table. The source code is included in the ReportingDemoDatabaseScript.sql script with the code download.

Test the dataset by clicking on the big red exclamation point at the top of the report designer. This executes the query and displays the results. You can repeat these steps if you wish to add multiple datasets to your report.

Setting up the report display

Next, switch to theLayouttab. This is where you actually build the report display. Start by dragging a table from the Toolbox, onto the Body section of the report. By default, the table shows a header row at the top, a detail row in the middle and a footer row at the bottom. You can add additional columns to the table by right-clicking on one of the columns and selecting one of the twoInsert Columnoptions.

Click on theDatasetstab under Toolbox on the left-hand side of the Visual Studio environment. You should see theReportDatadataset. Expand it, selectFirstNameand drag it to the first cell of the body row of the table. This will display the First Name field in the first column. Now put theLastNamein the second column, and theCustomerStatusin the third:

Note that, when you drag the columnFirstNameinto the body row of the table, SSRS make a guess as to what to call the row. It calls it "First Name". You can click on thePreviewtab to view the report.

Adding formatting

OK, so we have a basic, very plain, report. Let's add some formatting to make it look good. Let's add theDateOfBirthcolumn to the table. You can expand and shrink the size of the columns by highlighting and dragging:

If you wish to get rid of the footer row of the table and then click on any field in the table. You will see a grey box surround the table. Right-click on the icon at the beginning of the body row and then click theTable Footeroption to deselect it.

The table header is next. To format all the cells in the header in the same way, click and drag over all the cells to select them. Open thePropertieswindow and set background color, font, and so on.

Note, however, that if you wish to change the justification of the text or the size, then you do so using the main menu at the top, not via the properties window. Again, you can click on thePreviewtab to view the report.

Add a report header

The table already has a header, but the report can also have a header and footer. Report headers and footers appear on every page of the report. Put theReport DesignerinLayoutmode and selectReport > Page Headerfrom the menu. A new band labeledPage Headerappears on the report above the body. Drag a text box onto the header and click directly on it. Type "My First Report", not in thePropertieswindow, but right on the control.