Adding Bound Office Web Controls to DAP

The Layout Wizard offers options to add a Spreadsheet, PivotTable, or ChartControl when you drag a table or query node from the Field List. The following three sections describe how to create pages that display the graphic OWCs.

Note

Users who install the OWC 11 runtime version are limited to a subset of the features of the three components described in the following sections. If you're a VBScript expert, you can write scripts to perform operations that aren't accessible by unlicensed users. To learn more about scripting OWC 11 objects, start at http://msdn.microsoft.com/library/officedev/offstrt/aboutoffice.htm, click Show TOC, and look for Office 11 Developer Documentation.

Displaying a Table or Query Result Set in an Office Spreadsheet Control

The Office Spreadsheet control offers an alternative to tabular text boxes for interacting with table or query data in a page. Unlike the OWC 9 version of the Spreadsheet control, Access 2003's OWC 11 version binds to a table or query. The spreadsheet control also lets you display calculated data based on numeric and date columns, but this feature (and the AutoSum feature) isn't available when the sheet is bound. Another useful feature is the capability to export the spreadsheet data as an Excel 2003 worksheet that you can save to a file and manipulate offline.

Note

The Spreadsheet control's AutoFilter feature is based on the PivotTable and PivotChart approach, so users are likely to find the Spreadsheet filter more intuitive than the filter-by-selection method of the navigation control. Sorting the sheet, however, requires instructions for users who aren't familiar with Excel's sorting methods.

To add a Spreadsheet control to a page, using the Quarterly Orders by Product crosstab query as a read-only example, do the following:

1. With Pages active in the Database window, double-click the Create a Data Access Page in Design View item. If you didn't mark the Don't Show This Warning Again check box in a previous example, click OK to open the empty page.

2. Drag the Quarterly Orders by Product query from the Field list to the empty page section. In the Layout Wizard's dialog, select Office Spreadsheet, and click OK to add the control to the page.

3. Type a title for the page. To conserve vertical space on the page, set the font size to 18 points and delete the <H1> section below the title.

4. Expand the width and depth of the section and the control to display all seven columns and 12 or more rows. The Spreadsheet control uses field names not Caption values in the first row of the sheet (see Figure 24.42).

   Figure 24.42. You must resize the default section and Spreadsheet control width and height to display all columns from the query and a reasonable number of rows.

   

5. Change to Page view to test the bound Spreadsheet control's feature set. You can't add new columns to a bound Spreadsheet control, and changes that you make to cell values don't propagate to the underlying tables because crosstab queries can't be updated.

6. To format the order values as currency, select the Qtr 1...Qtr 4 columns, click the Commands and Options button (above the center of the OrderYear column) to open the dialog's Format page, and select Currency in the Number Format list (see Figure 24.43). Return to Design view and adjust the width of the spreadsheet control to accommodate the wider currency columns.

   Figure 24.43. The Spreadsheet control doesn't recognize the Jet Currency format of the Qtr... columns, so you must format them manually.

   

7. To test the AutoFilter feature, return to Page view and click the AutoFilter button to add drop-down buttons to each column. Open the list for a column CustomerID, for this example clear the (Show All) check box, and select one or more CustomerID values on which to filter (see Figure 24.44). The capability to filter on multiple-column values is an enhancement to the Filter by Selection feature of tabular pages.

   Figure 24.44. The AutoFilter feature lets you multiselect filter values in one or more columns.

   

8. Click OK to apply the filter, which results in the sheet shown in Figure 24.45. When a filter is applied, the AutoFilter button is in the depressed state, column numbers aren't consecutive, and the color of the column numbers is blue.

   Figure 24.45. The AutoFilter on state is evident from the nonconsecutive column numbers and the on (depressed) state of the AutoFilter button.

   

9. Click the AutoFilter button to remove the filter.

10. To sort the sheet by one of the columns, click the arrow to the right of the Sort button to open the column selection list. Moving the mouse pointer onto the context menu selects the entire sheet (see Figure 24.46).

    Figure 24.46. Opening the drop-down Sort button automatically selects all cells of the sheet below the field header row. Choosing a column sorts the sheet.

    

    Tip

    Don't select a column and click Sort Ascending or Sort Descending. Doing this sorts the column values independently of the other columns. Fortunately, clicking Undo restores the data to its original order.

11. Click the Undo button to return the sheet its original unsorted state.

You can't use the AutoSum feature or calculate new values in the bound sheet, but you can copy the sheet to a new unbound sheet that lets you add columns and rows. By default, the Spreadsheet control has three sheets, but you can add or delete sheets in the Worksheet page of the Commands and Options dialog. (The third Microsoft mystery of this chapter is why this dialog isn't named "Office Spreadsheet Properties.")

Tip

If you want to save your Spreadsheet page with a name other than Page1, do it now. When you save a page, only the bound sheet is preserved. The unbound sheet that you add in the following procedure is lost when you save the page.

To copy Sheet1 to Sheet2 and AutoSum the columns and rows, do this:

1. Press Ctrl+ A to select all rows and columns of Sheet1, and press Ctrl+C to copy the cells to the Clipboard.

2. Click the Sheet1 tab and select Sheet2 from the pop-up list.

3. With cell A1 selected, press Ctrl+V to copy Sheet1 to Sheet2.

4. Select the columns and rows to total D2:G948, for this example and click the AutoSum button, which creates column but not row totals (see Figure 24.47).

   Figure 24.47. The AutoSum feature totals selected columns but not rows.

   

5. To crossfoot the sheet with row totals, type the formula to sum the individual row values =SUM(D2:G2), for this example in another cell of the row (H2), and copy the formula to the remaining rows.

Unfortunately, AutoSum values don't update when you apply AutoFilter, which limits the usefulness of the AutoFilter feature. You can add command buttons or other controls to the form and write VBScript event handlers to automate Spreadsheet operations. The object model of the Spreadsheet control isn't simple, and writing VBScript to manage sheets is more difficult than creating VBA procedures to manipulate PivotTables and PivotCharts of Access forms.

There's no Save button on the Spreadsheet's toolbar, so you can't save Sheet2 and reopen it in the user interface. The only direct method for persisting the data is to export the sheets to an Excel workbook. When you click the Export to Microsoft Excel button, Excel opens a read-only OWCSheetNumber.xml file with an unbound version of Sheet1 and any other unbound sheets that you added.

You can save a read-write version of the exported file in XML Spreadsheet (*.xml) format or in any other format supported by Excel 2003. Set the XMLURL property value of the Spreadsheet control to the file name, QuarterlyOrdersSS.xml for this example. When you specify an XMLURL value, the control loads data from the XML file instead of the table or query. The XML file for the workbook with the two worksheets created in the preceding example, QuarterlyOrders.xml has a size of almost 1MB; the .xls version is 226KB. It takes a second or two to load the Spreadsheet control with a large XML file.

Creating a PivotTable Page

Dragging a table or query node from the Field List to an empty section of a page and selecting PivotTable in the Layout Wizard dialog adds a bound but featureless PivotTable to the section. Unlike Access PivotTable view or PivotTables that you add to forms, the toolbar and drop zones are missing from the default PivotTable control when you add it to a page.

Note

Chapter 12, "Working with PivotTable and PivotChart Views," and Chapter 18, "Adding Graphs, PivotCharts, and PivotTables," explain how to manipulate PivotTable data. The examples in this section and the following section on PivotCharts use the data from the qry1997OrdersByCountryPT sample query that you created in Chapter 12. This query is included in the Data24.mdb database in the \Seua11\Chaptr24 folder of the accompanying CD-ROM. Import the query to Northwind.mdb, or work from the Data24.mdb sample database.

To add a PivotTable based on the qry1997OrdersByCountryPT query, do this:

1. Double-click the Create Data Access Page in Design View item and click the Field List button if the Field List isn't open.

2. Expand the Field List's Queries node, and drag the qry1997OrdersByCountryPT query to the top left of the empty section. Select the Layout Wizard's PivotTable option, and click OK to add a bare-bones PivotTable control. Add a title to the page, reduce its font size, and delete the <H1> element to conserve vertical space.

3. Expand the size of the PivotTable to about 3x7 inches to start, right-click the PivotTable, and choose Commands and Options to open the eponymous dialog.

   Note

   The Commands and Options dialog is the full version of the Properties dialog for Access PivotTable view and PivotTables that you add to forms and reports. The Properties dialog doesn't have Protection or Data Source pages.

4. Click the Behavior tab and mark the Drop Areas and Toolbar check boxes to add these features to the control (see Figure 24.48). The Expand Indicator check box is marked by default.

   Figure 24.48. Add the drop areas and toolbar to the PivotTable control on the Behavior page of the Commands and Options dialog.

   

   Caution

   Don't mark the Auto Fit the Control check box. If you do, the height of the page expands to display all rows of the PivotTable. It isn't easy to recover by reducing the height of the section and the control in the user interface.

   Tip

   If you accidentally expand the control and its section, right-click the PivotTable, select Object Properties, and change the Format page's Height property value to 2in. Click the section header and change its Height value to 2in.

5. Select the Orders column, click the Format tab, and change the Number format to Currency. Then close the Commands and Options dialog.

6. Choose File, Save As, and type a descriptive name such as 1997 Orders by Employee and Country (Pivot Table), for this example in the Save As dialog. Click OK to open the Save As Data Access Page dialog. Type a simpler name for the page file such as 1997OrdersByCountry.htm and click Save. Click OK to acknowledge the warning message.

7. Double-click the control to activate it for layout changes, and drag the Country header to the Drop Row Fields Here drop zone. Drag the Name header to the Drop Column Fields Here drop zone, and drag the Quarter header to the drop Filter Fields Here drop zone.

8. Select the Orders header, and click the AutoCalc button and choose Sum to generate Sum of Orders rows.

9. Click the Hide Details button to display only the Sum of Orders values (see Figure 24.49).

   Figure 24.49. A diplay of the Sum of Orders values.

   

10. At this point, it's safe to allow the PivotTable to expand as necessary to display all data. Right-click the control, choose Commands and Options, click the Behavior tab, mark the Auto Fit the Control check box, and accept the default values.

    Tip

    Allowing the control to expand to accommodate detail data prevents users from needing to scroll both the browser and the control. Be sure not to click the Show Details button in Page Design view.

11. Press Ctrl+S to save your changes, and open the page in Page view or Web Page Preview. Figure 24.50 shows the page open in IE 6.0 after you click the Show Details button.

    Figure 24.50. IE 6.0 displays part of the PivotTable page with detail cells for individual orders.

    

The page persists changes that you make to the layout of the PivotTable in Page Design view, but not in Page view or Web Page Preview. Users who don't have an Office 2003 license can apply filters and hide or show details, but they can't alter the layout of the PivotTable.

Using the Chart Wizard to Bind an Office Chart to the PivotTable List

The final step in the process is to add an Office Chart control to the page and bind the chart to the PivotTable list. To generate the Chart, do the following:

1. Return to Page Design view and increase the height of Section: Unbound to about 11 inches.

   Tip

   Setting the Height property of the SectionUnbound object is easier than dragging the bottom of Section: Unbound in the page designer. Right-click the page and select Section Properties to open the Object: SectionUnbound properties sheet. In the Format list, scroll to the Height property and type the new value in inches (11in). You also can type a pixel value, such as 750px.

2. Click the Toolbox button of the Page Design toolbar, and click to select the Office Chart control.

3. Draw a small chart, and then expand the graph to occupy the remaining vertical space of Section: Unbound. Increase the width to about 7 or 8 inches.

4. Right-click the chart and choose Data to open the Data Source page of the Commands and Options dialog. Select the Data from the Following Web Page option and the default PivotTable: PivotTable0 item in the list. A clustered column chart whose data source is the Pivot Table appears behind the dialog (see Figure 24.51).

   Figure 24.51. The default chart type is a clustered column, which displays a bar for each employee's sales in each country.

   

5. Click the Chart Type button, and select the stacked column chart type, which is highlighted in Figure 24.52.

   Figure 24.52. The selected stacked column chart is more readable than the clustered column type.

   

6. Right-click an empty area of the chart, and choose Commands and Options to open the General page of the dialog with the Chart Workspace object selected. Click the Add Legend button to add a legend for the color of employees' bars (see Figure 24.53).

   Figure 24.53. Add a legend so that users can decipher the sales contribution by each employee.

   

7. Press Ctrl+S to save your changes. Open the page in Web Page Preview, and click the By Row/By Column button to display the data with employee names instead of countries on the x-axis (see Figure 24.54).

   Figure 24.54. Clicking the By Row/By Column button changes the x-axis from countries to employee names. If you want employee names to be the default, you must make the change in Page Design view.

   

Locking Down the Layout of PivotTables and PivotCharts

By default, PivotTables give users with Office 2003 licenses unlimited ability to alter the design properties of the PivotTable, except changing the data source. You can restrict user manipulation of the PivotTable by clearing one or more check boxes on the Protection page of the Commands and Options dialog. If the data source for the PivotTable is a table or an updateable query, clear the Edit Detail Data, Insert New Detail Rows check box, and Delete Detail Rows check box (see Figure 24.55). PivotTable data entry is prone to typographic errors, and unlike Access forms there's no easy method to validate updates. It's also a good idea to clear the View the Commands and Options Dialog Box in Run Mode check box to prevent users from making design changes that lead to help-desk support requests.

Figure 24.55. The PivotTable's Commands and Options dialog in Page Design view has a Protection page on which you can specify the changes that users can make to the PivotTable.

Note

The Commands and Options pages differ in Page Design and run mode. (Run mode is Page view or the page opened in IE 5.5+.) The Protection and Data Source tabs aren't visible in run mode. Changes that users make to page layout in run mode don't persist after they close the page.

PivotCharts bound to PivotTables have fewer default design options than PivotTables. You restrict user design changes on the Show/Hide page of the Commands and Options dialog of the PivotChart. The data displayed by the PivotChart depends on the PivotTable, so you can simplify the PivotChart presentation by clearing the Field Buttons/Drop Zones and Toolbar check boxes (see Figure 24.56). When you apply a filter to the PivotTable, for example, the data changes in the PivotTable and PivotChart.

Figure 24.56. Hide the PivotChart's toolbar and field buttons to prevent users from attempting to change the PivotChart layout.

Tip

Adjust the vertical position of the PivotChart below the PivotTable by dragging the height selection handle up or down, not by moving the entire PivotChart. Moving the PivotChart results in an annoying flashing of the display. After you verify the new position in Page view, adjust the bottom height selection handle to restore the original PivotChart dimension.

When you remove the toolbar and field buttons, you can improve the appearance of the chart by eliminating its border. To remove the border, click the Commands and Options dialog's Border/Fill tab, click the Color button to open a color picker menu, and click the None button. Figure 24.57 shows the PivotChart open in IE, with Qtr 1, country, and employee filters applied to the PivotTable and the border removed from the PivotChart.

Figure 24.57. Data displayed by PivotCharts bound to PivotTables reflects filters applied to the PivotTable. ToolTips are important because some filters display column stacks of the same color for multiple values.

Note

When you filter the rows of a PivotTable, the space formerly occupied by the missing rows appears as an empty region between the PivotTable and PivotChart.