Integrating Microsoft Access DB, SQL Reporting Services and Visual Studio .NET

Introduction

In this article I would like to demonstrate how we can leverage the SQL Reporting Services feature on to a Microsoft Access database using Visual Studio .NET as IDE. Let me initiate my discussion with a brief introduction to the SQL Reporting Services and its classical features.

SQL Server Reporting Services:

Microsoft has added reporting capabilities to SQL Server 2000. With the rollout of SQL Server 2000 Reporting Services, we can now generate reports from SQL Server, Access, Oracle, Open Database Connectivity (ODBC) and OLE DB data sources. Microsoft SQL Server 2000 Reporting Services is designed with a modular, distributed architecture to help achieve both scalability and flexibility. Processing is distributed across multiple components that can be extended and integrated into custom solutions.

• Server based reporting engines.
• Create reports with tables, graphs with data extracted from the database.
• Can contain data from relational and/or multidimensional data sources.
• Reports are viewed over the web.

During report processing, a report definition is retrieved from the Report Server database and used with data from the data source to create a report. Report processing begins with a published report definition. A report definition contains one or more queries, layout information, and code or expressions. Report and data processing are combined to create a dataset with layout information in an intermediate format that can be saved for fast retrieval, or directed to a rendering extension that processes it into a format that the user can view. After processing is completed, reports are compiled as a common language runtime (CLR) assembly and executed on the Report Server.

When a report is accessed, either on-demand or as a result of a subscription, the Report Server decides whether to generate the report from scratch or use a cached snapshot. Because report rendering is separate from the initial processing of the report data, the same report may be rendered in different formats through the use of rendering extensions. Reporting Services includes the following rendering extensions:

• HTML
• Microsoft Excel
• Image / TIFF
• PDF
• Comma-separated variables
• XML

Delivery extensions are responsible for delivering rendered reports on a schedule or other event to various locations. Reporting Services includes the following delivery extensions like e-mail and file system.

I would like to make use of the following examples to elucidate the integration process.

Example1- Integration of SQL Reporting Services and Microsoft VS.NET:

The below example provides a simple walk-through on creating and displaying a report in an ASP.NET Web page using Reporting Services. I assume that you have already downloaded and installed Reporting Services and are using Visual Studio .NET 2003 as your IDE.

1. Project Creation - In order to create a Reporting Services project, select Report Project Wizard template in Business Intelligence Project using Visual Studio .NET 2003.

   

   Figure 1

2. Datasource & SQL Query: Next we have to create a data source and SQL statement for the report to be generated.

   

   Figure 2

3. Report Type - Tabular format type is a basic row-by-column report presentation.
4. Table Design - The Report Wizard design option has some great options for rolling up, grouping, and summarizing your data.
5. Table Style � Available table style options are Bold, Casual, Compact, Corporate and Plain.
6. Report Server � We have to specify the Report Server details to which our report will be deployed.
7. Summary Screen - The final screen of the Report Wizard displays the summary of the above feed in information.

   

   Figure 3

The report designer has three tabs namely:

• Preview Tab: It is used to preview the report.
• Data Tab: It offers options to modify the datasource and SQL query.
• Layout Tab: It helps to fix up the appearance of the report (colors, fonts, grid lines, etc.).

Figure 4

Example 2 - Integration of Reporting Services, Access DB and Microsoft VS.NET:

The second example provides a quick walk-through on displaying an Access report in an ASP.NET Web page using Reporting Services. It assumes that you have already reviewed the above example on VS.NET and Reporting Services integration.

1. Let�s assume that we have already created a �Supplier� Access report which is populated using the �Supplier� table. In figure 5, you would see the Access report.

   

   Figure 5

2. Preview of Access report in Access environment:

   

   Figure 6

3. Now open the sample report project created using the above example (example1) illustration. In order to import the Access reports, click on the Project tab and navigate to Import Reports option and select Microsoft Access database.

   

   Figure 7

4. On selecting the Access option, it will allow the user to select the Access database from which he intends to generate the report.

   

   Figure 8

5. Now open the Supplier report in Visual Studio .NET. You will observe in the Build section - �Report Suppliers imported� message displayed. And also on the Solution Explorer, you will find that Supplier.rdl is added to the report project.

   

   Figure 9

6. On clicking the DataSet option on the �Data� tab, you will find that the following information about the Data Source, Command Type and Query String are displayed to the user:

   

   Figure 10

7. Using the Preview Tab, you will be able to view the Access report.

   

   Figure 11

Conclusion

Hope this article would have provided insight on how to leverage the SQL Reporting Services capability to Access reports, using the Visual Studio .NET IDE.