Reporting Services in SQL Server 2005 Express Edition with Advanced Services

  • Article

New: 14 April 2006

Microsoft SQL Server 2005 Express Edition with Advanced Services (SQL Server Express) is a new version of SQL Server 2005 Express Edition that includes Reporting Services functionality. Reporting Services in SQL Server Express is different from other editions of Reporting Services in the following ways:

  • Reporting Services in SQL Server Express includes a subset of the features that are available in other editions of SQL Server 2005. You can use this topic to learn about the features that are part of the Express edition (features are based on the SQL Server 2005 Service Pack 1 version of Reporting Services).

  • Product documentation for this edition is included in the SQL Server 2005 Books Online Documentation Refresh 2. To get the documentation refresh, you must download it from a Microsoft Web site. After you download and install it, you can use the SQL Server Express filter in Books Online to hide the Reporting Services content that does not apply to this edition.

  • In contrast with other editions of SQL Server 2005, the AdventureWorks sample database is not installed or attached automatically. Setup does not install or attach the database for you. To get the database, you must download it from a Microsoft Web site. After you download and install it, you must attach it to the local SQL Server Express Database Engine instance. You can use SQL Server Management Express to attach the database. For more information, see Installing Sample Databases for Express Editions.

  • Data source connection strings and Reporting Services URLs have different default values in a SQL Server Express installation. SQL Server Express always installs as a named instance. Any URLs or connection strings must include the instance name. The following examples illustrate the syntax you should use.

    Item Example syntax

    Connection string to the sample AdventureWorks 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

    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.

Requirements

Reporting Services in SQL Server Express has all of the same software requirements as other editions of Reporting Services. Because the report server runs as an ASP.NET worker process in IIS, you must have a local instance of IIS 5.0 or later with ASP.NET 2.0 enabled on your computer. In addition, the Reporting Services Configuration tool requires Windows Management Instrumentation (WMI). If you disabled WMI, you cannot use the configuration tool. For more information about product requirements, see Hardware and Software Requirements (SQL Server Express).

Reporting Features in SQL Server Express

SQL Server Express provides the following Reporting Services functionality:

  • On-demand report processing for each user who views a report. When a user opens a report, the report is initialized, the query is processed, data is merged into the report layout, and the report is rendered into a presentation format.
  • Rendering formats are available for HTML, Acrobat, and Excel.
  • Report data sources must be SQL Server relational databases that run locally in SQL Server Express.
  • Report server management and report viewing are supported through Report Manager.
  • Configuration is supported through the Reporting Services Configuration tool.
  • Rs.exe, rsconfig.exe, and rskeymgmt.exe command line utilities are available in SQL Server Express.
  • Windows authentication and predefined roles are used to map existing group and user accounts to a named collection of operations.

Unsupported Features

Other editions of SQL Server 2005 include a larger set of Reporting Services features. The following list describes the features that are documented in SQL Server Books Online, but cannot be used in this edition:

  • Scheduled report processing, caching, snapshots, subscriptions, and delivery are not supported.
  • Analysis Services, Oracle, XML, SAP, SQL Server Integration Services (SSIS), OLE DB, and ODBC data sources are not supported.
  • Remote data sources are not supported. Reports that are hosted in a SQL Server Express report server must retrieve SQL Server relational data from a local SQL Server Express Database Engine instance.
  • Ad hoc reporting through semantic models and Report Builder is not supported.
  • TIFF (Image), XML, and CSV rendering extensions are not supported.
  • The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.
  • Custom authentication extensions and custom role assignments are not supported. You must map existing Windows domain user and group accounts to predefined role definitions.
  • Custom report items are not supported.
  • Managing a long-running report process is not supported. Specifically, you cannot use the Manage Jobs feature in Report Manager to stop report processing.
  • Scale-out deployment is not supported.

If you are using the User Instances feature (also known as "Run As Normal User" or RANU) in SQL Server Express, remember that the data source will not be accessible over remote connections. This means that if you configure a report data source for the User Instances feature, remote users who access the report will get the following error:

"An error has occurred during report processing. Cannot create a connection to data source '<datasourcename>'. For more information about this error navigate to the report server on the local server machine, or enable remote errors."

In addition, the report server log file will contain the following entry:

Cannot create a connection to data source '<datasourcename>'. ---> System.Data.SqlClient.SqlException: User does not have permission to perform this action.

To avoid these errors, do not set the User Instance property in the connection string or in the Advanced Properties dialog box when defining the connection.

How to Create Reports

To create reports, you must install the SQL Server Express Toolkit. It includes the version of Business Intelligence Development Studio that is used with the SQL Server Express edition. The SQL Server Express Toolkit is installed separately from other SQL Server Express components. For more information, search the Microsoft Web site for installation instructions for SQL Server Express with Advanced Services.

The reports that you create in the Express edition of Business Intelligence Development Studio can use all of 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 2005 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 and Creating Reports, Publishing Reports to a Production Environment, and Creating, Modifying, and Deleting 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 developer, you can use SQL Server Express with the ReportViewer controls, which are available through Microsoft Visual Studio 2005. 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 of the features. If you cannot use a method, Reporting Services returns an error. For more information, see Programming Features for Reporting Services Editions.

See Also

Other Resources

Finding and Viewing Reports in Report Manager
Finding and Viewing Reports with a Browser
Features Supported by the Editions of SQL Server 2005
Reporting Services Features
Reporting Services and ReportViewer Controls in Visual Studio
Upgrading to SQL Server 2005
Installing SQL Server Express
Reporting Services Component Overview
Designing and Creating Reports
Deploying Reporting Services
Editions and Components of SQL Server 2005

Help and Information

Getting SQL Server 2005 Assistance