The tutorial shows with sufficient details how you may author a report based on an Ad hoc query deriving data from multiple data sources.
MySQL Server
Intermediate
In authoring a report you would normally connect to a datasource and create a dataset. The dataset can be based on a tables 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 and base the report on the dataset.
In this tutorial tables from two databases, a MySQL Server database 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 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.
Figure 1 shows an expanded view of the TestNorthwind database in the SQL Server Management Studio. The TestNorthwind database on the SQL Server is a copy of the Northwind database that you would find in the sample database folder in MS Access. IThe tables on TestNorthwind therefore have the same structure as those on the MS Access sample database Northwind.
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.
The SQL Statement copied from the SQL View of this query is shown in the next listing.
SELECT [dbo_Order Details].UnitPrice, [dbo_Order Details].Quantity,
dbo_Orders.ShippedDate, dbo_Orders.ShipName, dbo_Orders.ShipCity, [public_ADO NET
Destination].ProductName
FROM (([dbo_Order Details] INNER JOIN dbo_Orders ON [dbo_Order Details].OrderID =
dbo_Orders.OrderID) INNER JOIN dbo_Products ON [dbo_Order Details].ProductID =
dbo_Products.ProductID) INNER JOIN [public_ADO NET Destination] ON
dbo_Products.ProductID = [public_ADO NET Destination].ProductID;
A partial view of the result of running this query is shown in Figure 3.
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.
sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
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.
The retrieved data is already from two different servers, the EnterpriseDB and the SQL Server 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 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.
Select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\My
Documents\DistQry.mdb';'Admin';'',ViewDistQry)
Of course we could further modify this query by making a join with one of the tables on the SQL Server. For the purposes of demonstration we will be using the following query as the basis for authoring the report.
The query with the join is shown in the next listing.
USE TestNorthwind
GO
SELECT c.ProductName, o.ShippedDate,o.ShipName,o.ShipCity, o.ShippedDate
FROM TestNorthwind.dbo.Products AS c
INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\My Documents\DistQry.mdb';
'Admin';'',ViewDistQry)
as o
on c.ProductName=o.ProductName
Where o.ProductName='Ipoh Coffee'
As previously mentioned report builder 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 can be started from its shortcut in Start | All Programs| SQL Server Report Builder | Report Builder. It is assumed that the Report Services has already started. This brings up the UI of the Report Builder as shown in the next figure.
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.
In the Data Source Properties window [change the default name of data source to be different from DataSource1] that gets displayed; select the 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, 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 UI as shown in the next figure.
In the next step you click on the Dataset drop-down item. 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.
Click the QueryDesigner…button. The query gets copied to the interface.
When you click OK on the Dataset properties window you may get an error. When you click OK you will get the window shown in the next figure where you can delete the duplicate.
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 UI.
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.
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'.