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.