Using SQL Server 2005 Reporting Services with SQL Server 2005 Express Edition

From: http://msdn.microsoft.com/en-us/library/cc966542.aspx

Published: June 26, 2006

SQL Server Technical Article

Writers: Anthony T. Mann, President, Mann Publishing Group

Applies To: SQL Server 2005

Summary: This paper introduces using SQL Server 2005 Reporting Services with SQL Server 2005 Express Edition. It discusses key scenarios for using each product and how to design and develop Reporting Services solutions.

On This Page

Introduction
Downloading SSE and SSRS
Scenarios
Administration
Report Development
Testing and Staging SSRS Applications
Deploying SSRS Solutions
Report Packs
Uninstalling SSE and SSRS
Conclusion

Introduction

Microsoft® SQL Server™ 2005 Express Edition (commonly referred to as SQL Express, or SSE) is a free version of SQL Server 2005. It contains many of the important advances in SQL Server 2005 over its predecessor, SQL Server 2000, including SQL Server Reporting Services (SSRS).

This white paper introduces you to the SQL Server 2005 Reporting Services features that are available with SQL Server 2005 Express Edition. This white paper focuses on downloading, installing, configuring, designing, and deploying reports.

What is SQL Server 2005 Reporting Services?

SQL Server 2005 Reporting Services (SSRS) is a robust reporting solution that runs on Microsoft Internet Information Services (IIS) on Microsoft Windows® XP, Windows 2000, and Windows Server™ 2003. It works with data that resides on the SQL Server 2005 platform, as well as other data sources. SSRS can deliver reports in a variety of formats. The reports can be delivered on paper or through interactive Web-based reports.

SQL Server Reporting Services supports the full reporting life cycle, including:

• Report authoring. Report developers can create reports to be published to the report server by using design tools that use Report Definition Language (RDL), an XML-based industry standard used to define reports.

• Report management. Report definitions, folders, and resources are published and managed as a Web service. Managed reports can be executed either on demand or on a specified schedule, and are cached for consistency and performance.

• Report delivery. SQL Server Reporting Services supports both on-demand (pull) and event-based (push) delivery of reports. Users can view reports in a Web-based format or in e-mail.

• Report security. SQL Server Reporting Services implements a flexible, role-based security model to protect reports and reporting resources. The product includes extensible interfaces for integrating other security models as well.

Because you can configure security into reports, your sensitive data is protected. Report security specifies which reports a user can access. At a more granular level, report security uses Windows or SQL Server Authentication for data sources. You can use SSRS in SSE to create very robust reports that can be redistributed to other users, while allowing connections only to data sources that reside on the same computer as the one that runs SSRS.

There are two basic components for SSRS: Report Server and the Business Intelligence Development Studio (BIDS). Report Server is the run-time component that comes in the SQL Server 2005 Express Edition with Advanced Services. Report Server is the component that enables reports to run using SSRS. BIDS is the component that enables you to design, develop, and deploy reports to Report Server. BIDS is available in the SQL Server 2005 Express Edition Toolkit download.

Key benefits

Key benefits of using SSRS include:

• Reporting for ISV applications – Custom reports can be distributed along with SSE for a complete robust platform.

• Multiple report types – SSRS enables you to define reports as Microsoft Excel worksheets, PDF documents, images, and DHTML pages.

• Multiple reporting elements – SSRS enables you to add one or more data regions to a report to display tables, charts, graphs, and images.

• Parameterized reports – Using parameters that can be configured at runtime, reports can be customized to display data that is filtered based on those parameters.

• VBScript – Using Microsoft Visual Basic® Scripting Edition (VBScript), you can make very complex reports that contain custom functionality.

• Ad-Hoc Reporting- In SQL Server 2005 Workgroup Edition and above, SSRS includes Report Builder, a new ad-hoc reporting tool that enables business users to create their own reports. This feature is not available in SQL Express Edition.

Server 2005 Reporting Services features

SSRS is included in all editions of SQL Server 2005. Which features are available to SSRS is dictated by the edition of SQL Server 2005 that you are using. SQL Express contains a subset of the overall SSRS features.

The following table outlines the features that are available in the Express edition of SQL Server 2005, as well as features in the other editions of SQL Server 2005 and features supported by the Report Controls shipped in Microsoft Visual Studio® 2005.

Feature	Report Controls	Express	Workgroup	Standard	Enterprise
Web Service	No	Yes	Yes	Yes	Yes
Report Builder	No	No	Yes	Yes	Yes
Report Data Sources	.NET data sets	Local SQL only	Local SQL only	Extensible	Extensible
Number of export formats	Excel, PDF, Image (RGDI, Print), DHTML	Excel, PDF, Image (RGDI, Print), DHTML	Excel, PDF, Image (RGDI, Print), DHTML	Extensible	Extensible
SQL Server Agent	No	No	Yes	Yes	Yes
Maximum CPUs	Application specific	1	2	4	No limit
Maximum RAM	Application specific	1 GB	3 GB	OS Max	OS Max
Subscriptions	No	No	No	Yes	Yes
Data-driven subscriptions	No	No	No	No	Yes
SharePoint Integration	No	No	No	Yes	Yes
Caching	No	No	No	Yes	Yes
History	No	No	No	Yes	Yes
Delivery	No	No	No	Yes	Yes

Features Available in SQL Server 2005 Editions

For more information about SQL Server 2005 Reporting Services, please see http://www.microsoft.com/sql/technologies/reporting/default.mspx.

Top Of Page

Downloading SSE and SSRS

Obtaining SQL Server 2005 Reporting Services for SQL Server 2005 Express Edition is as simple as a download.

To download SSE and SSRS

1. Decide which download you need by reviewing the list of features in the previous table.

2. Go to http://msdn2.microsoft.com/en-us/express/bb410792.aspx.

3. Download the following:

   1. SQL Server 2005 Express Edition with Advanced Services

   2. SQL Server 2005 Express Edition Toolkit

4. For information on installing, see Installing SSE and SSRS.

Top Of Page

Scenarios

This section presents some ideas for scenarios under which SSE and SSRS can be used together.

Point of Sale applications

Point of Sale (POS) applications are a perfect scenario for using SSE and SSRS together. An example of a POS application is that of a retail store. Each cash register might contain a POS application that runs on SSE and provides SSRS reports. Each cash register could send its data to a centralized server each night.

SSRS reports could be used to generate real-time data stored in the local database, such as sales transactions, numbers of sales and returns, customer traffic volumes, and more.

ISV applications

Most Independent Software Vendor (ISV) applications use a database to store application data or configuration data. ISVs could distribute SSE and SSRS along with their applications.

Distributing SSE and SSRS is very simple. SSE can be distributed by using ClickOnce technology. SSRS is also simple to deploy by publishing reports to a Web service.

Web applications

Basic Web applications can benefit from SSE and SSRS applications. For example, a basic site that does not get many daily hits can store data in SSE. Then, SSRS reports can be used to generate nearly all kinds of data, such as site traffic reports, user profiles, user registration information, and more.

Top Of Page

Administration

Administering SSRS is easy. This section outlines how to install and configure SSE and SSRS.

Installing SSE and SSRS

SSRS is available as a free download. Before you install either SSE or SSRS, you must meet the basic prerequisites. They are:

• .NET Framework 2.0 – You can install the .NET Framework 2.0 by downloading it from http://msdn2.microsoft.com/netframework/default.aspx.

• Internet Information Services – This is required only if you are going to use SSRS, but not if you are going to use SSE by itself.

• Internet Explorer 6.0 SP1

• 512 MB RAM

• 600 MB Hard Disk Space

To install SSE and/or SSRS

Note If you have a beta version of SSE installed on your computer, you must first uninstall it. For instructions, see Uninstalling SSE and SSRS later in this paper.

1. Download SSE and/or SSRS. For instructions, see Downloading SSE and SSRS earlier in this document.

2. Run the executable associated with the download.

3. Install the required components for all downloads of SSE. This is shown in Figure 1.

   

   Figure 1 Installing required prerequisites

4. The installation program runs the system configuration checker. This tool automatically checks to see if you have all required prerequisites. If you don’t, an error message appears. You must fix any errors before continuing. One common error is to not have Internet Information Services installed first.

5. Choose which features to install. Figure 2 shows Feature Selection options for SQL Server 2005 Express with Advanced Services.

   

   Figure 2 Feature selection

6. After you choose which features to install, select the authentication mode for SSE. Select one of the following:

   • Windows Authentication Mode – All security permissions are assigned according to Windows accounts (either local or domain accounts).

   • Mixed Mode – Security permissions are assigned according to Windows accounts or accounts that are stored in SSE.

7. By default, Reporting Services is not marked for installation. To install it, click the arrow next to the red X and select Install this Component from the drop-down menu.

8. Specify how to install and configure Reporting Services. If IIS is installed, the default option is to install and configure SSRS. If IIS is not installed, the default option is to install only SSRS. In the latter case, you must manually configure SSRS.

   By default, SSE is installed as a named instance called SQLExpress. Therefore, the SSRS Web sites will be as follows:

   • Report Server – http:///ReportServer or http://localhost/ReportServer$SQLExpress

   • Report Manager – http://localhost/reports$SQLEXPRESS or http:///reports

9. The final step is to select whether you want to report setup errors to Microsoft and whether you wish to collect and remit usage data to Microsoft. By providing this information to Microsoft, you give valuable feedback that helps the SQL Server Express development team. By default, neither option is selected.

Configuring SSRS

After you install SSRS, you must configure it to ensure that it functions properly. Configuration is performed using ten separate steps in the Reporting Services Configuration Manager. To run this tool, open the Start menu and select Programs. Select Microsoft SQL Server 2005, then Configuration Tools, then Reporting Services Configuration. This displays the screen shown in Figure 3.

Figure 3 Reporting Services Configuration Manager

The ten areas of that you can configure are as follows:

• Server Status – Controls the state of the Report Server service. This page provides a quick glance at the overall status of SSRS. It shows a legend of icons that are used on the left-hand part of the page. It shows the following legend:

  • Configured – These items are configured correctly and are operational. Items that must be configured include the service running, virtual directories, Windows and Web service identities, and the SSRS database.

  • Not configured – These items are not yet configured, but must be configured prior to running SSRS.

  • Optional configuration – These items are optional, depending on your business requirements. Typically, encryption keys are the only optional configuration for SSRS running on SSE.

  • Recommended configuration – Configuring these items is recommended but not required for SSRS operation. Email Settings for notifications and delivery options is one recommended configuration option. Execution Account is another recommended configuration setting—use it to specify the security account to use for SSRS unattended operations.

• Report Server Virtual Directory – Controls the virtual directory for the report server, including the Web site under which the report server resides. The Report Server is the Web service that SSRS tools use to communicate with the reporting server. By default, the virtual directory name is either ReportServer or ReportServer$SQLExpress. If an SSL certificate is installed on the SSRS server, you can also require that all HTTP communication go through SSL (HTTPS). It is a good idea to use SSL if your reports contain sensitive information.

• Report Manager Virtual Directory – Controls the virtual directory for Report Manager, including the Web site under which the Report Manager web site resides. Report Manager is a graphical tool to navigate reports.

• Windows Service Identity – Manages the security account under which the ReportServer service runs. By default, this service runs under the NT Authority\NetworkService account. You might want to use a domain account instead of a built-in account.

• Web Service Identity – Manages the security account under which the Web server runs. By default, this service runs under the \ASPNET account.

• Database Setup – Configures the server, database, and credentials used to store SSRS metadata. SSE requires that the database be stored on the same computer as SSRS. You can specify the credentials used to connect to the database, as follows:

  • Windows Credentials – A local Windows or domain account is used to connect to the database.

  • SQL Server Credentials – A SQL Server account is used to connect to the database.

  • Service Credentials – A built-in Windows service account is used to connect to the database. This is the default option that is automatically selected when you install SSRS.

• Encryption Keys – Manages the encryption keys used for SSRS to make it more secure. Encryption keys enable SSRS to encode and decode sensitive data in the SSRS database, such as credit cards and social security numbers. This page enables you to perform the following actions:

  • Backup – Backs up encryption keys in case of disaster. It is a good idea to keep an encryption key backup (stored in an .snk password-protected file) in a safe place.

  • Restore – Enables you to restore encryption keys from a backup.

  • Change – Enables you to change the encryption keys used to encrypt sensitive information. If you change the encryption keys, encrypted data in the database will be reencrypted using the newly generated key. Make a backup of the keys after you select this option.

  • Delete – Removes encrypted information from the database.

• Initialization – Not available for SSE.

• Email Settings – Enables you to configure the reporting server administrator’s e-mail address and server for e-mail notifications. This option is recommended if you wish to receive reports via e-mail or to receive other notifications. Specify the account and SMTP server to use. You can use any server that is running the SMTP service or an Exchange server.

• Execution Account – Controls the security account that is used for SSRS unattended operations. Under most circumstances, when it executes or manages reports SSRS uses the account of the currently logged in user. However, there are circumstances where the user’s account cannot be used, such as when retrieving resources from an external server or when a data source is configured to not use any credentials. Under these circumstances, the account credentials to use are specified on this page. Setting the execution account is optional.

Top Of Page

Report Development

The components installed from the SQL Server Express Toolkit that work with Visual Studio 2005 are known as the Business Intelligence Development Studio (BIDS). Report development is done in BIDS. Reporting Services is installed after SQL Server Express Advanced Services is downloaded, while BIDS is installed after the SQL Server Express Toolkit is downloaded.

Creating new SSRS reports

To launch BIDS and start a new Reporting Services project

1. To launch BIDS, open the Start menu and select Run, Programs, Microsoft SQL Server 2005, SQL Server Business Intelligence Development Studio.

2. To start a new Reporting Services project, select File, New, and then Project. This displays the screen in Figure 4.

   

   Figure 4 Creating a new project in Business Intelligence Development Studio

3. Select the Report Server Project project type.

   In SSE, you have two choices for creating Reporting Services projects, whereas in other editions of SQL Server 2005, you have additional project types. The choices in SSE are:

   • Report Server Project Wizard – Guides you through creating a basic report.

   • Report Server Project – Creates a Reporting Services project, but gives you more control over your projects.

   This example shows how to create a report by using the Report Server Project type.

4. Type the name of your project and click the OK button.

5. Once the new project is created, you will see two folders in Solution Explorer: Shared Data Sources and Reports. Solution Explorer is shown in Figure 5. The Shared Data Sources folder contains a list of data sources that are shared among all reports. You use it to define a single data source that can be reused. The Reports folder contains all reports that you define. Using the Shared Data Sources folder to add a data source is covered in the next section, Adding new data sources. Using the Reports folder is covered in Creating new reports, right after the next section.

   

   Figure 5 Solution Explorer showing folder structure

Adding new data sources

To create a new shared data source, right-click the Shared Data Sources folder and select Add New Data Source on the shortcut menu. This displays a dialog box that you use to define the data source. To define the data source, specify the following:

• Name – The name of the data source that will be used in your reports.

• Type – The type of data source. While you can choose from multiple data sources, SQL Server 2005 Express can only execute reports that are run against relational databases on the local server only.

• Connection String – The connection string used to connect to the selected data source. If you wish to have BIDS generate the connection string for you, click the Edit button and fill in the desired parameters.

• Credentials – Specifies the security credentials passed to the data source. Select from these options:

  • Use Windows Authentication – Uses the credentials of the user who executes the report. This is the preferred credential setting, as it is the most secure.

  • Use a specific name and password – Uses the credentials of a specific user name and password.

  • Prompt for credentials – Forces Reporting Services to ask for login credentials from the report user.

  • No credentials – Security is not specified for a report.

Creating new reports

To create a new report, right-click the Reports folder and select Add New Report on the menu. This starts the Report Wizard, which guides you through creating the report in these steps:

• Select the Data Source – Select from a shared data source that you created earlier, or create a new data source.

• Design the Query – Specify a query to use as the data set from the data source. You can copy/paste a query that you have saved, or design a new query by clicking the Query Builder button.

  Note: The Report Wizard only lets you design a query where you specify table names or views. You cannot specify a stored procedure. However, once you exit the Report Wizard, you can change the query to a stored procedure.

• Select the Report Type – Choose from a tabular report or a matrix report. A tabular report displays data in groupings, according to the grouping field that you select. A matrix report is presented as a table without groupings.

• Design the Table (or Matrix) – Select from the fields that are returned from your query and that will be used in the different areas of your report. These areas are available:

  • Page – The fields that are displayed in the header of each page. Page fields can be used in both tabular and matrix reports.

  • Group – The fields that group data on tabular reports only.

  • Details – The fields that are used in the details section of tabular and matrix reports.

  • Columns – The fields that display columns across the top of the report on matrix reports.

  • Rows – The fields that display rows down the side of the report on matrix reports.

• Choose the Table Layout – For tabular reports only. Select from these options:

  • Stepped – Displays hierarchy of data in such a way that repeated values are not shown, but only the first occurrence is shown. This results a report that looks like it is “stepped.”

  • Block – Displays the hierarchy of data where values are repeated.

  • Include Subtotals – Includes subtotals for each grouping.

  • Enable drilldown – Enables the user to drill down into the details of a grouping. This option is only available for Stepped reports.

• Choose the Table Style (or Matrix) – Select from specific predefined styles.

• Report name – Name your report. This name appears in Report Manager menus, or in the URLs that you give your users.

After you finish the wizard, a report with an .rdl file extension is created. RDL stands for Report Definition Language. This is an XML file that contains all the elements necessary to represent the definition of the report. Opening this file in BIDS, displays a screen similar to the one shown in Figure 6.

Figure 6 Finished report shown in the BIDS Designer

Designing SSRS reports

Once you create a report by using the BIDS Report Wizard, you can customize and design it to suit your own needs. You can alter almost anything on the report. For example, you can:

• Change the query from an SQL statement that uses tables and views to a stored procedure.

• Change fonts and the general look and feel of your reports.

• Rearrange elements such as text boxes and logos.

• Add interactive elements, such as subreports, charts, and conditional formatting.

• Add code to your reports in VBScript.

The Reporting Services design surface, called the Report Designer, is divided into three parts, which you access by clicking the corresponding tabs at the top of the screen. Following are the three Report Designer parts.

• Data – Defines the data source and data set used in the report.

• Layout – Defines the layout of the elements on the report.

• Preview – Use to preview a rendering of the report in HTML format.

As with other Visual Studio 2005 projects, you add items to a report by dragging tools from the toolbox onto the Report Designer when the Layout tab is selected. The Reporting Services toolbox is shown in Figure 7.

Figure 7 Reporting Services toolbox

To design a Reporting Services report, drag the elements you want to use onto the Report Designer.

Top Of Page

Testing and Staging SSRS Applications

It’s easy to test and stage your SSRS applications. Your approach to testing and staging depends on your company’s policy for testing and staging any application. Some companies simply test on a development workstation and publish right to a server. Others have more rigorous testing requirements, which is recommended.

The first step to testing is to preview your reports. The Report Designer has a Preview tab. Click this tab to display your report. Figure 8 shows an example in the Business Intelligence Development Studio.

Figure 8 Previewing a report in BIDS

Once you know that the report behaves the way you expect, you are ready to move the report onto a staging server or into production, depending on how your organization manages its testing and staging environments. If you plan to deploy your SSRS applications to a staging server, follow the instructions in the next section.

Top Of Page

Deploying SSRS Solutions

When you make your SSRS reports available on a staging or production server, you do so by deploying them. Your reports will need the data sources that supply data to the reports. You can choose whether to overwrite a data source of the same name on the SSRS server during deployment. The default behavior is to not overwrite existing data sources, but to overwrite existing reports.

Before you deploy your reports, you must configure the deployment properties for your SSRS project. Right-click the name of your project in the BIDS Solution Explorer to display the screen shown in Figure 9.

Figure 9 SSRS Reporting project properties

You must configure these properties before you can deploy the report:

• OverwriteDataSources – Enables you to overwrite the shared data sources that you have configured in your project. The default is False.

• TargetDataSourceFolder – Folder to create on the server where your shared data sources will reside.

• TargetReportFolder – Folder to create on the server where your reports will reside. This lets you classify the reports however you wish.

• TargetServerURL – URL of the SSRS Web service. This is probably the ReportServer virtual directory on your target server.

After you configure the properties for your deployment target, you can deploy the target. Right-click the name of your project in BIDS Solution Explorer and select Deploy. Your reports (and optionally data sources) will be deployed to the specified target server.

Viewing reports

There are two ways to users can view reports:

• Report Manager – Users can view reports by using Report Manager, a Web-based application that is included with Reporting Services. By default, Report Manager is available at http://localhost/reports$SQLEXPRESS.

  Important Note: If you have named your database instance other than SQLEXPRESS, replace the text SQLEXPRESS in the above URL with the name of your instance.

• The Report Viewer control – This control enables you to embed reports in your applications by using Visual Studio 2005. The Report Viewer control is available for both Windows applications and Web applications.

The Report Viewer control has two operating modes. The first displays reports that are hosted on a Reporting Services Web service. The second displays embedded client-side reports that are not hosted on the Web service, similar to the way you embed Crystal Reports into Visual Studio applications.

The version of SSRS that is included with SSE makes reports available via the Report Server Web service. To use the Report Viewer control for developing client-side reports, you can use Visual Studio Web Developer Express or Visual Studio 2005 Standard Edition or higher.

The example presented here covers the following operations:

1. Use SQL Server 2005 Express with Advanced Services to create a report.

2. Publish the report to the Reporting Services Web service. For more information about publishing a report, see Deploying SSRS Solutions later in this paper.

3. Open Visual Studio 2005 and create a Windows application.

4. Embed an instance of the Report Viewer control onto a form and bind it to the Web service.

Figure 10 shows a Visual Studio 2005 solution with a Windows project. This project contains a single blank form.

Figure 10 Selecting the Report Viewer control

Drag the Report Viewer control onto the form. Then, set the properties of the report to execute, as shown in Figure 11. Remember that SSRS that comes with SSE can publish reports that the Report Viewer control can use.

Figure 11 Selecting Report Server properties

To select a specific report, select from the Choose Report menu. Specify the Report Server Url and the Report Path. The Report Server Url is the URL of the SSRS Web service. This is typically, http:///reportserver. The Report Path is the path to which you publish your reports. Figure 11 shows a top-level folder called DailyReports, with a report name of Report1. The first character of the report path must always be a slash.

Note If you are using Visual Web Developer Express, the Report Viewer control add-in is a separate download available from Microsoft at the Visual Web Developer Express page ( http://www.microsoft.com/express/download/).

Setting report security

Report security is configured by using role-based settings. In role-based security, you use roles that are assigned sets of permissions. You assign specific users or groups to those roles. Role-based security is very easy to implement.

SSE comes with predefined roles. You cannot change these roles nor can you create new ones. The following predefined roles are available to SSRS in SSE.

• Browser – Use to view folders, reports, and to subscribe to reports.

• Content Manager – Use to manage all aspects of content, including creating folders, reports, and data sources.

• My Reports – Use to publish reports, create folders, and manage resources in the My Reports folder.

• Publisher – Use to publish reports.

• Report Builder – Use to view report definitions (RDL).

To assign a Windows user or group to a predefined role

1. In Internet Explorer, open Report Manager by going to: http://localhost/reports$SQLEXPRESS.

2. Ensure that you are viewing the Report Manager home page.

3. Click the Properties tab to display the screen shown in Figure 12.

   

   Figure 12 Viewing users and roles

4. To assign a user to a role, click the New Role Assignment button. This displays the screen shown in Figure 13.

   

   Figure 13 Adding users to a role

By default, report security is inherited from the assignments that you set here. To change the security settings for an individual report, navigate to the report, click the Security tab, and making the desired changes. However, if you do this, the report will no longer inherit from its parent.

Top Of Page

Report Packs

Microsoft periodically releases add-ons to SQL Server Reporting Services that give you sets of prebuilt reports. These reports enable you to extend your environment in a variety of ways, depending on the software you have installed in your organization. Report packs are available at no charge. They can be freely downloaded from the SQL Server Report Packs page ( http://www.microsoft.com/downloads/details.aspx?FamilyId=D81722CE-408C-4FB6-A429-2A7ECD62F674). The following report packs are available at the time of this writing:

• SQL Server 2005 Report Pack for SQL Server Integration Services

• SQL Server 2005 Report Pack for Microsoft SharePoint® Portal Server 2003

• SQL Server 2005 Report Pack for Microsoft Dynamics Axapta 3.0

• SQL Server 2005 Report Pack for Microsoft Dynamics Navision 4.0

• SQL Server 2005 Report Pack for Microsoft Dynamics Great Plains 8.0

• SQL Server 2005 Report Pack for Microsoft Dynamics Great Plains 9.0

• SQL Server 2005 Report Pack for Microsoft Internet Information Services (IIS)

• SQL Server 2005 Report Pack for Financial Reporting

For example, if you are running IIS, you can report from the logs by installing the IIS report pack, changing a couple of parameters, and then running or deploying these reports.

To run these reports on SQL Server Express, you need to attach the included databases (.mdf files) included with the report packs to SQL Server Express (hint: you can use SQL Server Management Studio Express, where you can also easily browse the database schemas and get a better understanding of how the report packs work).

Make the following changes in Business Intelligence Development Studio (BIDS).

1. On the Project menu, select Properties.

2. Set the TargetServerURL field to http://localhost/reportserver$SQLEXPRESS.

3. To close the Properties page, click OK.

4. To display the Solution Explorer pane, on the View menu, select Solution Explorer.

5. In Solution Explorer, expand the Shared Data Sources list by clicking on the + symbol next to the folder icon.

6. Double-click the .rds file.

7. In the Connection String field, replace (local) with .\SQLEXPRESS.

8. Click OK.

After you complete these steps, you can preview your reports in BIDS and then deploy them to your report server.

Once you have downloaded and installed a report pack, a new project folder is created that you can modify as you wish. For example, the IIS Report Pack is installed by default in C:\Program Files\Microsoft SQL Server Report Packs\Microsoft SQL Server Report Pack for Internet Information Services. There is a solution file in the Reports folder in the same hierarchy. The project contains numerous reports that work out-of-the-box. Figure 14 shows what the solution looks like at design-time in BIDS.

Figure 14 IIS Report Pack at design time in BIDS

Top Of Page

Uninstalling SSE and SSRS

If you installed a beta version of either product prior to the final release outlined in this paper, the easiest way to uninstall it is to use a special uninstall tool that Microsoft has released. It is located at http://msdn2.microsoft.com/express/aa718398.aspx. The uninstall tool removes any previous beta versions of SSE and Visual Studio 2005.

If you are removing the final version of SSE or any of its components, open Add/Remove Programs in Windows Control Panel. Select Microsoft SQL Server 2005 to change or remove any components. Select what to uninstall or remove as shown in Figure 15.

Figure 15 Selecting a SQL Server 2005 Express component to remove

Top Of Page

Conclusion

The version of SQL Server 2005 Reporting Services that is available with SQL Server 2005 Express with Advanced Services provides powerful reporting functionality for your applications. Now you can embed interactive reporting capabilities into your data-driven applications and provide a rich presentation of the data to users.

For more information:

http://www.microsoft.com/technet/prodtechnol/sql/default.mspx

Did this paper help you? Please give us your feedback. On a scale of 1 (poor) to 5 (excellent), how would you rate this paper?

!href(mailto: sqlfback@microsoft.com?subject=Feedback: Developing Custom Components in SQL Server Integration Services for Project REAL)

Download

UsingSSRSwith SQLExpress.doc
1.6 MB
Microsoft Word file
Get Office File Viewers