Ungroup a column in a pivot table

Solution ID: 
 100006382 
Product: 
 SPSS Base  
Version: 
 8.0 
O/S: 
 WINDOWS 
Question Type: 
 Syntax/Batch/Scripting 
Question Subtype: 
  


Title:
Ungroup a Column such As "Valid" In PivotTable Output 

Description:
Q. 
I am using SPSS 8.0 For Windows, And I don't want the column labelled "Valid" to 
be In my frequency tables. "Valid" Is a group label. Is there a script which will 
ungroup these rows? 

A. 
The subroutine UnGroupRows will search For a Text String (such As 
"Valid") In row labels. Another subroutine, UnGroupColumns, will 
search colunm labels. When the requested label Is found, it selects 
it And ungroups it, (much As a user would). 

To Call the subroutines from an autoscript routine: 
1. Paste the subroutines UnGroupRows And/Or UnGroupColumns 
into your Autoscript.sbs file (at the End Is fine). 
2. Right-click On the table And Choose Create/Edit Autoscript from 
the context menu. 
3. Choose Create/Edit Autoscript And To the subroutine which the 
cursor Is placed In, add 

Dim objPivot As PivotTable 
Set objPivot = objTable 
UnGroupRows objPivot, "Valid" 

Check the Edit->Options Dialog's Script tab, to be sure that the 
routine Is enabled: there should be an X In the CheckBox In front of 
the routine's name. 

Here Is how the modified autoscript routine might look: 
_______________________________________________________ 

Sub Frequencies_Table_Frequencies_Create(objTable As Object, _ 
objOutputDoc As Object, lngIndex As Long) 
'Autoscript 
'Trigger Event: Frequencies Table Creation after running Frequencies 
procedure. 

'Effects: Goes through the Row Labels and finds "Total" rows 
'and turns "Total" and associated data cells bold 
Dim bolSelection As Boolean 

Call SelectRowLabelsAndData(objTable, cTOTAL, bolSelection) 
If bolSelection = True Then 
objTable.TextStyle = 2 'make text bold 
End If 

'--- this part was added to hide columns labelled "Frequency" --- 
Dim objPivot As PivotTable 
Set objPivot = objTable 
UnGroupRows objPivot, "Valid" 
End Sub 

_______________________________________________________ 

If you are creating an Autoscript routine, scroll down Until you find 
directions, And paste the indicated portion. 
_______________________________________________________ 

The following Is a simple demonstration of how To Call HideColumn from 
another routine. It can be applied To every PivotTable In the Output; 
see the SPSS Script eXchange at 
http://www.spss.com/software/spss/base/Win75/SCPTXCHG.html 
For further examples. Save the Text below To a script named 
"HideColumn.SBS" ,Select a PivotTable, And Then run the script. 
_______________________________________________________ 

'***** Demo of UnGroupRows and UnGroupColumns ***** 
'--- save everything below to a file "HideColumn.SBS" 
' select a pivot table and run the script 
'***** do not copy Sub Main into an autoscript; see below ***** 

Sub Main 
Dim objPivot As PivotTable 
Dim objItem As ISpssItem 

Dim bolFoundOutput As Boolean, bolFoundPivot As Boolean 

GetFirstSelectedPivot objPivot, objItem, bolFoundOutput, bolFoundPivot 
If Not (bolFoundOutput And bolFoundPivot) Then 
Exit Sub 
End If 

UnGroupRows objPivot, "Valid" 

objItem.Deactivate 
End Sub 

'************************************************************** 
'TO USE IN AN AUTOSCRIPT: 
'Click on Create/Edit Autoscript 
'Call UnGroupRows and/or UnGroupColumns from the 
'routine it creates, as described in the notes. 
'************************************************************** 

'************************************************************** 
'--- paste everything below into Autoscript.sbs, at the end --- 
'************************************************************** 
' 
'HideRowLabelColumn and HideColumnLabelColumn 
'search for the requested label, and hide the 
'column in which it is found. 

Sub UnGroupRows(objPivot As PivotTable, strLabel As String) 
Dim objRowLabels As ISpssLabels 
Dim i As Integer, j As Integer 

Set objRowLabels = objPivot.RowLabelArray 
With objRowLabels 
For i = .NumRows - 1 To 0 Step -1 
For j = 0 To .NumColumns - 1 
If Not IsNull(.ValueAt(i,j)) Then 
If .ValueAt(i,j) = strLabel Then 
objPivot.UpdateScreen = False 
'15 is the minimum value for RowLabels 
.SelectLabelAt(i,j) 
objPivot.Ungroup 
objPivot.UpdateScreen = True 
Exit Sub 
End If 
End If 
Next 
Next 
End With 
End Sub 

Sub UnGroupColumns(objPivot As PivotTable, strLabel As String) 
Dim objColumnLabels As ISpssLabels 
Dim i As Integer, j As Integer 

Set objColumnLabels = objPivot.ColumnLabelArray 
With objColumnLabels 
For i = .NumRows - 1 To 0 Step -1 
For j = 0 To .NumColumns - 1 
If Not IsNull(.ValueAt(i,j)) Then 
If .ValueAt(i,j) = strLabel Then 
objPivot.UpdateScreen = False 
.SelectLabelAt(i,j) 
objPivot.Ungroup 
objPivot.UpdateScreen = True 
Exit Sub 
End If 
End If 
Next 
Next 
End With 
End Sub 

Created On: 05/24/1999