Cycling through all layers of a table

 

Download as .sbs Shows as plain text

 

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
'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
Related pages

...

Navigate from here