Creating tables using a data management script

Here is a simple example that exports the Museum sample data set to an RDB database. It includes an OnAfterJobEnd Event section that sets up a simple crosstabulation (Age by Gender), a grid table for the Rating grid question, and a frequency table for the Education question. It then populates the tables, saves them as an .mtd file, and exports them (along with a chart for each table) to HTML:

Event(OnAfterJobEnd, "Create tables")
Dim TableDoc

Set TableDoc = dmgrJob.TableDocuments["RDBOutput"]

With TableDoc.Tables
' Create a table of Age by Gender
.AddNew("AgeByGender", "age * gender", "Age by Gender")

' Create a grid table for the Rating grid question
.AddNewGrid("RatingGrid", "rating", , "Rating Grid Table")

' Create a frequency table for the Education question
.AddNew("Education", "education", "Frequencies in full-time education")

' Add cumulative column percentages to the cell contents for the
' Education table
.Education.CellItems.AddNew(CellItemType.itCumColPercent)
End With

' Save the tables document
TableDoc.Save("C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\TablesFirstScript.mtd")

' Populate the tables
TableDoc.Populate()

' Export the tables
With TableDoc.Exports["mrHtmlExport"]
.Properties["Interactive"] = True
.Properties["LaunchApplication"] = True
.Properties["DisplayOption"] = "Table and Chart"
.Properties["LayoutStyle"] = "Frame Table of Contents"
.Properties["PresentationStyle"] = "Black and White"
.Export("C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\TablesFirstScript.htm")
End With

End Event

InputDataSource(Input)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrDataFileDsc; _
Location=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.ddf; _
Initial Catalog=C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Data\Data Collection File\museum.mdd"
End InputDataSource

OutputDataSource(RDBOutput)
ConnectionString = "Provider=mrOleDB.Provider.2; _
Data Source=mrRdbDsc2; _
Initial Catalog=''; _
Location='Provider=MSOLEDBSQL.1; _
Integrated Security=SSPI; _
Persist Security Info=False; _
Initial Catalog=TablesFirstScript; _
Data Source=localhost'; _
MR Init Project=TablesFirstScript"
MetaDataOutputName = "C:\Program Files\IBM\SPSS\DataCollection\7\DDL\Output\TablesFirstScript.mdd"
End OutputDataSource

Set TableDoc = dmgrJob.TableDocuments["RDBOutput"]

This line shows how in the OnAfterJobEnd Event section in a data management script you can use the dmrgJob.TableDocuments property to get access to a table document object for each output data source that was written using a read-enabled CDSC. This means that you cannot write scripts to create tables on a non-UNICOM Intelligence Data Model format output data source or a Quantum-format output data source (because the Quantum CDSC is write-only).

.Education.CellItems.AddNew(CellItemType.itCumColPercent)

This line uses an enumeration constant to add a new item of cell contents to the Education table. This is to draw attention to the fact that in a data management script, you can use the enumeration constants built into the Table Object Model, whereas in a standalone .mrs file, you must use the equivalent numeric value. For more information, see Creating a frequency table and defining cell contents.

The example shown is available as a sample file called TablesFirstScript.dms. To run this sample, you must have UNICOM Intelligence Reporter and access to a SQL Server installation and appropriate user access rights. Before running the sample, set up a SQL Server database called TablesFirstScript. If you run this script more than once, you must delete the records in the database created by the previous run, otherwise the serial numbers are duplicated and errors occur. When exporting charts, the HTML Tables Export component uses IBM SPSS Visualization as the default charting engine. The optional Microsoft Office Web Components (OWC) can also be used: you can download them from the Microsoft Download Center. You can change the sample to export tables only, by changing the DisplayOption export property to “Table Only”. For example:

.Properties["DisplayOption"] = "Table Only"

You usually create tables in the OnAfterJobEnd Event section. However, you can also access the Table Document in the same way in the OnJobStart Event section, OnNextCase Event section, and OnJobEnd Event section. If you decide to do this, do not populate or export the tables in these sections, because that might cause an error. Instead, save the tables in an .mtd file, and then open the .mtd file in the OnAfterJobEnd Event section, where you can then populate and export the tables. For an example of opening an .mtd file in a script, see Opening an existing table document.