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