Basic Server Reporting Functionality in SQL Server 2005 Express Edition

In the recent installments of our series dedicated to the most prominent
features of SQL Server 2005 Express Edition, we have started an overview of its
reporting capabilities. So far, we have described its basic architecture and
characteristics, as well as guided you through its setup procedure and post
installation modifications leveraging the Reporting Services Configuration
Manager utility. We will now focus on elementary methods you can employ to
generate custom reports (we will be discussing more advanced techniques in
upcoming articles).

We have concluded our previous
at the point where Reporting Server became operational, with
all of its essential components present and configured with their default
settings. To verify that the outcome has been successful, launch Internet
Explorer on the target computer and point to the Report Manager Virtual
Directory as the target URL (this is, by default, http://localhost/Reports$instance_name, where instance_name designates the SQL
Server 2005 Express Edition instance hosting the Report Server databases),
while logged on with an account that is a member of its Sysadmin role. Such
action should automatically redirect you to http://localhost/Reports$instance_name/Pages/Folder.aspx
page, serving as the SQL Server Reporting Services Home page, from which you
can manage site settings (including hierarchy, security, and report execution
policies), designate data sources, as well as upload files (report definitions
or resources). You might also want to launch Local Users and Groups console (by
invoking LUSRMGR.MSC in
elevated Admin mode) and ensure that SQLServer2005ReportServerUser$ComputerName$InstanceName
and SQLServer2005ReportingServicesWebServiceUser$ComputerName$InstanceName
local groups exist and contain, respectively, the ReportServer Windows and Web
Services accounts you have identified earlier.

In order to create reports based on SQL Server 2005 Express Edition
Reporting Services functionality, we have to rely on features provided by
Business Intelligence Development Studio. Its source files are included in the
SQL Server 2005 Express Edition Toolkit, available from the Microsoft Download
Center in the form of a self-extracting executable SQLEXPR_TOOLKIT.EXE. Once you have downloaded
and launched it, you will be presented with the familiar Microsoft SQL Server
2005 Setup wizard, prompting you to accept the End User License Agreement,
checking hardware and software prerequisites, requesting registration
information, and, most importantly, giving you the opportunity to choose
desired features, including connectivity components, Software Development Kit,
Management Studio Express, and Business Intelligence Development Studio
(including Report Designer).

Note that this rather straightforward process could turn into a considerably
more complex undertaking in situations where you had previously installed instances
of Visual Studio 2005 (such as, Visual Studio 2005 Express Edition). In such
cases, you will likely encounter a problem with a misdirected or missing
shortcut to devenv.exe (the
primary executable of SQL Server Business Intelligence Development Studio)
after completion of a seemingly successful setup process. To resolve it, you
might have to remove the earlier instance of Visual Studio (and potentially,
SQL Server 2005 Express Edition client components and Reporting Services),
to a separate folder (using /X
parameter), and invoke vs_setup.msi
from the setup subfolder of
the directory containing the extracted source files (and, subsequently,
reinstall previously removed software). In addition, if you are using Windows
Vista, you should also install Visual
Studio 2005 Service Pack 1 Update
intended for this operating system, and
ensure that you operate in elevated administrative mode whenever you design,
build, and deploy reports (which, incidentally, also applies also to launching
other management tools, such as SQL Server Management Studio Express and
Reporting Services Configuration console).

Our first sample report will be based on the AdventureWorks database, which
in the case of SQL Server 2005 Express Edition needs to be separately
downloaded from the CodePlex
Web site (there a number of different versions, including those intended
specifically for lightweight OLTP samples running on both x32 and x64 bit
platforms). After you have downloaded the AdventureWorksDB.msi
Windows Installer package, launch its execution using an account with local
Administrator privileges. This will trigger AdventureWorksDB InstallShield
wizard, taking you through a short sequence of steps, including accepting
license agreement and specifying target folder. Once this process is completed,
attach the database (using Attach… entry from the context sensitive menu of
the Databases node of your SQL Server 2005 Express Edition instance) within SQL
Server Management Studio Express console.

After launching Business Intelligence Development Studio, you should be able
to find the two Reporting Services-specific items listed under the Visual
Studio Templates section of the New Project dialog box under the Business
Intelligence Projects node (once you choose either New Project entry from the
File top level menu or Create: Project… from the Recent Projects pane on the
Start Page of the console). The first one, labeled Report Server Project
Wizard, guides you through a report generation, assisting with each necessary
step, including data source selection (automatically assigning connection
string based on parameters you specify), designing a query against it (via
friendly Query Builder interface), as well as choosing a report type (tabular vs.
matrix), layout (stepped vs. block), style, and formatting. Throughout this
process, you will also be asked to provide the path to the Report Server (note
that in our case, you might need to alter it from its default http://localhost/Reports to http://localhost/Reports$instance_name)
and deployment folder (located within the Report Server virtual directory).
After clicking on the Finish command button on the final page of the wizard,
you will be presented with the layout of the newly created report in the main
window of Business Intelligence Development Studio. You can further modify your
design by taking advantage of functionality available from this interface and,
once your report is ready, select the Deploy option in the context sensitive
menu of your project in the Solution Explorer window. However, in order to
avoid any issues, you should first verify the accuracy of entries in the
Project Properties dialog box (in particular, ensure that the Target Server URL
entry is set to http://localhost/ReportServer$instance_name,
assuming the default naming convention). Providing that no errors are reported
in the Output window, a new folder (named the same as your project name) should
appear on the Report Manager Web page. By opening it, you will access another
page with a link to the newly deployed report, displaying data in the format
you selected. Users can access reports via an equivalent method (although they
will be pointing their browsers to http://Web_server_name/Reports$instance_name).

Let’s demonstrate this process by walking through each of its steps while
designing a sample report based on the content of AdventureWorks database. In
particular, we will generate a report listing address data for individual
customers (leveraging the query described in the Sales and
Marketing Scenario
article on the MSDN Web site) in alphabetical order
(based on last and first names) but grouped according to country/region and
state/province. To start, launch Business Intelligence Development Studio and
select the Report Project Wizard from the New Project window. After you
acknowledge the introductory (and purely informational) page, you will be
prompted to Select the Data Source.
Click on the Edit... command
button, in the Server name listbox
locate the SQL Server 2005 Express Edition instance (if you do not see it on
the list, ensure that SQL Server Browser service is running), accept the
default log on mechanism (based on Windows Authentication), and connect to the
AdventureWorks database by choosing its name in the listbox labeled Select or enter a database name. (You can
take advantage of Test Connection
feature to ensure that information you specified so far is correct). After
confirming your selection, proceed to the next page labeled Design the Query. While you can enter it
directly in the Query string
text box, we will use the Query Builder
for this purpose. By default, you will be presented at this point with the
Generic Query Designer. To switch to its graphical counterpart, click on the Query Designer icon located in the upper
left corner of the main window. From its interface, add the Person.Contact (with FirstName and LastName columns), Sales.Individual (with CustomerID column), Sales.CustomerAdddress, Person.Address (along with its AddressLine1 and City columns), Person.StateProvince (with Name column aliased as StateProvince), and Person.CountryRegion (with Name column aliased as CountryRegion) tables, sorting their
combination in ascending order based on LastName
and FirstName pair. Confirm
your choices and return to the Design the
page. Choose the Tabular
option when prompted for the report type. On the Design the Table page, choose CountryRegion as the Page label, StateProvince as the Group criteria, and
add all remaining fields (CustomerID,
FirstName, LastName, AddressLine1, and City)
to the Details section. Decide on the table layout (by picking either Stepped or Block entry) and a visually preferred table style.
Finally, in the Report server
listbox, type in the URL pointing to your Report Server (by default set to http://localhost/ReportServer$instance_name),
deployment folder (where your report will be stored), and the report name
(which will identify it on a Web page accessed by your clients once the
deployment is completed). To make the report available via Report Server Web
site, use the Deploy option
from the context-sensitive menu of your project (in the Solution Explorer
window) we have mentioned earlier.

You can produce the same outcome using the second project template available
from the New Project dialog box in Business Intelligence Development Studio
interface (called simply Report Server Project), although in this case, without
relying on rather inflexible rules imposed by the wizard. We will provide an
example demonstrating this approach in our next article of this series and fix
some visual deficiencies of the report we just created.


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles