Step 3: Setting Cell Values (R)

You can set cell values by row or by column using the SetCellsByRow or SetCellsByColumn method respectively. The SetCellsByRow method is limited to pivot tables with one column dimension and the SetCellsByColumn method is limited to pivot tables with one row dimension. To set cells for pivot tables with multiple row and column dimensions, use the SetCellValue method.

Example: Setting Cell Values by Row

spsspkg.StartProcedure("MyProcedure")
table = spss.BasePivotTable("Table Title",
                            "OMS table subtype")

rowdim=BasePivotTable.Append(table,Dimension.Place.row,"row dimension")
coldim=BasePivotTable.Append(table,Dimension.Place.column,"column dimension")

row_cat1=spss.CellText.String("first row")
row_cat2=spss.CellText.String("second row")
col_cat1=spss.CellText.String("first column")
col_cat2=spss.CellText.String("second column")

BasePivotTable.SetCategories(table,rowdim,list(row_cat1,row_cat2))
BasePivotTable.SetCategories(table,coldim,list(col_cat1,col_cat2))

BasePivotTable.SetCellsByRow(table,row_cat1,lapply(list(11,12),spss.CellText.Number))
BasePivotTable.SetCellsByRow(table,row_cat2,lapply(list(21,22),spss.CellText.Number))
spsspkg.EndProcedure()
  • This example also shows how to wrap the code for creating a pivot table in an spsspkg.StartProcedure-spsspkg.EndProcedure block. When creating a pivot table with the BasePivotTable class, you must always wrap the associated code in an spsspkg.StartProcedure-spsspkg.EndProcedure block. You can copy the code for this example to a syntax editor window, enclose it in a BEGIN PROGRAM R-END PROGRAM block and run it to produce a pivot table.
  • The SetCellsByRow method is called for each of the two categories in the row dimension.
  • The first argument to SetCellsByRow is a reference to the BasePivotTable object--in this example, the R variable table.
  • The second argument to the SetCellsByRow method is the row category for which values are to be set. The argument must be specified as a CellText object (one of CellText.Number, CellText.String, CellText.VarName, or CellText.VarValue). When setting row values for a pivot table with multiple row dimensions, you specify a list of category values for the first argument to SetCellsByRow, where each element in the list is a category value for a different row dimension.
  • The third argument to the SetCellsByRow method is a list of CellText objects (one of CellText.Number, CellText.String, CellText.VarName, or CellText.VarValue) that specify the elements of the row, one element for each column category in the single column dimension. The first element in the list will populate the first column category (in this case, col_cat1), the second will populate the second column category, and so on.
  • In this example, Number objects are used to specify numeric values for the cells. Values will be formatted using the table's default format. Instances of the BasePivotTable class have an implicit default format of GeneralStat. You can change the default format using the SetDefaultFormatSpec method, or you can override the default by explicitly specifying the format, as in: spss.CellText.Number(22,formatSpec.Correlation). See the topic CellText.Number Class (R) for more information.

    Note also that the R lapply function is used to create the list of CellText.Number objects that specify the cell values for each row.