Introduction

In my previous article on this site I described creating an EnterpriseDB Linked server on SQL Server 2008. In this article the idea is carried forward to create a SQL Server 2008 report based on a distributed Ad hoc query using data on the SQL Server 2008 and a linked MS Access 2003 database. The article describes step by step the procedure to create such a report using Report Builder 2.0. The idea is generic and can be adopted for other scenarios as well.

Report Builder 2.0 is an excellent application to author, maintain and distribute reports. While the Report Server hosts the reports created in SQL Server 2008, the Report Manager provides the front end support for maintenance, scheduling and delivering reports in a number of formats. The report builder not only deploys reports to the report server but also provides an extremely flexible UI for authoring variety of reports including Ad hoc reports from report models as well as from Analysis Services data. Two free chapters on Report Builder 2.0 from the author’s book may be downloaded from the publisher’s site.

Overview

In authoring a report you would normally connect to a datasource and create a dataset (different from the ADO.NET dataset). The dataset can be based on a table(s) or queries. The columns in the dataset populate the report. What if the data is on different servers? One solution would be to create a distributed query that can be run against the two servers and derive a dataset (in the sense of Reporting Builder data related elements) and base the report on the dataset. Microsoft’s distributed query architecture supports two methods of calling up heterogeneous databases, one using a linked server and the other through Ad hoc queries.

In this article tables from two databases, a SQL Server 2008 database (it is in fact a copied over database from MS Access) and the other a database on EnterpriseDB described in the previous article will be used to provide linked tables for a MS Access database. A query created in MS Access against these linked tables will be used for creating the report. The MS Access database DistQry.mdb is shown in Figure 1.

Figure 1: MS Access (DistQry.mdb) application with linked tables

The linked table public_ADO NET Destination is on an EnterpriseDB server and the dbo.Order Details, dbo_Orders and dbo_products are on the SQL Server 2008. Details of this database and its use are described in a future article.

Figure 2 shows an expanded view of the TestNorthwind database in the Microsoft SQL Server Management Studio. The TestNorthwind database on the SQL Server 2008 is a copy of the Northwind database that you would find in the sample database folder in MS Access 2003. It is also found in several other Microsoft database products. The tables on TestNorthwind therefore have the same structure as those on the MS Access sample database Northwind.

Figure 2: TestNorthwind database on SQL Server 2008

Creating a query in MS Access

The query we will be using in creating the report will be created in the MS Access database, DistQry. This is easily created starting from the design UI as shown.

Figure 3: Query against the linked tables

The SQL Statement copied from the SQL View of this query is shown in the next listing.

Listing 1: SQL of the query (ViewDistQry )

A partial view of the result of running this query is shown in Figure 4.

Figure 4: Query results

Creating the Ad hoc Query

The data behind the report is provided by an Ad hoc query. By default, processing of Ad hoc queries is not turned on. You need to run the following statements shown in the next listing to turn on the Ad hoc query feature in SQL Server 2008.

Listing 2: Turn on Ad hoc query processing

We will be using a Transact-SQL Statement in this article for creating the Ad hoc query, a recommended practice if the datasource is not called frequently. A linked server has to be created if it has to be more frequent. In the case of infrequent use of the connection such as in this case, the Openrowset() method is quite adequate for running the query against the MS Access database in the SQL Server Management Studio.

Figure 5: Adhoc query using the Openrowset () method

The retrieved data is already from two different servers, the EnterpriseDB and the SQL Server 2008 providing linked tables to the MS Access database. The arguments for the Openrowset() are the provider [Microsoft.Jet.OLEDB.4.0]; the database source file location [C:\Documents and Settings\...\DistQry]; the default authentication; the <empty> password and the target object on the database[table or query]. The Microsoft.Jet.OLEDB.4.0 is one of the many OLE DB providers available for linked servers on SQL Server 2008.

Listing 3: Query with the Openrowset () method

Of course we could further modify this query by making a join with one of the tables on the SQL Server 2008. For the purposes of demonstration we will be using the following query as the basis for authoring the report.

Figure 6: Adhoc query with a join

The query with the join is shown in the next listing.

Listing 4: Adhoc query with a join

Authoring the Report

As previously mentioned Report Builder 2.0 will be used. If the Reporting Services is properly configured authored reports can be deployed to the report builder, or they can be persisted to files on the hard drive. The Report Builder 2.0 can be started from its shortcut in Start | All Programs| Microsoft SQL Server 2008 Report Builder 2.0 | Report Builder 2.0. It is assumed that the Report Services has already started. This brings up the UI of the Report Builder 2.0 as shown in the next figure.

Figure 7: Report Builder 2.0 (displaying the New drop-down list)

As shown in the above figure, the steps in authoring are clearly shown in the drop-down. First we create a datasource; derive a dataset from the datasource; define a parameter; and add images. Click on datasource and connect to SQL Server 2008 (the details are discussed in the free downloads mentioned earlier) as shown in the next figure.

In the Data Source Properties window [change the default name of data source to be different from DataSource1] that gets displayed; select the Microsoft SQL Server as the selected connection type. Choose to use a connection that is embedded in the report. After this, click on the Build button to bring up the Connection Properties window as shown. Accept the default, Microsoft SQLServer (SqlClient). Browse and locate your SQL Server. Provide the authentication information. After this you will be able to verify the connection by hitting the Test Connection button as shown.

In the present example the default DataSource 1 was not changed. If you are authoring a large number of reports it is recommended that you use a name for the datasource to associate with a report. When you close the Data Source Properties window a DataSource1 will be added to the Report Builder 2.0 UI as shown in the next figure.

Figure 9: Datasource is created

In the next step you click on the Dataset drop-down item seen earlier in Figure 7. This will bring up the DataSet Properties window as shown in the next figure. In the empty space for the field Query: insert the Transact_SQL query from Listing 4 as shown.

Figure 10: Query behind the DataSet1

Click the QueryDesigner…button. The query gets copied to the interface shown in the next figure. The result of running the query after hitting the symbol (!) is also shown in the figure.

Figure 11: Report Builder Query Designer

When you click OK on the Dataset properties window you may get an error shown in the next figure.

Figure 12: Error Message

When you click OK you will get the window shown in the next figure where you can delete the duplicate.

Figure 13: Modifying the output

After removing one of the ‘ShippedDate’ from the fields click OK. This will add the dataset, Dataset1 (the default was not changed) to the Report Builder 2.0 UI.

Report Layout

Remove the shortcuts on the report design interface. Click the main menu Insert | Table (icon) | Insert Table. Bring the cursor to the design area and click. This drops a rudimentary table structure with three columns and two rows. Drag the fields from the dataset (Dataset1 in the left hand side) and drop them on to the table columns as shown. The figure shows only for two columns but you can choose as many or as few columns as you wish.

Figure 14: Layout and populating the report fields

The completed report when processed by hitting the Run button displays the report as shown in the next figure. Note that no formatting has been applied except that the display format of ‘ShippedDate’ has been modified using the formatting feature in the ‘ribbon’.

Figure 15: Report from the Adhoc Query

Summary

The article shows with sufficient details how you may author a report using the Report Builder 2.0 based on an Ad hoc query deriving data from multiple data sources.

About Jayaram Krishnaswamy

Sorry, no bio is available This author has published 7 articles on DotNetSlackers. View other articles or the complete profile here.