• Article

Reporting Services in SQL Server Express with Advanced Services

Reporting Services in SQL Server Express with Advanced Services (SQL Server Express) lets you create, view, and manage reports based on data that is stored in the local instance of SQL Server Express. You can use this topic to learn how this edition of Reporting Services works, including how to create and deploy reports using the tools that are available in this edition. 

For more information about the features that are available in this edition of SQL Server, see Features Supported by Reporting Services in SQL Server Express. For more information about the features that are available in other editions of SQL Server, see Editions and Components of SQL Server 2008 R2.

Important

SQL Server Express includes SQL Server Management Studio Express. SQL Server Management Studio Express cannot be used to administer a report server. Use Report Manager and the Reporting Services Configuration tool instead. Use Report Manager and the Reporting Services Configuration Tool to administer the report server that is created when you install Reporting Services in SQL Server Express with Advanced Services. Although the installation package includes SQL Server Management Studio Express, you cannot use SQL Server Management Studio Express to administer the report server.

How Reporting Services Works with SQL Server Express

The following list describes how Reporting Services works in SQL Server Express:

  • All the server components of both SQL Server Express and Reporting Services are installed on a single server. You cannot store the report server database on a remote server.

  • All data sources that provide data to reports must connect to SQL Server relational databases are that are installed on the local instance of SQL Server.

  • All reports are processed on demand. Scheduled or unattended report processing is not supported.

  • SQL Server Express always installs a named instance of the SQL Server database engine. Therefore data source connection strings and Reporting Services URLs must include the instance name. The following examples illustrate the syntax you should use.

    Item

    Example syntax

    Connection string to the sample AdventureWorks2008R2 database hosted on a local SQL Server Express instance

    Data Source=localhost\SQLExpress; Initial Catalog=AdventureWorks

    URL to a report server and report server endpoint

    https://localhost/reportserver_SQLExpress

    URL to Report Manager

    https://localhost/reports_SQLExpress

Installing the Sample Databases

When you install SQL Server Express with Advanced Services, the AdventureWorks2008R2 sample databases are not installed automatically. To get the sample databases, you must download and install them from http://msftdbprodsamples.codeplex.com/. When you install the sample databases, make sure that the local instance of SQL Server Express is selected on the Database Selection page of the setup program.

Requirements

Reporting Services in SQL Server Express has all the same software requirements as other editions of Reporting Services. For more information, see Hardware and Software Requirements for Installing SQL Server 2008 R2.

How to Create Reports

The reports that you create in the Express edition of Business Intelligence Development Studio can use all the report definition features that you find in other editions of Reporting Services. For example, you can create drillthrough reports, subreports, and parameterized reports that include charts, tables, matrices, and lists.

You can reuse or copy report definitions that you created in other editions. Be aware that if you publish a report that you created in an earlier version of Reporting Services, the report will be upgraded to use the most recent SQL Server report definition format.

After you create a report definition, you can publish it to a report server. To make a published report available to users, you must use Report Manager to create role assignments that grant access to the report. For more information about creating, publishing, and securing reports, see Designing Reports in Report Designer and Report Builder 3.0 (SSRS), Publishing Reports to a Report Server, and Creating and Managing Role Assignments.

How to Deploy Reports and Use Report Server Functionality

To view published reports, you can use a browser or Report Manager, or create a custom application that uses a ReportViewer control to host reports.

Using Report Manager or a Browser

SQL Server Express includes Report Manager, a Web application that can be used by any user who wants to view published reports. Report Manager is accessed through a report server virtual directory that is configured on a local Web server. Users who have access to that directory can run Report Manager.

To view reports in Report Manager, you must define role assignments that allow users to view reports and navigate folders without granting access to server management features that a report server administrator might use. You can also use a Web browser to view a single published report.

Using ReportViewer controls

If you are a developer, you can use SQL Server Express with the ReportViewer controls, which are available through Microsoft Visual Studio. There are two controls:  one for Windows Forms applications and one for ASP.NET applications. The controls are freely distributable with your application. You can configure the controls to run remote server reports that run on SQL Server Express.

The ReportViewer controls support other processing modes and deployment models in addition to SQL Server Express. For more information, see "ReportViewer Controls (Visual Studio)" in the Visual Studio product documentation on MSDN.

Programmability in SQL Server Express

The Report Server Web service can be accessed programmatically. Although you can access all of the report server SOAP endpoints, you cannot use all the features. If you cannot use a method, Reporting Services returns an error. For more information, see Programming Features for Reporting Services Editions.