Set number of decimals in pivot 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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
'Solution ID:  100007757 

'Title:
'Setting the number of Decimal digits In Each cell, Or In a column 

'Description:
'Q. 
'I'm using SPSS 7.5 or higher. 
'I would Like To Set the number of Decimal digits displayed In a Pivot Table. 
'For example, I would Like To display five digits In Each cell. 
'Moreover, I would Like To see more precision For my significances, say 9 digits. 
'Is there Any way To automate this, so that I don't have to change all the cell formats manually? 

'A. 
'Yes. Below Is a script which will make these changes For the first selected table. 
'Sub Main may be modified Or discarded; however, the SetDecimalDigits And SetDecimalDigitsInColumn subroutines, And the FindColumnLabel Function will probably Not need modification should you wish To use them In your own scripts, Or Call them from an Autoscript routine. 

'Open a New script window, delete everything In it, paste In the following, And save As SetDecimals.sbs: 

'Begin Description 
'SetDecimalDigits sets cell formats for all cells to the specified number of digits. 
'SetDecimalDigitsInColumn affects only a single column. 
'FindColumnLabel looks for a column by label; 
'it returns the column index, or -1 if no column is found. 
'End Description 

'--------------------------------------------------------------------------- 
' Demo of how to call SetDecimalDigits and SetDecimalDigitsInColumn 
'--------------------------------------------------------------------------- 
Sub Main 
Dim objPivot As PivotTable 
Dim objItem As ISpssItem 
'find and Activate a pivot table 
GetFirstSelectedPivot objPivot, objItem, True, True 
'postpone drawing until we're finished 
objPivot.UpdateScreen = False 

'*********************************************************************** 
'set all cell formats to 5 decimal digits 
SetDecimalDigits objPivot, 5 
'*********************************************************************** 

'*********************************************************************** 
'if a column contains "Sig." then set the decimal digits to 9 
SetDecimalDigitsInColumn objPivot, 9, FindColumnLabel(objPivot, "Sig.") 
'*********************************************************************** 

'do all the drawing at once 
objPivot.UpdateScreen = True 
'de-activate the table 
objItem.Deactivate 
'sometimes, the PivotTable Height and Width are not updated correctly 
're-activating the table will work around this 
objItem.ActivateTable 
objItem.Deactivate 
End Sub 
'--------------------------------------------------------------------------- 

'--------------------------------------------------------------------------- 
'Changes the number of Decimal Digits for *all* cells in a PivotTable 
'--------------------------------------------------------------------------- 
Sub SetDecimalDigits(objPivot As PivotTable, intDigits As Integer) 
Dim lngRow As Long, lngCol As Long 
Dim objDataCells As ISpssDataCells 

Set objDataCells = objPivot.DataCellArray 

With objDataCells 
For lngRow = 0 To .NumRows - 1 
For lngCol = 0 To .NumColumns - 1 
If Not IsNull (.ValueAt (lngRow, lngCol)) Then 
.HDecDigitsAt (lngRow, lngCol) = intDigits 
End If 
Next 
Next 
End With 

objPivot.Autofit 
End Sub 
'--------------------------------------------------------------------------- 

'--------------------------------------------------------------------------- 
'Changes the number of Decimal Digits for all cells in one column 
'(Use with FindColumnLabel to find column by label) 
'--------------------------------------------------------------------------- 
Sub SetDecimalDigitsInColumn(objPivot As PivotTable, intDigits As Integer, lngColumn As Long) 
Dim lngRow As Long 
Dim objDataCells As ISpssDataCells 

Set objDataCells = objPivot.DataCellArray 

With objDataCells 
If lngColumn >= 0 And lngColumn < .NumColumns Then 
For lngRow = 0 To .NumRows - 1 
If Not IsNull (.ValueAt (lngRow, lngColumn)) Then 
.HDecDigitsAt (lngRow, lngColumn) = intDigits 
End If 
Next 
Else 'invalid column 
Exit Sub 
End If 
End With 

objPivot.Autofit 
End Sub 

'--------------------------------------------------------------------------- 
'Returns the *first* column whose label contains the string 
'Returns -1 if the label is not found 
'--------------------------------------------------------------------------- 
Function FindColumnLabel (objPivot As PivotTable, strText As String) As Long 
Dim objColLabels As ISpssLabels ' Row Label array. 

Dim lngR As Long ' Loop Counter 
Dim lngC As Long ' Loop Counter 

If objPivot Is Nothing Then 
Debug.Print "PivotTable is Nothing!" 
Exit Function 
End If 

Set objColLabels = objPivot.ColumnLabelArray 

' ColLabelArray is a 2-dimensional array. Loop through the cells to 
' find the label text that matches the target text (strText) 

'returns -1 if the label is not found 
FindColumnLabel = -1 
For lngC = 0 To objColLabels.NumColumns - 1 
For lngR = 0 To objColLabels.NumRows - 1 
If InStr(objColLabels.ValueAt(lngR,lngC), strText) Then 
FindColumnLabel = lngC 
Exit Function 
End If 
Next 
Next 
End Function 
'---------------------------------------------------------------------------
Related pages

...

Navigate from here