Data Management

Get more useful information by deploying SQL Server 2005 Reporting Services

Microsoft SQL Server 2005 includes the latest version of the popular Reporting Services module. This document shows you how to limit users' rights, create connections to databases, and produce simple reports.

In the release of SQL Server 2005, Microsoft has included the popular Reporting Services add-on right into the product and made it an option during installation. Reporting Services already enjoyed wide support due to its cost (free with a SQL Server 2000 license) and good feature set.

In this document, I will not go over the installation of this tool (see my previous download in this series for details), but will provide you with information on managing and using this tool. By the end of this article, you will be able to limit users' rights, create connections to databases, and produce simple reports.

For this document, I'm assuming that you have already installed Reporting Services and have it working. In the examples, I will be using the sample AdventureWorks database included with SQL Server 2005 and Visual Studio 2005.

This series on SQL Server 2005 includes:

Initial installation defaults

After the initial installation of Reporting Services, the only useful access allowed to the tool is provided to the members of the Administrators group. Other domain users can authenticate to the tool, but they are greeted with a blank window with the Reporting Services header and can't do anything until you provide them with something to do. (Figure A)

Figure A

The only users that can access the tool are members of the Administrators group.

There are actually two different IIS sites created for Reporting Services. You saw the /reports site in Figure A, which is the front-end that will be seen by your users. The other site, available at /reportserver, provides you with a way to execute reports without using the Web-based GUI provided by Reporting Services. By passing parameters to Reporting Services through the /reportserver link, you can execute reports automatically. With no parameters, you get a screen similar to the one shown in Figure B. Note: I will not be running reports using /reportserver in this article.

Figure B

The /reportserver site provides you with an alternate way to run reports.

Creating a simple report using Visual Studio's wizard

Let's jump right into creating your first report with Reporting Services. Later on, I'll show you how to allow and deny access to this report for specific users and groups.

There are two ways to create reports for use in Reporting Services. First, you can create reports using Visual Studio .NET 2003 with the Reporting Services add-in or Visual Studio 2005. I am using Visual Studio 2005. Or, you can use the Reporting Services tool's built-in report builder. I'm going to demonstrate the Visual Studio approach in this article.

To begin a new reporting project in Visual Studio, go to File | New | Project. The New Project dialog box opens up. In this window, select either Report Server Project Wizard or Report Server Project. Next, give your project some kind of descriptive name and click OK. (Figure C)

Figure C

I'm going to use a wizard for this first project.

Using the wizard, there are five steps that need to be completed to create your report:

  1. Select a data source for the data. In this case, I'll use the sample AdventureWorks database.
  2. Create a query to grab the data on which you want to report.
  3. Choose your report type.
  4. Choose your report layout.
  5. Choose your report formatting.

Select a data source

The first screen of the wizard, shown in Figure D, might look a little intimidating at first, particularly if you're not that familiar with creating database connections. However, the wizard provides you with a handy Edit button that gives you a really easy way to build this connection string. Take a look at Figures D, E & F to see what I mean.

Figure D

This data source selection window by itself requires that you type in the connection string to a database.

Figure E

Clicking the Edit button brings up this window that lets you pick and choose your SQL server, authentication method and database name.

Figure F

Once you click OK in the Edit window, the Connection String box is automatically populated for you.

Looking at Figure F, you can see that, after doing this once or twice, you won't have much difficulty manually providing the connection string, if necessary.

Click Next to move to the next screen in the wizard.

Create the query

In order to get anything into your report, you need to pull information from the database. That's where a query comes in. Like the previous step, this step gives you a big blank box in which to manually type a query. (Figure G)

Figure G

You can manually enter your query in the Query string box on this step of the wizard.

However, you might not know the complete table structure of your database. So, like the data source entry, Visual Studio gives you a GUI from which you can pick and choose what you would like to have on your report. In Figure G, click the Query Builder button to bring up such a window. If you do this and get a blank window with no directions, click the "Generic Query Designer" button in the upper left corner of the Query Builder window (Figure H).

Figure H

The Query Builder screen.

There's a lot to figure out even on the Query Builder window, but it does make the job of building a query much simpler than entering one manually. At first glance, you might wonder where you actually get database fields. When you first open the window, Visual Studio gives you a blank upper window. If you look at the toolbar, you'll notice that the right-most button is a table with a plus sign on it. Click this button to add tables from the database that you specified in the previous step. See Figure I.

Figure I

From this list, you can choose any table from which you want to pull fields.

Once you select tables, Visual Studio will indicate relationships between the table and start building your query as you can see in Figure J.

Figure J

The query starts to show up in the bottom part of the window.

Once you've selected your tables, select the fields you want to include on your report by placing checkmarks in the boxes next to the field name. (Figure K) As you add fields, Visual Studio will build the query and create the appropriate table joins based on the relationships that are defined in the database structure. Once you've selected your fields, you can test your query by clicking the Run button (the exclamation point) on the menu bar. The sample output shows up immediately below the query window. Once you've finalized your query, click the OK button.

Figure K

Pick and choose the fields you'd like to include in your report.

After you click OK on the Query Builder window, you're returned to the Design the Query step of the wizard and the Query string box is now populated. Click Next to continue. (Figure L)

Figure L

The Query string window is now populated and ready for use.

Select a report type

Visual Studio provides you with the ability to create two different kinds of reports: tables or matrix. (Figure M) With a table, you usually have column headings and the rows are completely made up of data elements. With a matrix format, you have headings above each column as well as at the beginning of each row. A tabular report is good when you're listing information, such as an employee listing (which is the example you'll see here). A matrix format would be good for something like a budget report where you want the column headings to be the month and the row headings to be the budget line, for example. Click Next to continue.

Figure M

Choose the type of report you want to create.

Report design, layout & style

After you choose the type of report you want, you need to choose the overall design. Basically, how do you want the data separated in your report? You have three choices for this: Page, group, and details. The page level information means that, when the fields that you select change, the report will go to a new page. For example, if you have ten manufacturing plants and you're running an inventory report, you could have the "Plant name" field in the page section so that inventory for each plant starts on a new page. Group is similar, but does not start a new page. When the group field changes, a new group just starts immediately below the previous group. Finally, you need at least some detailsâ€"the information that will be placed on each page or group. For this example, I've decided that I want my employee report grouped by city. (Figure N)

Figure N

Select your reports groupings.

With the design selection out of the way, now you can choose how you want the table laid out. Do you want it "stepped" so that grouping information goes to the next line, or do you want it in "block" form, which makes your report look more like a spreadsheet? I'm using the stepped option here. (Figure O)

On this screen, you can also choose to include subtotals for specific columns, which is useful for budget reports, for example. You can also choose to "Enable drilldown", which will allow the person viewing the report to drill down into the source data to determine where it came from.

Figure O

Select the layout method you'd like to use.

Finally, the last layout step asks you to select the table style you'd like to use. This one is easy. Just pick a design from the list. (Figure P) If you want to change it, you can do so later on by opening the project from within Visual Studio and making adjustments.

Figure P

Choose your table's style.

Deploy the report

Since Visual Studio can be run from anywhere, you need to somehow get the new report onto your report server. On the next screen of the wizard, you're asked to provide the report server name and the name of the folder into which you'd like to deploy the new report. In this example, I'm running Visual Studio right on the SQL Server. (Figure Q)

Figure Q

Provide the deployment server and folder for your new report.

Finally, you're provided with a summary screen that also asks you to name the report. You also have the option to preview the report if you like. Click Finish to deploy the report to your report server. (Figure R)

Figure R

Name your report and click Finish to complete the wizard.

Using the report

Starting up a connection to your report server now shows you some new entries on the opening page. (Figure S) First, you'll see a Data Sources option. Second, you should see a folder with the same name as the project you created in Visual Studio.

Figure S

These two new folders house the report definition and the connection string required for the report to run.

To run the report, open the folder containing the report you just created and click on the report name. The report opens up in the Web browser. Click the plus signs next to each city to expand that selection and see the data inside. You can also export the report information to other formats, including Excel, by clicking the "Select a format" drop-down option and choosing the format to which you'd like to export the information. (Figure T)

Figure T

The report is quickly viewed in a Web browser window.

Report access

With the report created and viewable, you can now limit who can use this report. Click the Properties tab right above the report. From the properties window, choose the Security option. (Figure U)

Figure U

The security option provides you with a place to secure access to this report.

On this screen, click the Edit Item Security button. You will get a message indicating that the current permissions are inherited from the parent, and you can choose to override this inheritance. Once you do this, you will get two new buttons: "New Role Assignment" and "Revert to Parent Security". Click the New Role Assignment button.

On the resulting screen, aptly entitled "New Role Assignment", in the box at the top of the window, type in the name of the group or specific user that will have access to this report. (Figure V) Next, select the role to which this user should be assigned for this report and click the OK button. (Figure W)

Figure V

The user 'slowe' will be assigned rights to browse reports.

Figure W

The Properties page for the report changes to reflect the new security assignment.

When the user next logs in, he will see the new report as an option.

This is a very basic look at report access in Reporting Services, and is only one way to set up a security structure for the service.

Many ways

This was a cursory introduction to getting started with the Reporting Services component now included in SQL Server 2005. You saw how to create a basic report and how to apply security to that newly created report. In reality, once you delve into the whole product, you will find that there are many different ways to accomplish each task.

Editor's Picks

Free Newsletters, In your Inbox