Translator's description: Welcome to my blog: http://blog.9cbs.net/daidaoke2001/
The mistakes in the translation or improper look at it, this is also the greatest driving force I insist on the translation work.
My email: tangtaike@163.com
For reprint, please notify us in advance.
Advanced programming skills
At this point, you have learned how to complete some basic programming operations, now let us discuss some more advanced skills. In the seventh chapter, the sales analysis and reporting solutions will involve many of these advanced techniques, so I only make some simple introduction to these techniques, you can refer to Chapter 7, which is related to the code contained in this section. Detailed explanation.
Save and recovery view
Any developer who uses perspective table components in the report system may need to provide them with the following features: Save the created report view and restore this report in the future - but the data in the report will be the latest data. The perspective table control can easily complete this task. This skill is described in the solution in Chapter VII.
The most common method of saving and recovery view is the XMLDATA property of the top-level interface of the control. This is a read / write attribute that returns a huge string in an XML format. Don't confuse it and the XML stream used as a data source is returned, formatted, filtered, sorted, and, etc., other definition information. The string of this definition information is a complete description of the view, but does not contain any values.
If you need to save the definition of the current view, you should read the value of this property and save it in one after you can re-extract it. A common method is to submit this string to an ASP page or a CGI program, which will then write this string into a file or write to the database and associate with the current user. When the user needs to view the report again, the program should extract this string from the permanent storage device and use it to set the XMLData property. After the property is set, the perspective table control clears all currently displayed data, connects to the original data source (if not connected to the data source), and performs the corresponding query to recreate the report. Therefore, users will see all the latest data in the report, but the layout of the report is exactly the same as the user stores reports.
For those who are no longer valid in the view, the perspective table control will be simply discarded. For example, if a field set in the view is deleted on the data source, the perspective table control is ignored by any information related to this field set without trying to restore it in the view. The same is true for the summary values or members of the data set returned in the query.
You can run the xmldataProperty.htm file in the book CD to watch how to use the XMLData properties. The button at the top of the page allows you to get the value of the XMLData property, and the value sets this property. In addition, you can empty the perspective report. Try to use the value of the property, clear the perspective table report, and reset the three features of the property.
Note that the connection information (connection string, and Cube name or command text) are also included in the string returned in the XMLDATA property. If the location of the data source between the user saves the report and reopen it, the perspective will not be able to connect to the data source and generate an error. If this happens, you should add an error handling code to capture and adjust the connection information before passing the string to the perspective control. The connection string is stored in the
Reporting systems often need to post two types of reports: One is a standard report that everyone needs to view, and the other is a special report that users can modify the report structure and saved. You may need to set the standard report so that users cannot delete or add fields in the report, but still drill and filter. You may also need to disable filtering, only allow operation of expand and shrink in the view. The perspective table component provides some setting options to help you lock the view. It also provides some events that can be used to monitor what operations are doing in the current user; however, there is no general mechanism for prohibiting these operations.
XML adventure
All of these surprising XML features should be attributed to another OWC team's famous developers, Kevin Grealish, who spent countless nights to study XML parsers, namespace, and those constant formats and standards. When we develop perspective table components, the XML standards about the namespace are still constantly modified. Because we must read the XML data released by Excel2000, so that our code processes XML functionality with Excel, this is a long-term job. And if the data source of the perspective report is a table data, our code must also be consistent with the MDAC permanent provider of XML data streams saved by Excel. Finally, Kevin solved this contradiction - so I would like to thank Kevin when you use XMLDATA properties or use XML as a data source.
If the user is not allowed to add or delete a field on a row axis or column axis, you should set the allowGrouping property of the perspective check to False. When this property is false, the perspective check is prohibited from adding or deleting a field on a row axis or column axis. However, users can still add field sets to the filter axis and add new summary values to the view.
If the user is not allowed to change any filter settings, the AllowFiltering property should be set to false. When this property is false, the perspective table component allows the user to open the filtered drop-down list, but the user does not allow the current filter settings - in other words, the user can see the current filtering condition, but cannot change them. The control also prohibits users from adding the field set to the filter axis.
To ensure that users cannot change formatting information applied to the report, the AllowPropertyToolbox property can be set to false. This will make the property toolbox button on the toolbar, and the menu items in the corresponding context menu are not available. This user does not even open the property toolbox. However, the user can also use the keyboard format command, such as Ctrl-B, Ctrl-I, and Ctrl-U. Check the LockDownView.htm example file in the CD to practice these properties.
Determine the selected part to do penetrate
Like the chart assembly, the perspective table component also has a Selection property in the top interface to return the currently selected object. Moreover, the type of object returned by this property is also changed, so it is necessary to use the type of TypenAme functions in VBScript and VBA to determine the type of return object. Run the DETERMININININSELECTION.HTM file in the book CD to see which different types of objects can be returned from this property. If you know what is currently selected, you can simulate some interesting features. One such function is discussed here: through penetration drill to obtain detailed information.
The OLAP system is good at displaying the data summary of the high-level, and allows the user to obtain detailed information of each layer via "down drill". However, the user will eventually reach the bottom of the super cube, and often require "penetrating drill" to obtain the lower layer details of the minimum layer summary value. If the current use is a table column data source, the perspective check can be automatically completed because detailed data is always available in the case of the table column data source. However, for OLE DB for OLAP, there is no general method, which can be used to extract a collection of detailed information behind the total. Nevertheless, vertical solutions typically get enough information to identify the list of data sources that contain detailed data, and can determine what kind of SQL statement should be formed to obtain all the desired values. Member details data line. ? ? ? ? ? ? .
The code from the DETERMININININSELECTION.HTM example file in the photo disc is taken below, shows how to get all information required from the selected summary value to generate the SQL statement of the detailed information of the summary value.
SUB Pivottable1_selectionChange ()
'Local Variables
DIM SEL 'TEMPORARY Selection Object
DIM SFILTERS 'CURRENT FILTER STRINGS
DIM FSET 'TEMPORY FIELDSET REFERENCE
'Grab the Current Selection
SET SEL = Pivottable1.Serection
'There is the success of Objects That the selection
'Could Be, Depending On What Was SELECTED
'Examples include Pivotaggregates, Pivottotals,
'Pivotmembers, Pivotfields, and PivotView
'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 "Pivotaggregates"
'Set the Type Name Label
Lbltype.innertext = Typename (SEL)
'If the Type is "pivotaggregates", show how to get
'The row and colorn members That Define That AggRegate
'You Could of Course Make this a select case statement
'and Handle Other Selection TypeSif TypeName (SEL) = "Pivotaggregates" THEN
'Pivotaggregates Could Contain Many Items, But Since
'this is a name, i will just work with the first item
SET PIVOTAGG = SEL.Item (0)
'Set the value label
lblval.innertext = Pivotagg.Value
'Get The Total Caption, The Row and Column MEMBERS,
'and the current filter
Lbltotal.innertext = pivotagg.total.caption
LBLCOLMEMS.INNNERTEXT = buildfullname (pivotagg.cell.columnmember)
LblrowMems.innertext = BuildFullname (pivotagg.cell.rowmember)
For Each Fset in Pivottable1.ActiveView.Filteraxis.fieldsets
Sfilters = sfilters & fset.caption & "=" & _
FSET.FILTERMEMBER.CAPTION & "", "
NEXT
LBLFilters.innertext = sfilters
Else
'Selection Was Something Other Than A Pivotaggregates
'Object. Clear The labels.
LBLVAL.INNERTEXT = "" "
lbltotal.innertext = "" "
LblrowMems.innertext = ""
LBLCOLMEMS.INNNERTEXT = ""
LBLFilters.innertext = ""
End if 'Typename (SEL) = "PivotaggRegates"
End Sub 'Pivottable1_SelectionChange ()
This code first uses the TypenAme function to determine the type of the selected part; if the type is pivotaggregates, it indicates that the user selects a summary value. The code will extract the value of the summary value, and the total value to which the summary value belongs. (Provisions specifying a summary value can only belong to a total.) Next, the code uses the auxiliary function buildfullName to create a string containing all members of the layer on the axis. The code of the buildfullname function is as follows:
Function Buildfulname (Pivotmem)
'Local Variables
DIM PMTEMP 'Temporary Pivotmember Reference
'Start by getting the current member's name
Sfullname = pivotmem.caption
'Set the temporary reference to the current member
Set PmTemp = Pivotmem
'Navigate Up The Parent Hierarchy Until You Hit NothingWhile Not (Pmtemp.ParentMember IS Nothing)
Set PmTemp = PmTemp.ParentMember
SfullName = Pmtemp.caption & "-" & sfullname
Wend
'Return Sfullname
Buildfullname = sfullyname
End function 'buildfullname ()
The key part of the code is the middle of the While cycle. As mentioned earlier, each member has a ParentMember property that returns the parent member of this member (if present). If there is no parent member, the property will return Nothing (which is a pointer to the NULL object in other languages). The cycle generates a string based on the CAPTION attribute of each member encountered in the layer. Of course, you may need to use the Name property, or even the uniqueename property (instead of the CAPTION property) to get the value required by the WHERE clause in the SQL statement.
Empty member
By default, the perspective table component is not displayed in the report or columns of each cell without data. However, sometimes no data or columns are meaningful, you need to display them. You can use the following code to implement this function:
Pivottable1.activeView.Rowaxis.displayemPTymembers = TRUE
Pivottable1.activeView.columnaxis.displayemptymembers = TRUE
DisplayemptyMembers is an attribute of a row or column axis, the default value is false. For OLAP data sources, this setting affects the MDX query statement sent to the data source, so, if this property is set to true, more data is returned. And for the table row data source, it will also affect the MDX statement used to query the temporary CUBE, but because the client contains all the data, this attribute does hardly affect performance - unless the temporary cube is very sparse.
Display visual value
By default, the perspective table component only displays "visual values". This means that the small and total data in the report is the total data displayed in the current report. If a member is filtered, the small and total of this member will not include the value of this member. 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 row axis, including two levels: states and cities. The small tab of each state represents the total of its city. If you filter out the city seattle and redmond. The total of Washington State should not change, or become a total of all cities in the report now? This is indeed a very intense problem. But fortunately, the perspective control supports these two modes.
The perspective table component default display can be permitted. If you need a control to display a total value of all members, don't consider whether the member is visible, use the following code:
Pivottable1.activeView.TotalalMembers = TRUE
In the case where the TotaLallmembers property is TRUE, the perspective table control will display a small asterisk next to the small count and the total to show that this value does not only represent visual members. The behavior of the asterisk is the same as the behavior in the OLAP perspective feature of Excel 2000. If you can use HTML's meaning of the asterisk in the perspective control, it is more perfect.
Display the total title as a row title
By default, the perspective table component displays the total title to the innermost column header on the report. But sometimes, it may be necessary to display these total headings as the innermost line title. You can use the following line code to easily complete this task: pivottable1.activeView.totalorientation = pltotaloreentationRow
The TotalorIntation property can be set to a value in the constant of the PivotViewTotalorIntationNum, which contains a value indicating the direction of the row, and the value of the margin direction. The perspective control can only place the total title on the innermost line axis or column axis.
Automatically expand
By default, the perspective table component will keep all the fields and members to the view, allow them to shrink. The user can be expanded in any path along the shaft to obtain more detailed information they need. You may need to configure your report so that when all fields and members are added to the view, they will be automatically expanded. Because this may create a huge, difficult display interface, so it should be used only when the automatic expansion function is generated by a size appropriate report.
You can use the following line code to open the automatic expansion function:
Pivottable1.memberexpand = Plmembrexpandalways
The value of this attribute is taken from the PivottableMexPandenum enumeration value, which other available values in this enumeration value have PlmembRexpandnever and PlmemberaexPandAutomatic.
This property is also useful when using a Pivot table control to generate a report on a web server (the next section will discuss this function). Set this attribute as Plmembrexpandalway, so that members are always expanded, so that all information in the report can be displayed when generating graphics - this is very important because the user gets a static graphic of the report, Is not a member in the graphic.
Use perspective table components on the web server
Like other Office Web components, the perspective table component can also be used in a memory without a user interface, you can create a perspective table component in the form of memory objects, then connect to a data source, use the program dynamics to generate a report, last Generate the GIF graphics of the current report, or generate your own HTML-form report by traverse the elements in the report view. The way of using the perspective control on the web server may be more attractive than writing the MDX query statement, and then use ADO MD.
When using a perspective check on a server, you can use all programming skills we have discussed so far. The only new programming element you need to know is an ExportPicture method. This method is almost the same as the ExportPicture method of the chart components discussed in Chapter 3. The following code can realize the output of the current report to a GIF graphics file:
Set fsotemp = createObject ("scripting.filesystemObject")
Strfilename = fsotemp.gettempname
Pivottable.ExportPicture Session ("StrtempFilePath") & strfilename, _
"gif", pivottable.maxheight, pivottable.maxwidth
Response.write "
Session ("Pivot" & session ("cntpivotimages)) = StrfileNamesession (" cntpivotimages) = session ("cntpivotimages) 1
The key line of this code is called to the EXPORTPICTURE method. An instance of FileSystemObject (a class of Microsoft script runtrack) has a temporary file name for the new GIF graphics, then the program adds this file name to the root path, the result is passed to the ExportPicture method, as the first Parameters. Like the chart assembly, the second parameter must be "gif". The last two parameters indicate the height and width, but the programming in the chart assembly is different (the output can be changed into any size), you must use the MaxHeight and MaxWidth properties of the perspective table component, otherwise the generated report Graphics may not be complete.
To generate a report graphic on the server, you can create the most extremely limited version. Users can view report graphics in any platform, any browser that can display GIF graphics, but the report is completely unable to interact.
附 附.
Advanced Programming Techniques
Now that you know how to perform some of the basic programmatic operations, let's discuss a few of the more advanced techniques you can use with the PivotTable component. Many of these are implemented in the Sales Analysis and Reporting solution covered in Chapter 7, so I Will Briefly Discuss these Techniques Here and refer you to That Chapter for a more detailed explanation of the code involved.
Saving and resting views
Anyone using the PivotTable component for a reporting system will at some point want to enable users to save a report view they have constructed and recall that report later-but with current data. The PivotTable control makes this quite easy to do. In fact, the Solution in Chapter 7 Illustrates this technology.
The basic approach to saving and restoring a view is to use the XMLData property at the top-level interface of the control. This property is read / write and returns a large string in an XML format. Do not confuse this with using an XML stream as a data source-the XMLData property returns a definition of the current view's layout, formatting, filters, sorts, and so on. The string completely describes the view but does not contain any data values.If you want to save the current view definition, get this property's value and save it where you can retrieve it later. A typical approach is to post this string to an ASP page or a CGI program, which would in turn write the string to a file or database associated with the current user . When the user wants to view that report again, your code retrieves this string from the persistent storage and sets the XMLData property to the retrieved string. When you set this property, the PivotTable control throws away any data it is currently showing, connec ts to the original data source (if it's not connected already), and executes the appropriate query to re-create the report. The user will see any new data that appears in the data source, but the report layout will be the same as it WAS WHEN THE User Saved IT.
The PivotTable control silently discards parts of the view that are no longer valid. For example, if the user had a fieldset in the view that was removed from the data source after the view was saved, the PivotTable control will ignore any information saved with that Fieldset and will not attempt to restore it in the view. The Same is true for totals or members no longer contained in the data set.
To experiment with the XMLData property, run the XMLDataProperty.htm file on your companion CD. The buttons at the top of the page allow you to get the XMLData property and set it back. Plus, they let you clear the PivotTable report. Try getting the property, clearing the PivotTable report, and resetting the property.Note that the connection information (connection string and name of the cube or command text) is also included in the string returned by the XMLData property. If the location of the data source changes between the user saving the report and reopening it, the PivotTable will fail to connect and will generate an error. If this is a possibility, you should include error-handling code to catch this and to adjust the connection information in the string before handing it . to the PivotTable control The connection string is stored in the
LOCKING DOWN A View
Reporting systems often present two types of reports: standard reports that everyone will probably want to see and ad hoc reports that users can construct themselves and save You might want to configure those standard reports so that users can not remove or add fields to the reports,. but they can still drill down and filter. you might also want to disallow filtering, allowing only expanding and collapsing within the view. The PivotTable component offers a few settings to help you lock down the view. It also raises some events that you can use To Monitor What Users are doing; however, no general mechanism for Denying Those Actions exists.adventures in XML
All this wonderful XML stuff only works because another of OWC's star developers, Kevin Grealish, spent many a late night wrestling with the XML parser, namespaces, and seemingly daily changes in formats and standards. While we were in the middle of developing the PivotTable component , the XML standards for namespaces were fluctuating quite a bit. Since we had to read in XML data that Excel 2000 published, it was a continual dance to keep Excel and our code bases in sync. When the source of the PivotTable report was on- sheet data, we also had to stay in sync with the MDAC persistence provider used to load the XMLData streams saved by Excel. This discrepancy eventually worked itself out-so the next time you use the XMLData property or use XML as a data source Thank Kevin.
To prohibit users from adding or removing fields from the row and column axes, set the AllowGrouping property of the PivotTable control to False. When this property is False, the PivotTable control prohibits the user from inserting or removing fields from the row or column axis. Users can still add fieldsets to the filter axis and add new totals to the view.To prohibit users from changing any of the filter settings, set the AllowFiltering property to false. When this property is false, the PivotTable component will let users open the filtering drop-down lists but prohibit them from changing the current filter settings-in other words, they can view the current filters, but they can not change them. The control also prevents the user from adding more fieldsets to the filter axis.
To make sure the user can not change formatting applied to the report, set the AllowPropertyToolbox property to False. This will disable the Property Toolbox button on the toolbar and the corresponding context menu item, prohibiting the user from even opening the Property Toolbox. However, users Can Still Use The Keyboard Formatting Commands, Such AS Ctrl-B, Ctrl-i, And Ctrl-U. TO Experiment with these Various Properties, See The LockdownView.htm Sample File ON Your Companion CD.
DETERMININING Selection for Drill Through
Like the Chart component, the PivotTable component has a Selection property at the top level that returns the currently selected object. As with the Chart component, the type of object returned by this property varies, so use the TypeName function in VBScript and VBA to determine the object type. To see the different kinds of objects the PivotTable control can return from this property, run the sample file DeterminingSelection.htm on your companion CD.You can emulate several interesting features if you know what is currently selected. I will discuss one Of these Features here: Drilling through to details.
OLAP systems are good at showing users a high-level data summary and letting them drill down to deeper and deeper levels of detail. However, a user will eventually reach the lowest level of the hierarchies in the hypercube and often will want the ability to drill through to the underlying details that make up that lowest-level aggregate. in the case of a tabular data source, the PivotTable control can do this automatically because the detail data is readily available. However, there is not yet a general way in OLE DB for OLAP to retrieve the set of details behind an aggregate. Despite this fact, vertical solutions often have enough domain knowledge to pinpoint which tabular data source contains the detail data and to determine how to construct a SQL statement to get the detail rows given all the MEMBERS THAT INTERSECT to Produce That Aggregate.
The Following Code, Taken from the DeterminingSelection.htm Sample File on The Companion CD, Shows How To Get All The Information from a SELECTED AGGREGATE TO FORMULATE A SQL Statement That Gets The Details:
SUB Pivottable1_selectionChange ()
'Local Variables
DIM SEL 'TEMPORARY Selection Objectdim Sfilters' Current Filter Strings
DIM FSET 'TEMPORY FIELDSET REFERENCE
'Grab the Current Selection
SET SEL = Pivottable1.Serection
'There is the success of Objects That the selection
'Could Be, Depending On What Was SELECTED
'Examples include Pivotaggregates, Pivottotals,
'Pivotmembers, Pivotfields, and PivotView
'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 "Pivotaggregates"
'Set the Type Name Label
Lbltype.innertext = Typename (SEL)
'If the Type is "pivotaggregates", show how to get
'The row and colorn members That Define That AggRegate
'You Could of Course Make this a select case statement
'And Handle Other Selection Types
If TypeName (SEL) = "pivotaggregates" then
'Pivotaggregates Could Contain Many Items, But Since
'this is a name, i will just work with the first item
SET PIVOTAGG = SEL.Item (0)
'Set the value label
lblval.innertext = Pivotagg.Value
'Get The Total Caption, The Row and Column MEMBERS,
'and the current filter
Lbltotal.innertext = pivotagg.total.caption
LBLCOLMEMS.INNNERTEXT = buildfullname (pivotagg.cell.columnmember)
LblrowMems.innertext = BuildFullname (pivotagg.cell.rowmember)
For Each Fset in Pivottable1.ActiveView.Filteraxis.fieldsets
Sfilters = sfilters & fset.caption & "=" & _
FSET.FILTERMEMBER.CAPTION & "", "
NEXT
LBLFilters.innertext = sfilters
Else
'Selection Was Something Other Than A Pivotaggregates' Object. Clear The Labels.
LBLVAL.INNERTEXT = "" "
lbltotal.innertext = "" "
LblrowMems.innertext = ""
LBLCOLMEMS.INNNERTEXT = ""
LBLFilters.innertext = ""
End if 'Typename (SEL) = "PivotaggRegates"
End Sub 'Pivottable1_SelectionChange ()
This code first uses the TypeName function to determine the type of selection; if that type is PivotAggregates, you know that the user has selected an aggregate number The code then retrieves the value of the aggregate and the total to which the aggregate belongs (.. . An aggregate by definition belongs to just one total) Next, the code uses a helper function, BuildFullName, to build a single string representing all members up the hierarchy on the axis The code for BuildFullName follows.:
Function Buildfulname (Pivotmem)
'Local Variables
DIM PMTEMP 'Temporary Pivotmember Reference
'Start by getting the current member's name
Sfullname = pivotmem.caption
'Set the temporary reference to the current member
Set PmTemp = Pivotmem
'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 = sfullyname
End function 'buildfullname ()
The key part of this code is the While loop in the center. As mentioned earlier, every member has a ParentMember property that returns the parent for the member if one exists. If it does not, the property returns Nothing, which is a Null object pointer in other languages. The loop builds a single string using the Caption property of each member as it walks up the hierarchy. of course, you might want to use the Name property or even the UniqueName property (instead of the Caption property) to retrieve Values Needed in a SQL Where Clause.showing Empty Members
By default, the PivotTable component will not display any report row or column that has no data values in any of its cells. However, sometimes the fact that a row or column has no data is meaningful, and you want to display it anyway. To Do So, Write the Following Code:
Pivottable1.activeView.Rowaxis.displayemPTymembers = TRUE
Pivottable1.activeView.columnaxis.displayemptymembers = TRUE
DisplayEmptyMembers is a property of the row or column axis, and by default, its value is False. For an OLAP source, this setting affects the MDX query the PivotTable component sends to the data source, so more data is downloaded when this property is set to True. For a tabular data source, this also affects the MDX used to query the temporary cube. But since all the data is on the client by that time, it hardly affects performance-unless the cube is exceedingly sparse.
Displaying Visual Totals
By default, the PivotTable component displays what are known as visual totals. Displaying visual totals means that the subtotals and grand totals in the report are the totals of the data currently displayed in the report. If a member is filtered out, the subtotals and grand totals above that member do not include that member's value This way, all the data shown in the report adds up, even if you filter out some members.Suppose you have a geographical hierarchy on the row axis with two levels:. State and City. The Subtotal for Each State Reflects The Total of All Cities In It. Now suppose you filter out the cities seattle and redmond. SHOULD The TOTAL for
Washington
State Be The Same Number AS Before, or Should ITAL OF All Cities Still Shown In The Report? this is actually a hotly debated Issue. Fortunately, The Pivottable Control Supports Both Modes.
The Pivottable Component Shows Visual Totals By Default. If You Want this Control to Show Totals for All Members Regardless of their visibility, Use this code:
Pivottable1.activeView.TotalalMembers = TRUE
The PivotTable control will place small asterisks next to the subtotals and grand totals when TotalAllMembers is True to indicate that the values are not reflecting the visibility of members. The asterisks are shown to match Excel 2000's OLAP PivotTable reports feature behavior. Ideally, you should put An Explanation of The Asterisk in Html Below The Pivottable Control.
Showing Total Captions as Rows
By default, the PivotTable component displays total captions as the innermost column headings on the report Occasionally, it is desirable to show those total captions as the innermost row headings instead You can easily accomplish this with the following line of code:.. PivotTable1.ActiveView .Totalorientation = pltotalorientationRow
The TotalOrientation property is set to one of the PivotViewTotalOrientationEnum constants, which include values for row or column orientation. The PivotTable control does not yet support placing the total caption anywhere but the innermost level of the row or column axis.
Expanding Automatically
By default, the PivotTable component will leave all fields and members collapsed when they are added to the view. Users can then expand any path along the axis about which they want to see more detail. You might want to configure your report to automatically expand all Fields and Members As Soon as They Areaded To The View. Since This Can Respert In A Large and Unwield Display, You Should Do this Only ife-expansion Will Generate A Reasonably Sized Report.
To Turn The Auto-Expansion Behavior on, Write the Following Line of Code:
Pivottable1.memberexpand = Plmembrexpandalways
The Value of this Property is Taken From The PivottableMemberexpandenum Enumement, with the Other Possible Values of Plmemberexpandnever and plummberexpandautomatic (Which is the default).
This property also comes in handy when using the PivotTable control on a web server to generate a GIF image of your report, which we'll discuss in the next section. Setting this property so that the members always expand ensures that all information in the report Is Exposed when IMGE IS WRITTEN-WHICH IMPORTANT, Considiering That The User Will Get Only a static image of the report and will not beable to expand members.using the pivottable component ON A Web Server
Just as the other Office Web Components can be used without a user interface, you can create the PivotTable component as an in-memory object, connect to a data source, construct a report programmatically, and generate a GIF image of the current report or build Your owning the elements of the view. using the pivottable control on a web server might be a more Compelling Option Than Writing Mdx Queries, Using
ADO
MD
And Handcrafting a formatted result.
All the programmatic manipulation discussed so far can apply to using the PivotTable control on a server. The only new coding element you need to learn about is the ExportPicture method. This method is almost identical to the Chart component's ExportPicture method discussed in Chapter 3. To Export The Current Report To a Gif Image File, Write The Following Code:
Set fsotemp = createObject ("scripting.filesystemObject")
Strfilename = fsotemp.gettempname
Pivottable.ExportPicture Session ("StrtempFilePath") & strfilename, _
"gif", pivottable.maxheight, pivottable.maxwidth
Response.write "
Session ("Pivot" & session ("cntpivotimages)) = StrfileNamesession (" cntpivotimages) = session ("cntpivotimages" 1
The key line in this code block is the call to the ExportPicture method. An instance of FileSystemObject (a class from the Microsoft Scripting Runtime library) retrieves a temporary filename for the new GIF image, and then the code hands that filename plus a root path to the ExportPicture method as the first parameter. As with the Chart component, the second parameter must be "gif". The last two parameters indicate the height and width, but unlike programming the Chart component (which can scale its content to any size) YOU MUST USE The Pivottable Component's Maxheight and MaxWidth Properties To Avoid Cropping The Report Image.
Generating a report image on the server creates the most extreme version of a locked-down report. Users can view such an image on any platform and in any browser that can display a GIF image, but the report will not be interactive.
