'Resolution number: 15494 Created On: Oct 9 1997 'Problem Subject: Cycling through All Layers of a Table 'Problem Description: I have a table With many layers. Sometimes, the table has a 'lot of dimensions, And I lose track of which categories I've visited when using 'the Pivoting Trays. I also have problems with clicking the little arrows. Is 'there any way I can systematically view the layers of the table? 'Resolution Description: 'Save the following As a Script (i.e. With an .SBS extension). 'You may wish To add a button To your toolbar To trigger it. 'Then Select a table In your Output, And trigger the script. 'It will advance To the Next category of the lowest layer 'dimension, starting With the table's current state. 'When it reaches the last category, it will cycle back To 'the first category, And advance the Next higher layer 'dimension As well. If the script Is triggered enough 'times, All layers will be visited, And the table will 'be returned To its original state. '********************************************************** Sub Main ' Declare object variables used in this procedure. Dim objItem As ISpssItem ' A navigator item. Dim objPivotTable As PivotTable ' Pivot table. Dim bolFoundOutputDoc As Boolean Dim bolPivotSelected As Boolean 'Call GetSelectedTable to get the selected pivot table Call GetFirstSelectedPivot(objPivotTable, objItem, _ bolFoundOutputDoc, bolPivotSelected) If (bolFoundOutputDoc = False) Or (bolPivotSelected = False) Then 'either there wasn't an output doc or a pivot table wasn't selected Exit Sub End If ' defer screen update until we're done with the object objPivotTable.UpdateScreen = False '********************************************************** ' do something useful Call NextCategory(objPivotTable) '********************************************************** 'perform all screen updates at once objPivotTable.UpdateScreen = True ' Deactivate the pivot table and exit objItem.Deactivate End Sub Sub NextCategory(objPivotTable As PivotTable) 'Cycles through *all* categories of *all* layer dimensions. 'Advances to the next category of the bottom layer dimension. 'When it gets to the last category of that dimension, it wraps 'around to the first category, and it also advances to the next 'category of the next higher dimension, and so on. Dim objPivotMgr As ISpssPivotMgr Dim objDim As ISpssDimension Dim lngNumLayerDimensions As Long Dim lngNumCat() As Long Dim i As Long Dim index As Long Set objPivotMgr = objPivotTable.PivotManager lngNumLayerDimensions = objPivotMgr.NumLayerDimensions ReDim lngNumCat(lngNumLayerDimensions) 'first, construct an index for the current state For i = 0 To lngNumLayerDimensions - 1 Set objDim = objPivotMgr.LayerDimension(i) lngNumCat(i) = objDim.NumCategories 'the test is probably unnecessary; 'why would a Dimension have no levels? If lngNumCat(i) > 0 Then index = index * lngNumCat(i) + objDim.CurrentCategory End If Next 'advance to the next index index = index + 1 'translate back into categories For i = lngNumLayerDimensions - 1 To 0 Step -1 Set objDim = objPivotMgr.LayerDimension(i) 'if we skipped this dimension before, we'll skip it again If lngNumCat(i) > 0 Then objDim.CurrentCategory = index Mod lngNumCat(i) index = index \ lngNumCat(i) End If Next End Sub