Introduction

When you are making reports/tables/charts etc. frequently there’s some need to nest a variable in order to create a breakdown of a variable. Thankfully Reports for Surveys/Professional makes this all very trivial and has nice support for nesting.

For example you can make tables like this:

its pretty easy to do. However there are times when nesting is a bit of a pain and it can be for any of the following reasons and more:

• Nesting can make the table harder to read because its making the top or side into a hierarchy, when the inside of the nest (age in this case) is a variable with lots of categories it can be pretty hard to tell whether which outside part you are looking at.
• You can’t sort values which split over multiple nests, for instance if you wanted to horizontally sort the above table into the order of the enjoyment, you will get the Male values sorted then the Female values sorted.
• Hide rules don’t work with nests if you want to hide empty columns and some of the inner variables items are empty they don’t hide nicely.
• Sometimes nesting doesn’t give you the charts you really want, and sometimes it really doesn’t give you what you would like when you export tables to Excel/PowerPoint/Word etc..

The Solution

There’s more than one way to solve this problem, but in the end it comes down to making a derived variable that combines the things you want to nest. You could do this inside Reports For Surveys by manually making a new variable, then adding each combination of categories in as expressions. Its going to take you a while if you have a lot of different variables to combine though.

At forgetdata we’ve had various issues come up over time where nesting just hasn’t been the solution. And we ended up writing a really generic function which just creates a new variable based on 2 variables and adds it into Dimensions Metadata. We figure its a universally useful bit of script so we’ll expose it to the masses, and it goes like this:

   1: ' Created by Forgetdata Limited. 2009

   2: ' Website: http://www.forgetdata.com

   3: ' Description:

   4: ' CombineVariables generates a new metadata variable based on each combination of categories of 2 other variables

   5: '

   6: ' Parameters:

   7: ' oMDMFields:  the MDM Fields Collection where the variables you want to combine are located

   8: ' var1: The name (its a string) of the first variable you want to combine

   9: ' var2: The name (also a string) of the second variable you want to combine

  10: ' oMDM: The owner MDM Document which is needed so that the function can call CreateVariable

  11: Function CombineVariables(oMDMFields, var1, var2, oMDM)

  12:  

  13:     Dim fldVar1, fldVar2, combVar,newVarName

  14:     set fldVar1 = oMDMFields[var1]

  15:     set fldVar2 = oMDMFields[var2]

  16:     

  17:     newVarName = var1 + "___" + var2

  18:     

  19:     if (NOT oMDMFields.Exist[newVarName] ) Then

  20:         Set combVar = oMDM.CreateVariable(newVarName, fldVar1.Label + " - " + fldVar2.Label)

  21:     else

  22:         Debug.Log("Combine variable "+ newVarName + "already exists, not creating")

  23:         Exit Function

  24:     end if

  25:     

  26:     combVar.HasCaseData = false

  27:     

  28:     const mtCategorical = &H0003

  29:     const sExpressions = &H0004

  30:     const mtCategory               = &H0000

  31:     combVar.DataType = mtCategorical

  32:     combVar.SourceType = sExpressions

  33:     

  34:     

  35:     Dim elem1,elem2,combElem

  36:     for each elem1 in fldVar1.Elements

  37:         for each elem2 in fldVar2.Elements

  38:             set combElem = combVar.Elements.AddNewElement(elem1.Name +"_"+elem2.Name, mtCategory)

  39:             combElem.Expression = var1 + ".ContainsAny({" + elem1.Name + "}) AND " + var2 + ".ContainsAny({" + elem2.Name + "})"

  40:             combElem.Label = elem1.Label + " - " + elem2.Label

  41:         next

  42:     next

  43:     oMDMFields.Add(combVar)

  44:     CombineVariables = combVar

  45:  

  46: End Function

You could add this function to any Dimensions DMS or TOM script in PASW Professional (or mrStudio) but this is some very simple example usage of this function:

   1: Dim oMDM

   2: Set oMDM = CreateObject("MDM.Document")

   3: oMDM.Open("C:\Program Files\SPSSInc\PASWDataCollection5.6\DDL\Data\Data Collection File\museum.mdd")

   4: CombineVariables(oMDM.Fields,"age","gender",oMDM)

   5: oMDM.Save()

If you store the variable into metadata as the example above, then you can use the variable inside PASW Reports for Surveys (SPSS Reporter/Desktop Reporter). The result of this is that there is a new variable called age__gender in your metadata. If you use this variable on a table you get this:

Finally

This really comes with no warranty or support, its just a case that we needed this function at quite a few times.

Forgetdata provides companies with Data Management, Data Warehousing and Reporting systems, some of these are based on SPSS PASW technologies to find out more about what we offer see our website.