How the standard reports work
Data used
Interviewing reports use sample data and call history data to create reports useful to supervisors and interviewers. Sample data is stored in a sample table that is created during the preparation phase of the project, typically through the Participants activity. Contact history data is stored in a table called <sample_table_name>_<project_name>_HistoryTable which is created when the project is activated. Both tables are part of the project database.
The activation process creates a view called <project_name>_SampleOverview_Inner that joins these two tables. (the legacy view <project_name>_SampleOverview is still available). <project_name>_SampleOverview_Inner is used to right-join and remove sub-queries, resulting in improved performance over the legacy <project_name>_SampleOverview view.
This view is used by the contact history based reports. One row is added to this view each time an interviewer accesses a sample record (sample based reports directly use the sample table).
Since the view contains columns from the sample table, and these columns might vary between projects, the view for each project is different. However, the following columns are always present in any view and can be used in customized reports.
Columns
Active
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
Set to 1 while the sample management functions are running (Queue=ACTIVE).
ActivityStartTime
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: only SampleOverview_Inner
The value is same as the StartTime for the latest record in the history table for a specific sample.
AppointmentTime
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The most recent appointment time. This time is valid only if Queue=APPOINTMENT.
AppointmentTryCount
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The number of calls made after an appointment has been set.
CallOutcome
Comes from: HistoryTable
Available in SampleOverview and SampleOverview_Inner?: Yes
The result of the current access attempt.
CallOutcomeTmp
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The most recent contact outcome stored in the sample record.
Comments
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
Comments entered by interviewers when accessing the sample record.
Duration
Comes from: HistoryTable
Available in SampleOverview and SampleOverview_Inner?: Yes
The amount of time that the interviewer was accessing the record, including time spent conducting the interview.
InterviewerId
Comes from: HistoryTable
Available in SampleOverview and SampleOverview_Inner?: Yes
The login name (user name) of the interviewer who accessed the sample record.
InterviewMode
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The most recent interview method. Typically Phone, but can be set by the script as Phone, Web, or Personal.
PhoneNumber
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The telephone number.
PreviousQueue
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The queue that the record was in before this current access. When the last queue is Active, this is ignored and the last queue before that is used.
Queue
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The queue in which the sample record is currently held. This is empty for Web (inbound) interviews.
RecallTime
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The most recent recall time. This is the time for which a soft appointment (busy, no answer, answering machine) is set, and applies only if Queue=RECALL.
ReturnTime
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The time that the sample record was most recently returned by the interviewing system.
SampleId
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The Id for the sample record.
Serial
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The serial number for the data record associated with this sample record.
StartTime
Comes from: HistoryTable
Available in SampleOverview and SampleOverview_Inner?: Yes
The time that the interviewer started accessing the record.
TimeZone
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The time zone for the sample record.
TryCount
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: Yes
The number of calls made to this sample record. Excludes calls made after an appointment has been set.
UserId
Comes from: Sample
Available in SampleOverview and SampleOverview_Inner?: only SampleOverview_Inner
The value is same as the UserId for the latest record in the history table for a specific sample.
Creating the report
When a user selects a report from the Interviewing activity's menu, the activity performs the following tasks:
1: Creates a participants report document
The ReportName_srconfig.prd file is an XML file whose contents are based on the project’s catifields_<project_name>.mdd file that is created in FMRoot\Users\UserName\Projects\<project_name> when a project is activated. The .prd file contains this information:
▪The table name and connection string for accessing the sample and call history data. This table property is not used when TableDef is used.
▪A TableDef property that contains the report's data select statement. The data source for the select statement is derived from the Table property.
Site > Servers > [Server Name] > Applications > CatiReports > ApplicationSettings > [Report Name] > Table
The where clause is derived from the filters selected from user interface. All data source fields are selected, and data is filtered using the filters (the filters are translated by the ExprTrans component, allowing them to be used by SQL Server). This select statement is used to create a temporary view that is used by the Table Object Model.
▪A column called InterviewerId that stores the interviewer's name.
▪A column called CallOutcome to store the contact outcome of the most recent contact to the sample record. Categories for this column come from the SampleRecReturnCodes property in the project's Sample Management object in DPM.
▪One column for each sample field that might be used in tabulations and reports (that is, fields for which the Can Tabulate column on the Define Fields tab shows True in the Interviewing Options activity). The column names are the same as the field names.
2: Generates metadata
The Interviewing activity uses the Table Object Model to create reports. It creates a Tables Document and uses the Document.DataSet.Load method to load the data using the Sample Reporting MDSC, see
Document.DataSet.Load in the MDM Object Model Reference.
The Sample Reporting MDSC reads the .prd file and opens the data source of the report that is specified in .prd file. It then reads the columns named in the .prd file and creates a set of derived categorical variables, one for each column. The categories for each variable come either from the .prd file or from the sample and contact history records themselves. Derived variables have the same names as the columns except that they end with an underscore; for example, the Queue column generates a derived variable called Queue_ whose categories are the values of the PredefinedQueues property plus any additional queues in the sample records.
3: Runs the report
The Interviewing activity specifies the table to be generated using methods and properties in the Table Object Model, populates the table, and creates an HTML table and/or chart based on the specifications by using the HTML Export component in the Table Object Model (see
HTML tables export).
See also