How do the standard reports work?

Interviewer - Server > Configuration and customization > Customized Interviewing reports > How do 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 SampleTableName_ProjectName_HistoryTable which is created when the project is activated. Both tables are part of the project database.

The activation process creates a view called ProjectName_SampleOverview_Inner that joins these two tables. (the legacy view ProjectName_SampleOverview is still available). ProjectName_SampleOverview_Inner is used to right-join and remove sub-queries, resulting in improved performance over the legacy ProjectName_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 may 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_ProjectName.mdd file that is created in FMRoot\Users\UserName\Projects\ProjectName 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 may 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).