Across project reports
Across project reports always include both real and test data, and include the following reports:
▪Busy, Wait and Idle Time Across Projects
▪Interviewer Statistics Across Phone Projects
▪Interviewer Statistics Across Personal Projects
▪Overall Disposition Across Phone Projects
▪Overall Disposition Across Personal Projects
▪Sample Usage Across Phone Projects
▪Sample Usage Across Personal Projects
Background
The across project report views are pre-build at activation time and the views for each report are performed pre-aggregation. For example, the view for the Overall Disposition Across Phone Projects report is based on the union of the following query for each project:
SELECT '%PROJECT%' As Project,
CallOutcome,
Count(CallOutcome) As CallOutcomeWeight
FROM %TABLE%
WHERE Queue <> 'FRESH' AND (Test IS NULL OR Test <> 1)
GROUP BY CallOutcome
In this case, the final report is based on a .PRD that creates categoricals from Project and CallOutcome. The table is still "Project_ * CallOutcome_", but it uses sum cell items based on CallOutcomeWeight instead of simple counts.
Example
The Interviewer Statistics Across Phone Projects report provides a better example. The view template used to create the reporting view is as follows:
SELECT '%PROJECT%' As Project,
InterviewerId,
CallOutcome,
Count(CallOutcome) AS CallOutcomeWeight,
SUM(Duration) As Duration,
(Select count(IsLatestEntry)
from %OVERVIEW% STInner
where IsLatestEntry='1' and
Screener='Passed' and
STInner.InterviewerId = ST.InterviewerId) As PassedScreener,
(Select count(IsLatestEntry)
from %OVERVIEW% STInner
where IsLatestEntry='1' and
Screener='Failed' and
STInner.InterviewerId = ST.InterviewerId) As FailedScreener
FROM %OVERVIEW% ST
WHERE StartTime > DateAdd(hh, -8, GetUTCDATE()) AND
InterviewerId IS NOT NULL AND InterviewerId <> '__Web'
AND CallOutcome IS NOT NULL
GROUP BY InterviewerId, CallOutcome
The across project report views are generated by the CrossProjectViews component at activation time. The views are created by taking the template defined in the CrossProjectViewTemplate DPM property for each report and replacing the following arguments for each project:
%TABLE%
Replaced with the fully-qualified sample table name for the project
%HISTORY%
Replaced with the fully-qualified sample history table name for the project
%PROJECT%
Replaced with the project name
The queries for each project are combined via UNION ALL in the final view. Each view is added to the DPM database, mrInterviewData, as <ReportName>_CrossProjectView.
The template that is used to create the view and the script that is used to generate the report are stored in DPM under:
Servers\<site>\Applications\CatiReports\ApplicationSettings
When examining the table specification for the Interviewer Statistics Across Phone Projects report, you will notice that sum elements are used instead of simple counts, and that expressions are often used on the sum element to filter what goes into the sum.
For example, the Appointment count for each Interviewer is produced using the following specification:
"Appointment '" + MDMTexts.Variables["Appointments"].Label + "' sum(CallOutcomeWeight, 'CallOutcome_={Appointment}'),"
Defining the number of projects that are used in the reports
TheCrossProjectMaxCount DPM setting sets the maximum number of projects that are included in the across project reports. The default value is 256.
The property can be updated in DPM:
Site > Servers > [Server Name] > Application > CatiReports > [Cross Report Name] > CrossProjectMaxCount
Summary
The advantages of this approach is that filtering and pre-aggregation is performed in the database, while the final report is produced using the tabulation components. Better performance is delivered while still allowing the report to be easily customized by changing the report script. The other advantage is that all of the reports are available from a single user interface.
The main disadvantage of this approach is that filtering via the report is limited to the columns being grouped upon. For example, with the Interviewer Statistics Across Phone Projects report, it would be possible to filter the report on Project, Interviewer, or CallOutcome, but not date/time. The ability to pass custom where clauses to the view is currently not possible; the date/time filter needs to be hard-coded in the view.
Another disadvantage is that is more difficult to construct reports. Instead of simply producing a table from a standard set of variables, you also need to think about the view that will be used.
See also