Working with the ADO recordset
Sample Script Files: CellValues.mrs, CellValuesAndLabels.mrs
These example scripts are based on the Museum sample data set. See
Running the sample table scripts for information on running the example scripts.
The contents of a populated table are stored in an ADO recordset, which is accessible using the Table.CellValues property. This topic provides examples of working with the recordset. An ADO Recordset object consists of records and fields. In general terms the records represent the rows of the table and the fields represent the columns of the table.
The Table.CellValues property returns the recordset for a table. The property has a single parameter, which is designed for use in the future when the Table Object Model supports layers. It should always be set to zero, which is its default value.
The following mrScriptBasic example creates a simple table of biology by gender, populates it, and writes the recordset to a text file.
' Create the table of Biology by Gender
TableDoc.Tables.AddNew("Table1", "biology * gender", "Biology By Gender")
' Remove the column percentages default cell contents from the table
TableDoc.Table1.CellItems.Remove(1)
' Populate the tables
TableDoc.Populate()
Dim rs, fso, txtfile
' Prepare the text file
Set fso = CreateObject("Scripting.FileSystemObject")
Set txtfile = fso.CreateTextFile("CellValues.txt", True)
' Request the recordset for the table
Set rs = TableDoc.Table1.CellValues
' Write a heading to the text file
txtfile.WriteLine("Table1")
' Call the WriteRecordSet subroutine
WriteRecordSet(rs, txtfile)
txtfile.Close()
Sub WriteRecordSet(rs, txtfile)
Dim MyField
' Make sure we are at the first record
rs.MoveFirst()
' Loop through records until the end of file reached
While Not rs.EOF
For Each MyField In rs.Fields
'Write out field value
txtfile.Write(MyField.Value)
'Write out the tab character
txtfile.Write(mr.Tab)
Next
' Move to next line in output file
txtfile.WriteLine()
' Get next record
rs.MoveNext()
End While
End Sub
Here is the table:
And here are the contents of the text file:
R1:1 602 339 263
R2:1 169 81 88
R3:1 433 258 175
R4:1 - - -
Each line in the text file corresponds to a record in the recordset and each record in the recordset corresponds to an element on the side axis.
The first field in each record contains the row ID in the form R n : L, where n is the index of the corresponding element on the side axis and L is the layer number. When the table has one layer, the layer number is always 1 (because it is a one-based index, unlike the zero-based index that is used as the Layer parameter for the Table.CellValues property). The subsequent fields in the record correspond to the elements on the top axis. In this example there is one field for each element on the top axis because the table has one set of cell contents only, counts.
If we were to add a second set of cell contents to the table, there would be two fields for each element on the top axis. For example, here is the output for a table that is exactly the same as the previous one except that it shows column percentages with two decimal places as well as counts:
R1:1 602 100.00% 339 100.00% 263 100.00%
R2:1 169 28.07% 81 23.89% 88 33.46%
R3:1 433 71.93% 258 76.11% 175 66.54%
R4:1 - - - - - -
Notice that there are the same number of records in the recordset, but now each record has two fields for each element on the top axis, and each field represents one of the items of cell contents for the corresponding table cell. If we added a third set of cell contents, there would be three fields in each record for each element on the top axis, etc.
You can use the ADO recordset with the axis and element labels to create complete tables. For an example of doing this, see the CellValuesAndLabels.mrs sample script file.
Here is its output:
Top axis: Gender of respondent
Side axis: Holds biology qualification
Base Male Female
Base 602 339 263
Yes 169 81 88
No 433 258 175
Not answered - - -
This script uses the
ElementHeadings collection to retrieve the labels of the elements in the table and write them to the text file along with the data from the table. Using
ElementHeadings ensures that the element order reflects the order in the displayed table (taking into account any sorting or hidden elements). For further information on using the
ElementHeadings collection, see
Working with elements and element headings.
Requirements
UNICOM Intelligence Reporter
See also
