On Importing an Access Parametric Report into SQL 2000 Reporting Services

• Download demo project - 105 Kb

Introduction

SQL Server 2000 Reporting Services allow importing Reports created in Microsoft Access. This article discusses importing of a parametric report created in MS Access 2000 into a Reporting Services *.rdl format, using Visual Studio 2003.

Details of the Access Report

A RepSVC.mdb file was created into which only the 'Orders' table from the Northwind database was imported. The fields were cleared of any look-ups or Joins to other fields in the Northwind database. The design view of this table is shown here:

Query Designer in Access was used to create a 'parametric query' to test how easily a report goes over into the SQL Server 2000 Reporting Services. The 'SQL' statement for this query is shown here. The query requires an input parameter, the name of the country, to produce the query results.

SELECT Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, 
Orders.ShipName, Orders.ShipCity, Orders.ShipCountry
FROM Orders
WHERE (((Orders.ShipCountry)=[Country?]));

The Report wizard was used to produce a report in MS Access for 'Denmark', and this is shown in the next screenshot:

Details of Importing into Visual Studio 2003

Create a report project, AccessProject in Visual Studio 2003, following the example in ASP.NET and SQL 2000 Reporting Services. This comes with a Report folder which is empty. From the Project menu item drop down, click on Import as shown:

This will automatically point to 'MS Access', and opens up a browsing of the 'My Documents' [default for MDB file]. Point to the RepSvc.mdb created earlier, which already has a report called 'Which Country'. This immediately creates a 'WhichCountry.rdl' in the Reports folder as shown:

In creating the reprot file 'WhichCountry.rdl', the Reporting Service has extracted a 'DataSet' with all the fields specified in the original query, as shown:

The next screenshot shows the 'Layout' of the report which resembles the Access Report, as shown:

If you right click at the spot shown in the above screen shot, you can see all the details of the Report Design. You can go item-by-item and make changes if you like. The next screen shot shows the 'Report parameters' window, wherein you can enter your parameters:

While entries can be made to this 'Report Parameters' window, the ellipsis by the side of the DataSet1 drop-down can be used to input the parameter as shown.

The parameter shipcountry='Denmark' will be used, and therefore the input in the 'Parameter' tab of this window is as shown here:

After inserting the parameter, the report can be run after building it, by right clicking the report WhichCountry.rdl file, and clicking Run. A part of the report generated is as shown here:

Interactive Report

In the previous section, the parameter value pair 'ShipCountry=Denmark' was hard coded into the Dataset properties. However, there is yet another way of inserting the parameter value in an easier manner. As in the previous case, the Dataset properties is set with the placeholder variable as shown in this screen shot:

In the preview tab of the report shown here, the check on the 'Null" is cleared which opens up the text field for entering the value of the parameter. If the check is left in place, the report will return zero records. The slide after next shows, some records for 'Country? = France'.

Printing the Report

The 'Print' button in this report's Preview is 'grayed' out. It is not clear whether this is so in full version [this happens to be a trial version]. However, by clicking the 'file save' icon in the above figure as shown, you are presented with a couple of alternatives in saving this file. From here, it is easy to print in Adobe format or TIFF format depending on the resources available.

Conclusions

Although what is presented is the import of a simple parametric report, it appears that the SQL 2000 Reporting Services does an extremely good job of importing the file. Whether this is backward compatible with the previous versions of Access remains to be seen. The conversion appears instantaneous, this is perhaps the example's data set is very small. It was also observed that when the import is invoked, all the reports in the database are automatically converted at the same time into *.rdl files with the same report name [x.rpt goes over to x.rdl].