Introduction

In SQL Server Reporting Services 2008 Microsoft came up with the idea of creating ad hoc queries that allowed even non-SQL professionals to make business decisions using backend data based on report models. Pivot tables and pivot charts were also created for helping to make business decisions like comparing revenue in different quarters; in different years; among different product models etc. Creating and viewing data using these constructs is easily done even in MS Excel. These constructs also use the information to create OLAP cubes that can later be reviewed in SQL Server Analysis services. With Office 2007 these constructs are fortified using Excel Services.

The content of the article is developed on a Windows XP Computer with SP3, Microsoft Access2003 and SQL Server 2008 Enterprise (Evaluation edition).

The article is organized as follows:

• Creating an ODBC DSN for SQL Server 2008
• Invoking the Pivot Table and Pivot Chart Wizard in MS Excel 2003
• Selecting Data with Query Wizard
• Visualizing data

Creating an ODBC DSN for SQL Server 2008

Excel accesses data on the SQL Server 2008 using an ODBC connection. The very first thing you need is a ODBC DSN. You can create the ODBC connection to SQL Server 2008 by tracing the following steps:

1. Bring up windows from Start | Control Panel | Administrative Services | Data Source (ODBC). In here you will be creating a User DSN.

2. In the User DSN tab click the Add...button.

3. In the Create a New Data Source window scroll down and choose SQL Server and click Finish.

4. The wizard quizzes you for the various fields you need to fill up in the Create a New Data Source to SQL Server. For the name field you need to provide a name of your choice. This is going to be the DSN(Herein PivotSQL2k8). Also provide a description for the data source although it can be blank (default). The drop-down for the Server should be able to bring up a list of SQL Servers. If it does not correctly show all the servers, you can type the name of the server using the syntax <machine (computer) name\SQL Server Instance name>.

Figure 1: ODBC wizard choosing the server

5. Click "Next" which takes you to the page where you need to provide the authentication information. If your SQL Server is configured for Windows authentication, as in the case of the server used for this article, you can accept the default option. If it is configured for SQL Server authentication, you need to provide the LoginID and Password. With Windows authentication, uncheck the check box for obtaining the default settings and click "Next".

6. In the next page use TestNorthwind for the default database and accept other defaults as shown.

Figure 2: Defaut database for the connection

7. In the encryption and language settings page place a check mark for the SQL Server system messages to English using the drop-down handle (if necessary) and accept other defaults. Click Finish.

8. The summary page of the wizard shows up. You can also test the connectivity by hitting the Test Data Source button, as shown in the following figure.

Figure 3: Summary of the DSN

Click OK in the test page as well as in the ODBC wizard page. You will be back in the Wizard page and your new DSN PivotSQL28k8 appears in the User DSN list. Click OK and close the wizard.

Invoking the Pivot Table and Pivot Chart Wizard in MS Excel 2003

Click "Open MS Excel 2003" from its shortcut. In the drop-down of the Data menu item click on PivotTable and PivotChart report, as shown in the next figure.

Figure 4: Accessing the PivotTable and PivotChart wizard in MS Excel

This brings up the PivotTable and PivotChart Wizard's step1. Change the option to External data source. There are two options to choose from, for the kind of report. You can choose just the PivotTable or the PivotTable report together with the PivotChart. Here the combo option is chosen.

Figure 5: Pivot Table and PivotChart Wizard

Click Next. The Step 2 of the wizard shows up.

Figure 6: Getting the Data

Click the Get Data button. The Choose Data Source button with three tabs, Databases, Queries and OLAP Cubes gets displayed. Since we created the data source we just need to look it up in the presented list.

Figure 7: Choosing the DSN

Selecting Data with Query Wizard

Highlight PivotSQL2k8* and click OK. This opens the Query Wizard. Scroll down in the "Available tables and columns:" and choose "Product Sales for 1997". Then click the > arrow button to transfer it to the "Columns in your query".

Figure 8: Choosing the table or view

You can choose a column in the query and hit the Preview Now button to see the data. You can hit the Options button to restrict your objects to any of Tables, Views, System Tables and Synonyms. Click Next. In the Filter Data page of the Query Wizard you can filter the data you want.

Figure 9: Invoking the Query wizard

We will skip this page by making no changes. Click Next. Skip the Sort Order page (not shown here) that comes up as well. The reason for this is to make the filtering in the Pivot table later. Click Next. Here there are several options to choose what you want with the outcome of your query. This time accept the option (default) to "Return Data to Microsoft Office Excel".

Figure 10: Where and how to persist retrieved data

Click Finish. You are back to the PivotTable and PivotChart Wizard Finish. The Excel application gets displayed with a design area docked to Sheet1; a PivotTableFieldList (floating) and a PivotTable (floating) menu bar. The design area can accept items from the field list by dragging and dropping them into the designated drop areas.

Figure 11: Pivot table design

Visualizing data

Drag Shipped Quarter from PivotTable field List to "Drop Column Fields Here". Drag and drop ProductName to "Drop Row Fields Here". So far only two fields have been used and they are highlighted in the field list.

Figure 12: Configuring the Pivot Table (partial)

In a similar manner drag and drop the "CategoryName" from the list to the "Drop Page Fields Here" and finally the "ProductSales" to the "Drop Data Items here" to complete the design of the Pivot Table.

Figure 13: Fully configured Pivot Table

Observe that this table is showing the sales for all Categories and for all Products. You may just see what this table would like for the category "Beverages" by hitting on the drop-down handle for CategoryName, choosing 'Beverages' and clicking the OK button.

Figure 14: Filtering for one field

This will filter the whole data for the CategoryName="Beverages" as shown in the next figure.

Figure 15: Filtered by a chosen category

You can drill further down and see among the beverages the product sales of 'Ipoh Coffee' and 'Chai', for example. In this case you can bring up the ProductName list as shown in the next figure.

Figure 16: Filtering by Product Name

Clear the checkbox for all items [uncheck (Show All)] and then check only 'Chai' and 'Ipoh Coffee'. Click OK. You can now see the quarterly sales for these two products only.

Figure 17: Filtered by Category Name as well as two products

Now click on the Chart1 tab at the left of Sheet1 and you will see a stacked report for these two products with the 4 quarters as the four series in the chart. If the default chart size cannot be enlarged by the handles then just copy the chart and place it in another Sheet. This is what was done to get the picture shown in the figure.

Figure 18: Pivot Chart Corresponding to the above Pivot Table

The relation between the PivotTable and the PivotChart is bidirectional and any changes you make in the PivotTable are immediately reflected in the Pivot Chart, and vice versa.

Summary

The built-in support for PivotTable and PivotChart in MS Excel 2003 is a very easy to use tool that can be used to visualize data stored on the backend database. The slicing and dicing of data is quite intuitive and requires very little skills. As this tool uses the ODBC datasource, even third part databases can be visualized.

About Jayaram Krishnaswamy

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