Programming Microsoft Office 2000 Web Components Chapter 4 Section 5

Advanced Programming Skills

 

Now you have learned how to complete some basic programming operations. Now let's discuss some more advanced skills. Many of these advanced skills are involved in Chapter 7 sales analysis and report solutions. Therefore, I will only give a brief introduction to these skills. For more information, see Chapter 7, detailed explanations of the Code contained in this section are provided.

Save and restore views

Any developer who uses the pivot table component in the report system may need to provide the following functions to their users: Save the created report view, and restore the report later-but the data in the report will be the latest data. The pivot control can easily complete this task. This technique is described in Chapter 7 solutions.

The most common method to save and restore a view is to use the xmldata attribute of the control top-level interface. This is a read/write attribute that returns a huge string in XML format. Do not confuse it with the XML Stream used as the data source-the xmldata attribute returns the layout, formatting, filtering, sorting, and other definition information of the current view. The string of this definition information completely describes the view, but does not contain any value.

To save the definition of the current view, read the value of this attribute and save it in a place where you can extract it again later. A common method is to submit the string to an ASP page or CGI program, which then writes the string to a file, or write it into the database and associate it with the current user. When you need to view the report again, the program should extract the string from the permanent storage device and use it to set the xmldata attribute. After the property is set, the pivot table control clears all currently displayed data, connects to the original data source (if the data source is not connected), and executes the corresponding query to recreate the report. Therefore, you will see all the latest data in the report, but the layout of the report is exactly the same as that when you store the report.

The pivot control will simply discard those that are no longer valid in the view. For example, if a field set in the view is deleted from the data source after the view is saved, the pivot table control ignores any information related to the field set, instead of trying to restore it in the view. The same applies to aggregate values or members that are no longer included in the returned dataset.

You can run the xmldataproperty.htm file on the optical disk to view how to use the xmldata attribute. The button at the top of the page allows you to obtain the value of the xmldata attribute and set the value of this attribute. In addition, you can clear the pivot report. Please try to use the three functions: getting the attribute value, clearing the pivot report, and resetting the attribute.

Note that the connection information (connection string, cube name, or command text) is also included in the string returned by the xmldata attribute. If the location of the data source changes between the user saving the report and re-opening the report, the pivot table cannot be connected to the data source and an error occurs. If this happens, you should add the error handling code to capture and adjust the connection information before passing the string to the pivot control. The connection string is stored in the <X: connectionstring> label, and the data member (the name of the cube of the OLAP data source) is stored in the <X: datamember> label, command text (used for table column data sources) is stored in the <X: commandtext> label. You can use VBscript or the instr and replace functions in Microsoft VBA to easily find and replace the content of these tags. Chapter 7 provides more information about xmldata attributes and how to change the data source.

Lock View

The report system usually needs to publish two types of reports: one is a standard report that everyone may want to view, and the other is a special report that users can modify the report structure and save. You may need to set the standard report so that you cannot delete or add fields to the report, but can still perform drilling and filtering. You may also need to disable the filter function. You can only expand or contract a view. The Pivot Table component provides some settings to help you lock the view. It also provides events that can be used to monitor what operations the current user is performing. However, there is no general mechanism to prohibit these operations.

 

XMLAdventure

All these amazing XML features should be attributed to Kevin grealish, another well-known owc developer who spent countless nights studying XML parsers and namespaces, and those constantly changing formats and standards. When developing the pivot table component, the XML standards for namespaces are constantly modified. Because we have to read the XML data released by excel2000, it is a hard and long-term task to make the XML processing function of our code consistent with that of Excel. If the pivot report data source is table data, our code must be consistent with the MDAC permanent provider used to load the XML data stream saved in Excel. Finally, Kevin solved this conflict-so the next time you use the xmldata attribute or use XML as the data source, remember to thank Kevin.

 

If you are not allowed to add or delete fields on the row or column axis, set the allowgrouping attribute of the pivot table control to false. When this attribute is set to false, the pivot table control prohibits you from adding or deleting fields on the row or column axis. However, you can add the field set to the filter axis and add new summary values to the view.

If you are not allowed to change any filtering settings, set the allowfiltering attribute to false. When this attribute is set to false, the pivot table component allows you to open the filter drop-down list, but does not allow you to change the current filter settings-in other words, you can see the current filter condition, but they cannot be changed. The control also prohibits you from adding a field set to the filter axis.

To ensure that the formatting information applied to the report cannot be changed, you can set the allowpropertytoolbox attribute to false. This will make the attribute toolbox button on the toolbar and the menu items in the corresponding context menu unavailable. In this way, you cannot even open the attribute toolbox. However, you can still use keyboard formatting commands, such as Ctrl-B, Ctrl-I, and Ctrl-U. Refer to the lockdownview.htm example file in the optical disk to practice the usage of these attributes.

Determine the selected part for penetration Drilling

Like chart components, the pivot table component also has a selection attribute in the top-level interface to return the selected object. In addition, the type of the object returned by this attribute also changes. Therefore, you need to use the typename function in VBScript and VBA to determine the type of the returned object. Run the determiningselection.htm file on the optical disk to check which types of objects can be returned by the pivot table control from this attribute.

If you know what is selected, you can simulate some interesting functions. Here we will discuss the function of getting detailed information through drilling through penetration.

The OLAP system is good at displaying High-Level Data summaries and allows users to drill down to obtain detailed information of each layer. However, the user will eventually reach the bottom layer of the supercube, and often need the "drilling through" function to obtain the lower layer details that constitute the lowest layer summary value. If you are currently using a table column data source, the pivot control can automatically complete this task, because the detailed data is always available in the case of a table column data source. However, there is no general method for ole db for OLAP to extract the detailed information set after the total. Even so, the vertical solution usually gets enough information to find the table column data sources that contain detailed data and thus determine what SQL statements should be made up, to obtain the detailed information data row of all members required to make up the total value .??????.

The code in the determiningselection.htm example file in the following example shows how to obtain all required information from the selected summary value to generate an SQL statement that extracts detailed information about the summary value.

Sub partition ttablepartition selectionchange () 'Local Variables Dim sel 'temporary selection object Dim sfilters 'current filter strings Dim fset 'temporary fieldset reference   'Grab the current selection Set sel = optional ttable1.selection   'There are invalid Types of objects that the selection 'Could be, depending on what was selected 'Examples include repeated taggregates, repeated ttotals, 'Using tmembers, using tfields, and using tview 'You can use the typename function to determine the type 'Of the object ' 'If the user selected an aggregate number, 'The typename function will return "using taggregates"   'Set the type name label Lbltype. innertext = typename (SEL)   'If the type is "taggregates", show how to get 'The row and column members that define that aggregate 'You could of course make this a select case statement 'And handle other selection types If typename (SEL) = "taggregates" then 'Your taggregates cocould contain your items, but since 'This is a sample, I will just work with the first item Set Partition Tagg = SEL. Item (0)   'Set the value label Lblval. innertext = Invalid Tagg. Value   'Get the total caption, the row and column members, And the current Filters Lbltotal. innertext = Invalid Tagg. Total. Caption Lblcolmems. innertext = buildfullname (Tagg. Cell. columnmember) Lblrowmems. innertext = buildfullname (Tagg. Cell. rowmember)   For each fset in incluttable1.activeview. filteraxis. fieldsets Sfilters = sfilters & fset. Caption & "= "&_ Fset. filtermember. Caption &"," Next Lblfilters. innertext = sfilters   Else 'Selection was something other than a has taggregates 'Object. Clear the labels. Lblval. innertext = "" Lbltotal. innertext = "" Lblrowmems. innertext = "" Lblcolmems. innertext = "" Lblfilters. innertext = ""   End if 'typename (SEL) = "taggregates"   End sub 'gradient ttable1 _ selectionchange ()

This code first uses the typename function to determine the type of the selected part. If the type is repeated taggregates, a summary value is selected. The Code extracts the value of the summary value and the total value of the summary value. (A summary value must belong to only one total .) Then, the Code uses the auxiliary function buildfullname to create a string containing all the Members on the layer of the axis. The code for the buildfullname function is as follows:

Function buildfullname (effectmem) 'Local Variables Dim pmtemp 'temporary extends tmember reference   'Start by getting the current member's name Sfullname = effectmem. Caption   'Set the temporary reference to the current Member Set pmtemp = effectmem   'Navigate up the parent hierarchy until you hit nothing While not (pmtemp. parentmember is nothing) Set pmtemp = pmtemp. parentmember Sfullname = pmtemp. Caption & "-" & sfullname Wend   'Return sfullname Buildfullname = sfullname   End function 'buildfullname ()

The key part of the code is the while loop in the middle. As mentioned above, each member has a parentmember attribute, which is used to return the parent Member of the member (if any ). If no parent member exists, the property returns nothing (in other languages, it is a pointer to a null object ). Loop generates a string based on the caption attribute of each member encountered during upstream game in the layer. Of course, you may need to use the name attribute or even the uniquename attribute (instead of the caption attribute) to obtain the value required by the WHERE clause in an SQL statement.

Show empty members

By default, the pivot table component does not display rows or columns in the report that each cell does not contain data. However, sometimes rows or columns without data make sense, and you need to display them. You can use the following code to implement this function:

Required ttable1.activeview. rowaxis. displayemptymembers = true Required ttable1.activeview. columnaxis. displayemptymembers = true

Displayemptymembers is an attribute of the row or column axis. The default value is false. For OLAP data sources, this setting affects the MDX query statement sent from the pivot table component to the data source. Therefore, if this attribute is set to true, more data will be returned. For the table column data source, it also affects the MDX statement used to query the temporary cube, but because the client contains all the data, therefore, this attribute will hardly affect performance unless the temporary cube is very sparse.

Display visual total value

By default, the pivot table component only displays "visual total value ". This means that the subtotal and total in the report are the total data displayed in the current report. If a member is filtered out, the subtotal and total values of this Member are not included. In this way, even if you filter some members, all the data displayed in the report will be accumulated.

Assume that there is a geographic layer on the line axis, which contains two levels: state and city. The subtotal of each State represents the sum of all its cities. Now, if you filter out the cities "Seattle" and "Redmond. Should the sum of Washington State remain unchanged, or is it the sum of all cities currently displayed in the report? This is indeed a very heated debate. Fortunately, the pivot table control supports these two modes.

The Pivot Table component displays the total visual values by default. Use the following code to display the total values of all members without considering whether the Members are visible:

Required ttable1.activeview. totalallmembers = true

When the totalallmembers attribute is true, the pivot table control displays a small star number next to the subtotal and total to show that this value does not only represent the visible member. The behavior of displaying an asterisk is the same as that in the OLAP pivot report function of Excel 2000. If you can use HTML below the pivot control to explain the meaning of the asterisk, it is more perfect.

Display the total title as the row title

By default, the pivot table component displays the total title as the innermost column title in the report. However, you may need to display these total titles as the innermost row titles. You can use the following code to easily complete this task:

Required ttable1.activeview. totalorientation = pltotalorientationrow

The totalorientation attribute can be set to a value in the pivotviewtotalorientationenum constant. This constant contains the value indicating the row direction and the value indicating the column direction. The pivot control can only place the title of the total on the inner row or column axis.

Auto expand

By default, the pivot table component keeps all fields and members in the view in a shrinking State. Users can expand along the axis in any path to obtain more detailed information they need. You may need to configure your reports so that all fields and members are automatically expanded when they are added to the view. Because this may generate a huge and hard-to-handle display interface, you should use this function only when the auto-expand function produces a report of an appropriate size.

You can use the following line of code to enable the auto-expand function:

Required ttable1.memberexpand = plmemberexpandalways

The value of this attribute is taken from the receivttablememberexpandenum enumeration value. Other available values include plmemberexpandnever and plmemberexpandautomatic (default ).

This attribute is also useful when you use the pivot control on a Web server to generate a GIF image of a report (this feature will be discussed in the next section. Set this attribute to plmemberexpandalways so that Members are always expanded. This ensures that all information in the report is displayed when a graph is generated. This is very important, the user only obtains a static graph of the report and cannot expand the members of the graph.

InWebUse the pivot table component on the server

Like other Office Web Components, the pivot table component can also be used without a user interface. You can create a pivot table component as a memory object and connect it to a data source, use a program to dynamically generate a report, and finally generate the GIF image of the current report, or generate your own HTML report by traversing the elements in the report view. Using the pivot control on the Web server may be more attractive than writing MDX query statements, then using ado md, and then manually formatting the query structure.

When using the pivot control on a server, you can use all the programming skills we have discussed so far. The only new programming element you need to know is the exportpicture method. This method is almost the same as the exportpicture method of the chart component discussed in chapter 3. The following code outputs the current report as a GIF image file:

Set fsotemp = Createobject ("scripting. FileSystemObject ") Strfilename = fsotemp. gettempname Extends tTable. exportpicture SESSION ("strtempfilepath") & strfilename ,_ "GIF", gradient tTable. maxheight, gradient tTable. maxwidth Response. Write " Strfilename & "'>" Session ("updated" & SESSION ("cnt1_timages") = strfilename Session ("cnt0000timages") = SESSION ("cnt0000timages") + 1

The key line of this Code is to call the exportpicture method. An instance of FileSystemObject (a class in the Microsoft Script Runtime Library) obtains a temporary file name for the new GIF image, and then the program adds the file name to the root path, the result is passed to the exportpicture method as the first parameter. Like the chart component, the second parameter must be "GIF ". The last two parameters indicate the height and width, but the programming here is different from that in the chart component (the chart component can change the output image to any size ), you must use the maxheight and maxwidth attributes of the pivot table component. Otherwise, the generated report image may be incomplete.