How to Migrate Access Reports to Reporting Services

Writer: Adam Cogan

Technical Reviewers: Andrew Weaver, Jatin Valabjee

Applies To: SQL Server 2000 Service Pack 3a (SP3a)

Summary: Microsoft® SQL Server™ Reporting Services is a powerful new tool allowing developers to move reporting solutions online for truly global access. This paper provides the following information:

• Details how to migrate existing reports from Microsoft Access to Reporting Services.

• Steps through a practical and detailed demonstration of the successful migration of a number of familiar Access reports, and addresses the more critical migration issues.

On This Page

Introduction
How to Use the Import Reports Feature
Conclusion
Tips
Glossary – New Terms for Access Developers

Introduction

Microsoft® SQL Server™ Reporting Services is a new reporting platform that has even more functionality and flexibility than Microsoft Access and Crystal Reports developers have become accustomed to. It combines the speed, scalability, and manageability of centrally managed reporting, because reporting has moved from the client model to the client/server model. This brings all the advantages of Web-based applications.

This paper details how to use the Reporting Services Import Reports feature to convert reports from Access to Reporting Services and resolve any resulting issues.

Note While this paper focuses on the Access developer, many of the concepts are similar for Crystal Reports developers.

For further technical information, report samples, and information about obtaining Microsoft Reporting Services, see https://www.microsoft.com/sql/reporting.

Important This paper does not address all the possible issues that you may encounter during Access report migration. As more issues are encountered, they will be added to the updated version of this paper, which can be found at https://www.ssw.com.au/ssw/Standards/DeveloperSQLReportingServices/.

Reporting Services does not require you to migrate your data from Access to SQL Server. However, migration of your data to SQL Server is recommended because SQL Server is a more robust, flexible, and scalable data solution than Access. Reporting Services reports can use a variety of data sources, including ODBC and OLE DB compliant databases.

Examples and notes in this paper assume that reporting data is stored in Access.

How to Use the Import Reports Feature

In most cases, migration of Access reports to Reporting Services reports is as straightforward as a single-click import process. However, after they are imported, you may encounter some migration issues. This section details these issues and methods to resolve them.

The process of migration involves three steps:

1. Using the Reporting Services Import Reports feature on the Access database.

2. Resolving any migration issues that are listed in the task list.

3. Taking advantage of the management features of Reporting Services, such as security and performance (caching).

Throughout this paper, samples based on the Northwind Access database are used to demonstrate the report migration process. The Northwind database is shipped with Access and can be found in the Samples subdirectory of the Access installation directory. Alternatively it can be downloaded from https://www.microsoft.com/downloads/. To demonstrate new features in Reporting Services, the AdventureWorks SQL Server database is used. The AdventureWorks database ships with Reporting Services.

Prerequisites

The following are prerequisites:

• The Access database (.mdb file) or Access project (.adp file).

• Microsoft Access 2002 or later.

• Microsoft SQL Server Reporting Services.

• Microsoft SQL Server 2000 SP3a.

• Microsoft Visual Studio® .NET with Reporting Services client tools installed.

You can migrate from earlier Access database files if Access 2002 or later is installed.

Reporting Services can import reports from databases created with Access 2000 or earlier. However, to allow access to the Import Reports feature in Visual Studio .NET, Access 2002 or later must be installed. When importing, Reporting Services may prompt you to convert the database to a later version. We recommend that you upgrade the database to the newer format, however, upgrading the database is not required.

Use the following procedure to upgrade an earlier Access database before importing:

1. Ensure that no copies of the database are open.

2. Install Access 2002 or later.

3. To open the file, click File, click Open, and browse for the database.

4. To convert and save the database with a new name, click Tools, click Database Utility, click Convert Database, click To Access 2002 File Format, and save the database with a new name.

The reports from the new database can now be imported into Reporting Services.

Scenario

This following sections step through the process of importing and resolving migration issues encountered with the following reports:

• Invoice report (a standard report).

• Catalog report and Catalog subreport (a report with a linked subreport that displays images stored in a database).

• Employee Sales by Country report (a report with conditional formatting and parameters).

• Sales by Category report and Sales by Category subreport (a report with a chart).

The First Steps for Migration

1. Create a new report project in Visual Studio .NET (see Figure 1).

   

   Figure 1   Creating a new report project

2. Use the Import Reports feature to import the Access database reports into Reporting Services (see Figure 2). Note that Reporting Services will open the database in Access during the import process. To use this feature, right-click the Reports folder within the solution and select Import Reports.

   

   Figure 2   Importing reports from Access

   When you import the reports, Access will open while the report import is taking place. The new window will close when the process is completed.

   Note There is no option to selectively import reports—they are all automatically imported. All suggestions for improvements to Reporting Services are beyond the scope of this document, but for ideas for better software, see https://www.ssw.com.au/ssw/Standards/BetterSoftwareSuggestions/
   ReportingServices.aspx.

3. View the task list to see any errors or warnings that occurred during the import process (see Figure 3). Following the scenario, only four reports will be migrated. Therefore, systematically remove the warnings for unwanted reports using the following steps.

   

   Figure 3   Task list build errors (warnings)

   You will have 23 build errors from the import of Northwind. Despite the name, they are only warnings and the reports can still be viewed and deployed.

   Note The icon depicts a build warning, that is, an error that occurred while attempting to import certain report elements from Access. Typically many of these items will only affect the layout or formatting of the report rather than the functionality.
   Task list management is different in Reporting Services. In Visual Studio .NET, developers see a task list item, click it to go to the appropriate place in the code, address the issue, and then see the task list item disappear. In Reporting Services. developers manually maintain the task list, which is not as interactive. So you need to be careful in the way you delete reports and manage task list items. (For more information, see the following steps.)
   You cannot rely on the task list to do further filtering such as filtering for the current file. Also, double-clicking the task list does not open the related report.

Unfortunately, items in the task list will persist even after the report is deleted. Take care to delete reports one at a time, so that you can keep control of your build warnings. Perform the following steps to manage the items in your task list:

1. Note the build errors related to the report that you want to delete.

2. Check all the build errors related to that report (using the check box next to the task description).

3. Delete the report.

4. Repeat these steps for all 10 reports.

5. Hide all the selected tasks using the Show Tasks shortcut menu shown in Figure 4.

   

   Figure 4   Filtering the task list to only show the relevant warnings

6. When your Reporting Services project is restarted, items in the task list will disappear. We recommend that you copy the task list contents to a Word document so that you can refer to it later as shown in Figure 5. Right click the task list and click Copy, and then paste the information into a Word document to preserve the warnings.

   

   Figure 5   Saving task list information

7. The highlighted reports are the ones that you want to delete in this scenario. Because of the problem with build warnings, you should delete them one-by-one. Delete the unnecessary project reports as shown in Figure 6.

   

   Figure 6   Deleting reports

The Steps to Successfully Migrate the Invoice Report

No build warnings are associated with the Invoice report. This section describes the actions required to migrate the Invoice report.

Inspect the Invoice report in Layout  view and in Preview  view. At the same time, open the report in Access to allow efficient comparison.

In Layout view, the reports look almost identical. However, when you preview the report, you should notice the large number of pages (830) in the report (see Figure 7).

The Invoice report with 830 pages is incorrect. It needs to be changed to use report parameters.

Figure 7   The invoice report with incorrect number of pages

Note Access developers typically do not parameterize reports so that they can use an external filter (code outside the report) to refine the report data at runtime. For example:

DoCmd.OpenReport "Invoice", acViewNormal, , "OrderID = " & [ctlOrderID]

Although the Preview view looks the same when you open the report in Access, the method the data is bound to the report needs to be changed. In Northwind, the Invoice report was opened through an Access form, and the event for opening the report used the DoCmd.OpenReport method, which allowed a filter to be placed on the report automatically so that only a single invoice would be loaded. Hence the first task is to add a report parameter to refine the invoice search.

Other Issue 1 – Adding Report Parameters

To add a report parameter to the Reporting Services invoice, switch to Layout view for the report. From the menu, go to Report and select Report Parameters as shown in Figure 8.

Figure 8   Adding a report parameter

A dialog box opens where you can manage all the parameters for the report. Parameters can have a number of types, such as String, Integer, Boolean, DateTime, or Float. Each parameter can be limited to the results of some dataset (so that a drop-down list appears) and can also have default values (optionally drawn from a dataset as well).

OrderID will be added as the new parameter of type Integer and with nonquery available values and no default value (see Figure 9).

Figure ** 9** **   Add the OrderID parameter**

Now that the report parameter has been added, connect the report parameter to the query that the dataset is based on. To do this, select the Data  tab and select the main dataset that the report uses. Then complete the following steps to add the parameters to the dataset and connect the report parameters to the query parameters.

1. Set the report dataset to accept parameters.

   1. Add a PARAMETERS clause to the query.

   2. Add a WHERE clause to the query using the parameters listed in the parameter clause (see Figure 10).

      

      Figure 10   Add the PARAMETERS and WHERE clauses to the report dataset query

2. Associate the report parameters with the query parameters (see Figure 11).

   1. Select the Data tab , select the dataset, and then click the ellipsis button .

   2. Select the Parameters tab and match the parameter names from the query to the report parameters. Click OK to save the settings.

      

      Figure 11   Connect query parameters to the report parameters

Now when you preview the report, you should notice the report toolbar requires a parameter value to be entered before progressing. For example, in Order Number, type 10643 and click View Report as shown in Figure 12.

Figure 12   Previewing the parameterized Invoice report

Now the migration of the Invoice report is complete, and will be an exact replica of the original Access report.

The Steps to Successfully Migrate the Catalog and Catalog Subreport Reports

Now issues associated with the migration of the Catalog report are addressed. The following build warnings were raised during the import so these will be handled first.

Catalog: The control HeaderPageBreak is an unsupported type.
Catalog: The control Picture is an unsupported type.

Task List Item 1 – Forced Page Breaks

The first build warning is an issue concerning the report layout, control grouping, and pagination controls within the Access report. These are typical problems concerning report migration. This problem is caused by a forced page break within the header of the report. When this report is converted to Reporting Services, the entire header is placed into a single rectangle and this page break is lost (see Figure 13).

Figure ** 13** **   The forced break within the Access header section is lost in the conversion**

To re-create page breaks, perform the following:

1. Related items can be grouped using the Rectangle control . Drag a new Rectangle control onto the report layout.

2. Drag all the contents that are to be on a separate page into the newly created rectangle.

3. Set the properties of the rectangle to allow page breaks before and after for proper pagination as shown in Figure 14.

   

   Figure ** 14** **   Select both check boxes for the Page Break properties for a Rectangle control**

4. Select the check box for this task list item to show it as complete. There should still be one remaining item for this report.

Task List Item 2 – Database Images

The second build warning was issued because the Access report makes use of a bound object control to display pictures that are stored on the database (as shown in Figure 15).

Figure ** 15** **   An Access bound object frame for database images does not get imported**

Reporting Services offers similar functionality when reading from an Access database, however, the source for the image needs to be corrected to remove some of the information added by the Jet or OLE data source driver for Access databases. To create the new image control, perform the following steps:

1. Select the Image  button from the toolbox and drag it onto the report next to the Catalog item description box.

2. In the Image Creation Wizard, select the image source as a Database type, and select the field from the dataset that contains the image data.

3. Replace the default value in the image Value field with the following expression as shown in Figure 16:

   =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String
   

(Fields.Picture.Value),105))

where **Picture** is the database field with the stored image data.

![Figure 16 Add a new Image control to the page and use the \<Expression...\> field to set the value](images/Cc966391.figure16(en-us,TechNet.10).gif "Figure 16   Add a new Image control to the page and use the \<Expression...\> field to set the value")

**Figure** ** 16** **   Add a new Image control to the page and use the \<Expression...\> field to set the value**

4. Select the check box for this task list item to show it as complete. There should be no more tasks remaining for this report.

Other Issue 1 – Linked Subreports

Additional migration problems must be investigated. Again, use the Layout view and Preview view for the report. Having resolved the previous issues, the layout appears to be the same. However in the Preview view, notice that every product is listed under every category (see Figure 17). The products in the report must be filtered based on their parent category.

Figure ** 17** **   The Catalog report shows all products under every category**

The Catalog report shows all products under every category. The link between the master and subreport is not imported.

Within Access, subreports can be linked by some specified field so that the subreport is automatically filtered based on the value in the master report. Figure 18 illustrates how this is done within Access.

Figure ** 18** **   Properties that link the master and subreport in Access and that are lost during the import**

When reports are imported, linked subreports do not remain linked in Reporting Services reports.

To link the subreport to the master report, add the report parameters to the CatalogSubreport so that the master report can query the subreport for a specified CategoryID. Perform the steps detailed in the earlier section: The Steps to Successfully Migrate the Invoice Report. In this case, however, use the CategoryID as the query and report parameter.

Having parameterized the subreport, connect the frame for the subreport in the master report to pass the parameter value to it. To do this, open the properties for the subreport on the Catalog report and click the Parameters tab. Select the parameter name for the parameter that you just created and link it to the field that is being used for data region grouping (the CategoryID field) as shown in Figure 19.

Figure ** 19** **   Connect the subreport parameters to the subreport control in the master report**

The subreport is now linked to its master report and will be filtered based on the specified parameters that are passed to the report. When you preview the report, you should now find that the report has its report header properly separated onto two pages, the images for each category are shown, and only the products relevant to the category are shown.

The Steps to Successfully Migrate the Employee Sales by Country Report

Now the issues associated with the migration of the Employee Sales by Country report are addressed. The following build warnings were raised during the import, so these are handled first.

Employee Sales by Country: The object GroupHeader2 is associated with the 
     ON-FORMAT event. Events are not supported.
Employee Sales by Country: Modules are not supported.
Employee Sales by Country: The object 
     Employee_x0020_Sales_x0020_by_x0020_Country is associated with the 
     ON-NO-DATA event. Events are not supported.
Employee Sales by Country: The object GroupHeader0 is associated with the 
     ON-FORMAT event. Events are not supported.

Warning If at any point while editing this report you click the Data tab, the Report Definition Language (RDL) file is automatically cleaned, and when you return to the Preview tab, the following error occurs:

An error has occurred during report processing.
Query execution failed for data set 'DataSet1'.
No value given for one or more required parameters.

To fix the problem, alter the dataset parameters property to connect the report parameters to the parameters that are defined within the Access query that is being used to draw data from. Use the at sign (@) symbol to indicate such parameters, and the parameter name will need to exactly match the ones in the Access query as shown in Figure 20.

Figure ** 20** **   Connect the report parameters to the Access query parameters**

Note This is similar to the steps described in The Steps to Successfully Migrate the Invoice Report for connecting the query parameters to the report parameters.

Task List Item 1 – Conditional Formatting

The first task list item refers to the conditional formatting code in Access used in this report to display the Exceeded Goal! red label if the employee being viewed exceeded the sales target. In Access report designer, this code is found in the Format event for the group header containing the label (see Figure 21). In Access 2002 or later, conditional formatting can be placed into the control itself (as shown in Figure 22).

Figure ** 21** **   Example Format event code for conditional formatting in Access**

Figure ** 22** **   Example conditional formatting code (different from Northwind)**

As previously mentioned, Reporting Services does not support events and will not import this event. To apply conditional formatting to report items in Reporting Services, expressions are used for any number of the style properties that need to be controlled. Expressions can be used to dynamically control the values for many properties. For example, to set the visibility of the Exceeded Goal! label based upon the total amount of sales, see Figure 23 and Figure 24.

Figure ** 23** **   Add an <Expression...> to the Hidden property of the Exceeded Goal! textbox to simulate Access conditional formatting**

Figure ** 24** **   Set the expression for the ExceededGoalLabel to toggle visibility with this expression**

Note A build warning in the task list will only appear for formatting carried out within the Format event. Formatting carried out by the conditional formatting dialog box in Access is not imported and will not cause a build warning.

If you are using any form of conditional formatting, you will need to enter the formatting manually.

Note The hidden property is being set so that the control is not hidden if the amount is greater than 5000.

Select the check box for this task list item to show it as complete. There should still be three remaining items for this report.

Task List Item 2 – Modules Not Supported

This task list item refers to the fact that Reporting Services does not support custom Access modules, that is, code behind an Access report. This code must be added manually as expressions in Reporting Services. The other task list items all are related to this item and should resolve this issue, so this will be addressed later in this paper.

Task List Item 3 – No Data

When the data source returns no data in an Access report, a custom event can be used to control the content that is displayed, or to perform some other function. This is achieved by using the report NoData event (see Figure 25 and Figure 26). Reporting Services has a similar functionality. However, this can apply to any data region type (for example, list, table, and matrix) through the No Rows property (see Figure 27). A message can be used in place of the control’s content, or an expression can be written to carry out some other event.

Figure ** 25** **   The Access NoData event**

Figure ** 26** **   The Access code for the NoData event**

Figure   27   The Reporting Services No Rows property for a data region type control provides equivalent functionality

In the No Rows properties for the main Details list, set the expression to display the string "No Data for Date Range" (see Figure 28).

Figure ** 28** **   Add the message to the <Expression...> for the No Rows property of the list control**

Select the check box for this task list item to show it as complete. There should still be two remaining items for this report.

Task List Item 4 – Events Not Supported

This is the same warning produced by task list item 2. It refers to the fact that custom code behind Access reports is not supported in Reporting Services. Any code can be moved to an expression for the report item. In this case, the code restarts the page numbering at the beginning of each new group as shown in Figure 29. Currently there is no support for this type of action in Reporting Services.

Figure ** 29** **   The Access code that caused the Reporting Services event warning**

Select the check box for this task list item to show it as complete (as this cannot be resolved). There should still be one remaining item for this report

Task List Item 2 (Revisited) – Modules Not Supported

Having reviewed all of the functions for the code module of the report throughout the course of resolving the other task list items, the task list item is complete. Select the check box for this task list item to show it as complete. There should be no more remaining items for this report.

Other Issue 1 – Parameters

The Employee Sales by Country report requires two parameters—start date and end date. These parameters were imported during the process, however, their properties show that they have less than friendly names (and they are of string type). To correct these issues, open the Report Parameter dialog box (as described in The Steps to Successfully Migrate the Catalog and Catalog Subreport Reports) and correct these issues as shown in Figure 30.

Figure ** 30** **   Change the parameters to use DateTime data type and give them more user-friendly names**

The Steps to Successfully Migrate the Sales by Category Report

Now the issues associated with the migration of the Sales by Category report will be addressed. The following build warning was raised during the import so this is handled first.

Sales by Category: The control SalesChart is an unsupported type.

Task List Item 1 – Charts

Charts created in Access are not imported into Reporting Services. They must be re-created manually using the Reporting Services chart tool, which is a relatively straightforward process. The following process shows how to convert the bar chart in the Sales by Category report (see Figure 31) to Reporting Services.

Figure ** 31** **   A section of the Sales by Category report showing the chart in the Access report**

1. Open the report in Reporting Services and select Layout mode . Select the Chart tool from the toolbox, and drag a box of a reasonable size onto the report layout to create a chart as shown in Figure 32.

   

   Figure ** 32** **   The Chart control in the Report Designer**

2. The data to display on the chart now needs to be set. Click the Fields  tab button at the lower left of the screen to display the available data fields. Double-click the chart to display the chart field placeholders.

3. Set the data fields, that is, the fields that consist of the values in the chart and are represented by the bars. Drag the ProductSales field into the Drop data fields here box. Then drag the ProductName field into the Drop category fields here box to set the grouping of the chart. In this case, the product sales are grouped by product name.

4. Set the properties of the chart to match the chart in the Northwind report. Right-click the chart and select Properties. Set the chart type to Bar, select the Legend tab, and clear the Show Legend check box. Click OK to confirm changes. Click the Preview tab to preview the report as shown in Figure 33.

   

   Figure ** 33** **   Check that the preview of the chart looks correct**

5. Notice that the chart is sorted in reverse alphabetical order, whereas the chart in the original Northwind report is sorted alphabetically. Set sorting properties for the chart by right-clicking the chart, and selecting Properties.

6. Select the Data tab, select the first item in the Category Group list box (the default name is chart1_CategoryGroup1) and click Edit. Click the Sorting tab, choose =Fields!ProductName.Value in the first row under the Expression heading, and select Descending under the Direction heading (see Figure 34).

   

   Figure ** 34** **   Setting the sort order for the bar chart**

Other Issue 1 – Linked Subreports

When you enter Preview mode for the Sales by Category report, you will notice that the subreports do not appear correctly. All products appear under every category. The link between the main report and the Sales by Category subreport has not been preserved correctly. The subreport needs to have its parameters linked to the main report. For instructions about linking subreports to main reports, see Other Issue 1 – Linked Subreports under The Steps to Successfully Migrate the Catalog and Catalog Subreport Reports.

Deploying the Reports

The reports have now been successfully imported to Reporting Services and can now be deployed to the reporting server (see Figure 35). Ensure that the report has been set to deploy to the correct reporting server by selecting the report project in Solution Explorer, selecting the Project menu, and choosing Properties.

Figure ** 35** **   Deploy the report project**

The reports are now successfully deployed and other users can now view these reports in their browser.

For more information about report deployment in Reporting Services, see https://msdn2.microsoft.com/library/aa256327.aspx. Also for suggestions for optimizing and fine-tuning reports, see the Tips section later in this paper.

Conclusion

Microsoft SQL Server Reporting Services is a powerful reporting solution that provides centralized management, an open architecture for robust extensibility, and seamless integration into existing business environments.

Migration of existing reports from Microsoft Access to Reporting Services is often as straightforward as a quick import process. After they are imported, expect some compatibility issues, and use some of the previously described techniques to resolve them.

Tips

We highly recommend that you read and implement the following tips to ensure a manageable and efficient Reporting Services solution.

Tip 1 – Use a Shared Data Source

When a report is imported into Reporting Services, a connection string is generated that contains a reference to the Access database. This is done once for each report, that is, each report contains its own connection string. If multiple reports use the same database (as is often the case), moving the connection string to a common location facilitates easier updating of the data source. For example, if the reporting Access database is moved to another location, the connection string would only need to be changed in one location rather than for each report using data from the same data source.

Reporting Services has a feature called a shared data source item, which defines a connection to a frequently used data source. To create a data source and set all reports to use this source, follow these steps:

1. Select the project in Solution Explorer, and add a new Data Source to the project as shown in Figure 36.

   

   Figure ** 36** **   Adding a shared data source**

2. In the Data Link Properties window, choose the Provider tab, select Microsoft Jet 4.0 OLE DB Provider, and click Next.

3. Browse for the reporting Access database, provide a user name and password (if required), and click Test Connection to test the database connection. If this is successful, click OK to close the window. If not, verify that the user name and password are correct and that the database is not open.

4. Link all reports to use the shared connection. Open the first report in the Solution Explorer list and select its Data tab . Click the ellipsis button next to the dataset drop-down list to edit the source of data for the report. In the resulting Dataset window, select the shared data source (usually named DataSource (shared)) from the Data source drop-down list. Click OK to close the window. Repeat this process for each report.

Tip 2 – Run Regular Expressions to Ensure Your Reports Follow Company Rules

Because Report Definition Language (RDL) files are plain text files, code sniffing tools can be run to ensure reports follow your company standards. For example, to ensure all reports are using a shared data source and do not have the connection string behind the report, you could run a regular expression as follows:

<ConnectString>.*</ConnectString>

You can write code in .NET by using System.Text.RegularExpression, or you can use third-party tools. Examples of third-party tools include the following:

• UltraEdit (www.ultraedit.com).

• SSW Code Auditor (www.ssw.com.au/CodeAuditor). (For more information, see the following example.)

SSW Code Auditor provides a graphical wizard-style interface to make the task of building regular expressions easier. It analyzes your RDL files against a set of rules, and produces a report (as shown in Figure 37), which can then be used to correct any errors found.

Figure 37     Because RDL files are in text, tools such as SSW Code Auditor can show reports on files that break specified rules—leading to better quality control

Table 1 lists regular expressions that you can use to verify the correctness of your reports.

Table 1   Regular expressions to use on your RDL files

Rule	Regular expression
A connection string was found in the report. All data connection information should be removed from every report and moved to a shared data source item in the report project (see Tip 1 – Use a Shared Data Source).	<ConnectString>.*</ConnectString>
The SQL query used for the report data should not contain "SELECT *", because it is an inefficient method of selecting data. Only the fields required for the report should be retrieved, for example, "SELECT ID, FirstName, LastName, PhoneNumber".	(?i:SELECT) \*
Build warnings should not exist in the report. These warnings are typically caused when importing unsupported report items from Access into Reporting Services, and appear as XML comments in the RDL file.	<!-- (?i:Warning).*-->

Tip 3 – Organize Images

Reporting Services offers three options for storing images on the server:

• Embed the image as a MIME type into the report directly.

• Store the image on the server and link it from the report.

• Get the image from a database.

Typically, within a report project, the images should be placed in a subfolder of the main project. This way, all reports can make use of the same images, so that if any image is changed, no changes to any of the reports are required. The same should be done within a Reporting Services report.

You have two options for doing this. The first is to use Report Manager to manually create a new Images subdirectory of the deployed project and upload each image into this directory. The problem with this method is that the images are no longer directly associated with the reporting project. The second is to use Visual Studio .NET to handle the deployment of images when deploying reports. Subdirectories cannot be used in the Reporting Services project, so a separate Visual Studio report project must be created that contains all images as resources. When this project is added to the main report project, the images contained are deployed when the reports are deployed.

Tip 4 – Other Projects Within the Reporting Solution

Typically a Reporting Services project will be only one part of an application’s solution file. After development has been finished on the reporting component of the solution, autodeployment should be deactivated to save time when rebuilding the solution. Perform the following procedure in Visual Studio .NET:

1. Open the properties dialog box for the solution.

2. Select Configuration Properties.

3. Clear the Deploy check box for the reporting project as shown in Figure 38.

   

   Figure ** 38** **   You can turn off Deploy in Solution compilation options to speed up compilation time—useful when you are working with .aspx pages**

Tip 5 – Easy Access to Reports for Users

All Reporting Services reports are stored in one location on the Reporting Server. The URL to the reports is usually in the format https://servername/reports. To facilitate easy access to the reports for users, add a hyperlink to your application.

Glossary – New Terms for Access Developers

.NET A set of Microsoft technologies with the goal of providing individual and business users with a seamlessly interoperable and Web-enabled interface for applications and computing devices, and to make computing activities increasingly Web browser-oriented1.

Conditional formatting Setting formatting of a report element (text label, image size) based on a certain condition. For example, showing a price in red if the value is more than $500.

Expression Used by Reporting Services to retrieve data from database fields and to define conditions and perform calculations. Also used to invoke external business logic.

eXtensible Markup Language (XML) A widely adopted standard way of representing text and data in a format that can be processed without much human or machine intelligence.

Internet Information Services (IIS) The Web server package installed with Microsoft operating systems.

Linked subreports Subreports that have their content filtered based on some bound field in the master report.

Microsoft SQL Server Reporting Services A Microsoft reporting solution which provides a complete set of tools and applications that can be used to author, publish, and manage reports2.

Reporting Services Report Definition Language (RDL) An XML structure used by Reporting Services that encompasses all possible variations that a report can assume. At a minimum, it specifies a query and other report content, report properties, and report layout.

Report Designer A tool used to publish reports to a report server. Report Designer is integrated with Microsoft Visual Studio .NET 2003.

Report Manager A Web-based report access and management tool that is included with Microsoft SQL Server Reporting Services. It is used for the management of reports, resources, and the report server.

Shared Data Source Used by Reporting Services to define reusable database connection information that can be accessed by multiple reports.

Subreport In Reporting Services, an item embedded in a report that points to another report. The child report may run as part of the full report, or it can be a full report.

1	searchwebservices.techtarget.com, "A searchwebservices definition"
2	Microsoft Reporting Services Books Online, "Introducing Reporting Services"