Creating Data Access Pages with Charts using Office Web Components

About Microsoft Office Web Components

OWC are a set of Microsoft COM Controls for spreadsheet, chart and database publishing. These components have their own set of object models: ChartSpace, Data Source Control, PivotTable List, and Spreadsheet. Depending on the version of Office, you may have two different executables for this program. With a licensed version of Office XP your machine may have OWC10.exe. On the other hand with Office 2003 you may have a slightly larger file (22.56MB), OWC11.exe. The version on the Windows XP (Media Center Edition) machine used in this tutorial is OWC11.exe and the tutorial uses MS Access 2003.

If you have no license for Office XP or Office 2003 you can view web pages which use these controls by downloading the executable but you cannot interact with the controls (view only mode). These components can also be added to your ASP.NET applications by referencing them in the Add References' window of COM libraries as shown. For more detailed information on licensing, please search MSDN.

About this tutorial

This is a very basic tutorial targeted to readers who want to use these controls to create charts that can be placed in their data access pages. The tutorial shows how one can create this chart starting from scratch after instantiating an empty data access page in their database. To keep the details simple a couple of tables have been created in an MS Access database called Charts.mdb. The following three tables were created to illustrate the use of the OWC chart control.

The PrincetonTemp tables lists monthly temperatures and the record high temperatures for a New Jersey town (record high is fictitious). 

The table Speedy shows time vs. distance covered by a projectile.

 Portfolio is a listing of a stock held by a person. It also lists each stock as a percentage of the total.

Monthly temperature chart for Princeton

Create an empty data access page by clicking on Create data access page in Design View or using the New toolbar menu item. This opens up the New Data Access Page window where you highlight the Design View and click OK. You may get a version related warning as shown.

When you click OK, as seen in the above picture, you should see this design view of the data access page. The central rectangular area is where you are going to place the chart component.

Now click on the chart component marked in the above picture and place it on the design view of the data access page as shown. The Microsoft OWC control has been resized to fill the design area as shown.

You may right click and look at the object properties if you need to make changes. Here most default options are taken.

Now click on the the OWC control so that its handles are visible and right click this component. You will get a context sensitive menu as shown.

Now click on the Data... which opens up the next window where you need to choose a source of data. You have three options to choose from.

If you choose the first option of typing the value into a datasheet, then step 2 becomes active and you will be guided. In the present case, the third option is chosen. This changes the previous window to the one shown in the next picture. It uses the MSODSC.  Now click on the Data Details tab and choose the PrincetonTemp table from the drop-down. This procedure of choosing data will be the same for the other two tables as well.

Monthly temperature chart for Princeton continued

When you choose the table, the field list corresponding to that table is added to accompany the page as shown. You may also place a check mark against  Plot detail records as shown.

Another change that occurs is that the OWC shows some drop-down areas for the various fields. These are place holders for data that you drag and drop onto them from the field list.

Click on the Month in the field list (if it is not visible just right click on the OWC and the list should pop up), then click on the Drop Categories Fields Here at the bottom of the OWC as shown. Also drag and drop the Temperature and RecHigh fields into the Drop Data Fields Here place holder location at the top.

This changes the chart as shown in the next picture. You may also drop these fields inside the data area where the chart appears. If you need to remove them you may highlight so that it sprouts handles and then use the cut submenu from the Edit menu item.

Now click on the OWC and from the drop-down menu click on the Commands and Options... menu item. This brings up the Commands and Options window as shown, with several tabs.

In the General tab you can add multiple charts, as well as legends and titles.

In the Borders/Fill tab you can make choices to make the chart very "jazzy" or make it look very conventional as shown. You can fill the chart with your choice of colors or choose a preset color scheme.

The next tab, Data Details, is the same we saw earlier, and the screen shot is skipped. The Type tab allows you to choose the type of graph or chart. The one chosen here is the column graph.

In the next tab, Series Groups, you group the series and also add the axes. For example, the scale of the temperature can be added to the graph. In this case, it was added to the left of the chart as shown.

The next tab, Show/Hide, shows the default options of whether a certain feature is showing or hidden.

Now you can view how the page looks by clicking on the View --> Page View from the main menu.

In this view if you click on the Month drop-down, you may deselect certain months if you want and the interactive chart would show views for only the active months as shown (February, November, and October are missing). This is certainly a nice feature when you want to selectively filter data.

You may now save this page from the file menu. This page in this tutorial was saved as Prctntemp.htm in the default directory C:Documents and Settingscomputer userMy Documents Before the page is saved it warns you about the datasource location information not being an UNC path.

After making a few more minor changes to the formatting the page appears as shown in the next picture when opened in IE 7.0.

Creating an X-Y graph

Most of the details of configuring the X-Y graph are as for the previous case. Here the final field list is shown in the next picture.

Into the Drop Category Fields Here location drag and drop the Time field. Into the Drop Data Fields Here drag and drop the Distance field. When you go over the location a tool-tip pops up indicating that the Y data fields may be dropped as shown.

After dropping the Y field data from field list, the X Data area is revealed. Once again drag and drop the Time to the X Data area.

This completes the graphing of the table values in Speedy.

Creating a Pie Chart

The next picture shows the final few steps of creating the Pie chart. The source for this data is the Portfolio table. The type of chart chosen is the Pie chart type. When you make these selections, the design view of the data access page changes as shown.

Now right click on the OWC and bring up the field List. Drag and drop the Stock to the Drop Category... place holder and the Percent field list to just below the Drop Filter Fields Here place holder as shown in the next picture.

.

This page may also be saved to the same default directory as before.

Summary

Creating data access pages which display charts of various types is quite easy. The pages are interactive for licensed users. The steps in charting are fairly straightforward except that the drag and drop operation may sometimes lead to confusion. The online help is excellent as it is very useful to clarify most of your problems. The Microsoft Office Web site is another location where you can find help. The tutorial considered data in the local mdb file. It is just as easy to chart data on a connected data source.

blog comments powered by Disqus