Click here to Skip to main content
Click here to Skip to main content

On Importing an Access Parametric Report into SQL 2000 Reporting Services

, 2 Sep 2004
Rate this:
Please Sign up or sign in to vote.
Describes importing of an Access 2000 parametric report into SQL 2000 Reporting Services.

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].

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

mysorian
Technical Writer Hodentek
United States United States
Worked in the area of electrical discharges, high energy lasers, high voltage technology, plasma technology, lithography, thin film plastics, superconducting thin films, diamond thin films, electron accelerators, and free electron lasers for several years. Mentored/guided MS and PhD students at several universities in USA, Brazil, Australia, and India.
Reading books and photography are my hobbies.
 
Also trained workforce clients with legacy computer skills in web related technologies.
 
I recently authored a beginner level book on MS SQL Server Integration Services. Details available at the following link:
 
http://www.packtpub.com/sql-server-integration-services-visual-studio-2005/book
 
My second book was released in 2008
Learn SQL Server Reporting Services 2008
 
Get book details at the following site:
http://www.packtpub.com/learning-sql-server-2008-reporting-services/book
 
This is for anyone who is interested in Reporting Services a la Microsoft. It has over 50 hands-on exercises and covers all aspects of Reporting Services.
 
Recent new books:
 
Microsoft SQL Azure Enterprise Application Development 2010
-A Comprehensive book on SQL Azure
 
Microsoft Visual Studio LightSwitch Business Application Development 2011
A step-by-step approach that is sure to work
 
Learning SQL Server Reporting Services 2012 Packt Publishers, ISBN: 978-1-84968-992-2 , 2013
Follow on   Twitter   LinkedIn

Comments and Discussions

 
GeneralCode behind report inclusion PinmemberBOBLAiBAM!8-Aug-07 4:38 
Questionsql server Pinmembernagesh8629-Apr-07 18:19 
AnswerRe: sql server Pinmembermysorian30-Apr-07 2:17 
GeneralImport Access Reports Pinmemberarshadsa14-Dec-05 4:41 
GeneralRe: Import Access Reports Pinmembermysorian14-Dec-05 5:10 
GeneralRe: Import Access Reports Pinmemberarshadsa14-Dec-05 5:16 
GeneralRe: Import Access Reports Pinmembermysorian14-Dec-05 6:08 
GeneralRe: Import Access Reports Pinmemberarshadsa14-Dec-05 7:42 
GeneralRe: Import Access Reports Pinmembermysorian14-Dec-05 8:23 
GeneralCreate the RDL programmatically PinsussAnonymous29-Nov-04 6:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140821.2 | Last Updated 3 Sep 2004
Article Copyright 2004 by mysorian
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid