Automatically Generating a Title

[Previous] [Next]

The PivotTable control offers a title bar at the top of the view, enabling you or the user to specify a meaningful title for the report. However, while users browse the data, it is often helpful to generate an automatic title based on what the user has included in the report. This title then can be suggested as the title of the report when saved, which is what occurs in the Save Report button's Click event handler described earlier.

Generating an automatic title is easy. The following code shows one method for doing so:

 Sub AutoTitlePivot(ptable)     Dim sTitle     Dim sSep     Dim sBy     Dim sFor     Dim sAnd     Dim ttl     Dim fset     Dim pview              sTitle = ""          ' Constant string tokens used to build the title     sSep = ", "     sBy = " by "     sFor = " for "     sAnd = " and "          ' Grab the active view     Set pview = ptable.ActiveView          ' Start by including the names of the totals used     If pview.DataAxis.Totals.Count > 0 Then         For Each ttl In pview.DataAxis.Totals             sTitle = sTitle & ttl.Caption & sSep         Next         sTitle = Left(sTitle, Len(sTitle) - Len(sSep))     Else         sTitle = "(No Totals)"     End If 'Some totals in the report              ' Next put " by <row fieldsets>"     If pview.RowAxis.FieldSets.Count > 0 Then         sTitle = sTitle & sBy         For Each fset In pview.RowAxis.FieldSets             sTitle = sTitle & fset.Caption & sSep         Next         sTitle = Left(sTitle, Len(sTitle) - Len(sSep))     End If 'Fieldset(s) on row axis     ' Next put " by <col fieldsets>"     If pview.ColumnAxis.FieldSets.Count > 0 Then         sTitle = sTitle & sBy         For Each fset In pview.ColumnAxis.FieldSets             sTitle = sTitle & fset.Caption & sSep         Next         sTitle = Left(sTitle, Len(sTitle) - Len(sSep))     End If 'Fieldset(s) on column axis          ' Finally, include any filters     If pview.FilterAxis.FieldSets.Count > 0 Then         sTitle = sTitle & sFor         For Each fset In pview.FilterAxis.FieldSets             sTitle = sTitle & fset.Caption & "=" & _                 fset.FilterMember.Caption & sAnd         Next         sTitle = Left(sTitle, Len(sTitle) - Len(sAnd))     End If 'Fieldset(s) on filter axis          pview.TitleBar.Caption = sTitle End Sub 'AutoTitlePivot() 

This method starts by declaring a number of variables and giving them default values. These variables are used as the separator values in the eventual title, and you can change these variables to use different separators. The code begins by concatenating all total captions in the view, separating them using the sSep variable. It then concatenates the fieldset captions on the Row axis if any exist. It continues by concatenating the Column axis fieldset captions and then concatenating any fieldset captions placed on the Filter axis, noting their current filter members. For example, if you use the Quick Pivot user interface to build a report that shows Store Sales by Promotions by Time filtered by Product, the automatic title generated by this method is "Store Sales by Promotions by Time for Product=All Products". If you change the filter field to show only the sales for the Drink product family, the last part of the title changes to "Product=Drink".



Programming Microsoft Office 2000 Web Components
Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)
ISBN: 073560794X
EAN: 2147483647
Year: 1999
Pages: 111
Authors: Dave Stearns

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net