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".