Programming microsoft office 2000 web component

Members pview. ChildMembers "Drink". ChildMembers "Dairy". This code, also taken from the FilteringMembers. When filtering on a set of members within a field, use the field's FilterMembers property; when setting the selected member for a fieldset on the filter axis, set the fieldset's FilterMember property. This example uses the Members collection of the fieldset to get a reference to the desired PivotMember object.

Although you can use the unique name here as well, I want to show you how to dynamically locate a member using the Members collection. This collection is available for every fieldset and returns a hierarchical collection of PivotMember objects. If the fieldset has an All member, representing the total for all members most do , the top level of the PivotMembers collection will have one item representing the All member. Each member has a ChildMembers collection and a ParentMember property, allowing you to navigate up and down the member hierarchy.

You can retrieve child members either by name or by index. Although the PivotTable component does not yet support top or bottom N filtering, you can simulate these techniques by retrieving the top or bottom N member names and using the FilterMembers property to show only those members. If you already have retrieved all members from the server, you can use the top and bottom N filtering techniques described in Chapter 2. Then set the FilterMembers property to include only those members.

For an example of this technique, see Chapter 7. We have not discussed a few elements of the PivotTable component yet—specifically the toolbar and the title bar. As you know, the toolbar is the row of command buttons across the top of the control, and the title bar is the strip of text beneath the toolbar that gives you a title for your report. The toolbar can be either visible or hidden; by default, it is visible. Although the toolbar offers many useful commands, you might want to hide it and show your own user interface for manipulating the control.

To do so, simply set the DisplayToolbar property at the top-level interface of the control to False. The title bar can also be visible or hidden. You can customize the text in it, the background and text colors, and the font settings font name, font size, and the bold, underline, and italic settings.

Typically, you will want to set the caption of the title bar to an appropriate title for your report. To do so, you need only one line of code:. The Titlebar property returns a PivotLabel object, with which you can adjust all the other font and color settings just mentioned.

A few other properties control the overall look of the report. These expand indicators offer a one-click method for expanding and collapsing a member to show or hide its child members.

If you turn the expand indicators off, the user can still double-click the member or use the Expand toolbar button to expand and collapse. Two modeless windows can be displayed floating above the PivotTable component: the PivotTable Field List and the Property Toolbox that was first mentioned in Chapter 2.

Both of these windows can also be displayed or hidden programmatically by toggling the DisplayFieldList and DisplayPropertyToolbox properties.

Since these are Boolean properties, you can also use them to discover whether the two windows are being displayed. Automatically displaying the field list is useful when a user first opens a report that does not yet have any fields or totals in the view. You can format a number of the elements in a PivotTable report differently than their default appearance.

However, the PivotTable component does not yet support per-cell formatting in the report. You can set the number format for totals shown in the report, make the subtotal numbers look different than the other numbers, adjust the formatting of the member labels, change the appearance of the field buttons, and change the formatting of the various drop areas. Chapter 7 will explain how to automatically apply formatting based on your web page's current style sheet. For now, let's take a look at some of the simpler formatting tasks.

Most likely, you first will want to format the numbers in your report. By default, the numeric values appear without any number formatting, but you can apply any of the number formats supported in the Spreadsheet component. See Chapter 2 for more information on this. For example, if you are analyzing sales data, you generally want to format the numbers in a specific currency format.

To do this, you would write code like so:. Totals "Store Sales". Although you specify the number format for the PivotTotal object, the font and color settings for the totals in your report are not exposed by this object. You'll probably want to make your grand totals stand out in the report—for example, by making the background color something other than white and making the numbers bold. To do so, you would write code like this:. FieldSets 0. Fields 0 pfld. The grand total of the row or column axis is actually a subtotal of the very first included field of the very first fieldset on the particular axis.

To adjust its formatting, you establish a reference to this field and then modify the SubtotalBackColor and SubtotalFont properties. You can use this same technique to format the subtotals at any inner level so that they are different than the numbers contributing to them. To adjust the subtotal formatting, first establish a reference to the field to which the subtotal belongs and adjust the SubtotalBackColor and SubtotalFont properties.

Keep in mind that these properties are carried with the field as the user or your code moves it around the report. If your intention is merely to format the grand totals to look a certain way regardless of what fields are placed on the row or column axis, you need to reapply formatting during the QueryComplete event, which is raised whenever the report's layout is changed.

When applying formatting using the Property Toolbox, you might not be sure how to select the entire control to set control-level formatting such as the text in the title bar or various display settings for the entire view. To select the whole control, click the title bar. You might notice in all the samples in this chapter that the PivotTable component seems to automatically grow and shrink when you change the view. This behavior, which is called AutoFit, is on by default. When AutoFit is on, the PivotTable control will resize itself to show all data in the report without internal scroll bars.

This is especially useful in web pages since they already contain scroll bars. The PivotTable control will resize until it reaches the MaxHeight and MaxWidth property settings and then will show internal scroll bars again. If you are working in a fixed forms environment such as Visual Basic, you likely will want to turn this behavior off so that the control stays the desired size and does not extend outside the form.

The form will crop the control when it extends past the form's edge. To do so, set the AutoFit property at the control's top-level interface to False. The PivotTable component has another level of AutoFit; by default, this control resizes the column widths of the detail grid so that no numbers get truncated. Again, each column will grow until it reaches the limits imposed by the DetailMaxWidth and DetailMaxHeight properties.

They had never seen a control that automatically resized itself and did so while you scrolled the document, making more of the report visible. Mike Coulson, the extraordinary developer who programmed the user interface portion of the PivotTable component, spent quite a long time optimizing this control so that it retrieves only the data currently shown onscreen. This allows the control to display data quickly even if it still has to read 10, more rows in the background. To keep the AutoFit feature from slowing this process down, Mike dynamically adjusted the control's size as new data scrolled into view if the data was larger than that shown previously.

Since the Internet Explorer team never thought that a control might resize itself while scrolling in the document, this behavior exposed quite a few bugs in the Internet Explorer code base. Thankfully, the Internet Explorer team was responsive to fixing those bugs for us. However, I think the PivotTable control still qualifies as one of the most complex controls a container can hold, especially when using the AutoFit feature. Previous page. Table of content. Next page. Binding to the Data Source Component In addition to the three sources of data mentioned above, the PivotTable component offers one more choice for retrieving data.

Tabular Data Sources To load the PivotTable component with data from a tabular data source, you would write the following code, taken from the PivotTableList.

The code you execute is just one line long: PivotTable1. Adjusting the View Layout After you have connected the PivotTable component to a data source, you probably will want to add fieldsets and totals to the view, include and exclude fields in a fieldset from the view, and expand or collapse members or fields.

For example, the following code builds a report showing customer locations inside customer genders on the row axis: pview. FieldSets "Customer" You also can add multiple fieldsets to the column and filter axes and multiple totals to the data axis.

To insert a fieldset before an existing fieldset in the view without adding it as the innermost fieldset on the axis, use the optional Before parameter on the InsertFieldSet method: pview. FieldSets "Customer" , 0 This code adds the Customer fieldset to the left or outer side of the Gender fieldset. Removing a fieldset from an axis is fairly simple and, not surprisingly, involves the RemoveFieldSet method: PivotTable1.

RemoveFieldSet FieldSet The FieldSet parameter is actually quite lenient—you can specify the name of the fieldset, a reference to a PivotFieldSet object, or the index of the fieldset on the axis.

If you are working with a tabular data source and want to completely remove all detail fields, use the following code: For Each fs In PivotTable1. Name Next To manipulate both the detail fieldsets and the totals on the data axis of the PivotTable component, use the ActiveView.

Creating New Totals for Tabular Sources If your data source is tabular, the PivotTable component will not have any totals available for the report by default. Sorting Now that you know how to load data into the PivotTable component and lay out the view to produce a simple report, you might want to set an initial sorting order or adjust the sorting of the report in response to a specialized user interface you have supplied.

Filtering After setting the sort order for your report, you might want to set some initial filtering for it so that only a subset of data is displayed. To set the initial value for a fieldset on the filter axis, write the following code: ' Set the initial Product filter field selection to ' "Dairy", which is contained within "Drink". Customizing the View We have not discussed a few elements of the PivotTable component yet—specifically the toolbar and the title bar.

To do so, you need only one line of code: PivotTable1. Applying Formatting Programmatically You can format a number of the elements in a PivotTable report differently than their default appearance.

To do this, you would write code like so: PivotTable1. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. Can you help us improve? Resolved my issue. Clear instructions. Easy to follow. No jargon. Pictures helped. Didn't match my screen. Incorrect instructions. We'll discuss each of these controls briefly in this section and in much more detail in the following chapters.

The word "Office" in the name "Office Web Components" indicates that the controls were developed by some of the same programmers who created Microsoft Excel and Microsoft Access and that the controls were made to look, feel, and behave like small versions of their Microsoft Office siblings.

These controls definitely don't have all the features found in Excel and Access—in other words, you wouldn't want to dynamically download all of Excel and Access to view a report in your browser! However, the controls do contain many of the commonly used features, especially those needed when interacting with content that's already been created. Plus, they can read and write the HTML file format of Excel , allowing the user to click a button and load the current data into Excel for more powerful analysis.

In this book, I'll detail the noteworthy Excel or Access features that are and aren't supported by each component. I'll also show you how to add some of these missing features with your own code.

The "Web" part of OWC's name is often misleading. However, the controls have a few behaviors that make them especially suited to the unique environment of Internet Explorer. For example, web browsers automatically support scrolling along a document, and it's often annoying for a control in the page to have its own set of scroll bars.

The Spreadsheet and PivotTable controls can be set to automatically adjust themselves to fit their current content without requiring internal scroll bars. Also, all the controls support the color names available in Internet Explorer in addition to supporting numeric RGB values. That means you can set the background color of an element to "CornSilk" or "PapayaWhip" my personal favorite , and the control will convert the color to the appropriate RGB value just as Internet Explorer would.

The "Components" part of OWC's name is a touch confusing, although it's more accurate than using the word "Controls" though I will often refer to OWC as "controls" for convenience throughout this book. The Office Web Components are unusual in that they can be used in control containers like web pages, Visual Basic forms, and so on, as well as in memory as invisible objects. Most COM controls can be used only as visible controls in control containers, and most invisible objects, such as those accessed via the Microsoft ActiveX Data Objects ADO interface, can be used only in memory and cannot be put on a form or web page.

The OWC library was built so that its components could be used either way, which enables you to use the controls with the user interfaces they expose or for their base services, such as spreadsheet recalculation. The ability to use the components as invisible objects also enables you to use the library on a server to easily generate static content that users can view in any web browser more on that later in the chapter.

That means you can weave the components into a custom solution and make them look and act the way you want. I will discuss most of the important properties, methods, and events in the subsequent chapters and will cover many more of these in the chapters describing the various solutions found on the companion CD.

Let's take a brief look at each of the components and discuss what kinds of solutions you can build with them. As already mentioned, the next four chapters will cover each component in more depth. The Spreadsheet component shown in Figure is like a small version of an Excel spreadsheet, complete with a spreadsheet user interface and a recalculation engine that supports nearly all the calculation functions in Excel