Unica Marketing Operations Reports Specifications
Folder: Affinium Plan

Overdue Milestone Report
A report of budget, revenue, and ROI information for each campaign and each cell within the campaign.
Program Tasks and Milestones
A list of all milestones for the program selected in the parameter field.
Project On Time Analysis
List of all projects that were done early or were overdue.
Project Performance Crosstab (Custom)
Crosstab of project performance metrics including leads passed, revenue, and ROI, aggregated over initiative and project type vs. business units.
Project Performance Summary
Project listing with standard attributes.
Project Performance Summary (Custom)
List of projects grouped by initiative, with budget, cost, revenue, response rate and lead generation performance data.
Project Tasks
A list of all tasks and their status for the specified project.
Resource Task Load
A list of tasks assigned to selected users. The report returns tasks for each project that falls into the specified date range.
Resource Utilization Summary
A summary of resource utilization percentages by user. Select start and end dates to specify a date range for the report.
System Programs
Count of all Programs in the system by template type and status
System Projects
A count of all projects in the system by status and template type.
System Users
A list of all users in the system along with their status and most recent activity.
Task On Time Analysis
Vendor Spend Summary
Displays total invoiced expenses for specified vendors in specified date range, including breakouts for paid, approved and received expenses.


Overdue Milestone Report
Description:A report of budget, revenue, and ROI information for each campaign and each cell within the campaign. 
# Queries:1
Layout:1 list(s)
Report Variables:LateTask (boolean): [Report Query].[Days Late] >= 0
ReportRunType (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Overdue Milestone Report.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(list)
Total: 14Filters:
[Days Late]>0
[MarketingOperationsBV].[Task].[End Date] >= ?Start Date?
[MarketingOperationsBV].[Task].[End Date] <= ?End Date?
[MarketingOperationsBV].[Task].[Milestone] is not null
[MarketingOperationsBV].[Task].[Status] in ?Task Status?
[MarketingOperationsBV].[Project].[Status] in ?Project Status?
 
 
Days Lateif ([Days Diff] =0) then (null) else ([Days Diff])  
End Date[MarketingOperationsBV].[Task].[End Date] 
Task Name[MarketingOperationsBV].[Task].[Task Name] 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End] 
Notes[MarketingOperationsBV].[Task].[Notes] 
Statusif([MarketingOperationsBV].[Task].[Status] is missing) then ('Pending') else ([MarketingOperationsBV].[Task].[Status])  
Count(Milestone)[Milestone]/ count 
User Full Name - First Last[MarketingOperationsBV].[Project Owner].[User Full Name - First Last] 
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Task Number[MarketingOperationsBV].[Task].[Task Number] 
Milestone[MarketingOperationsBV].[Task].[Milestone] 
Owner([MarketingOperationsBV].[Task Member].[User Full Name - First Last]) 
Project Code[MarketingOperationsBV].[Project].[Project Code] 
Days Diff_days_between([Forecast/Actual End],[End Date]) 
Task Status Prompt Query
(prompt)
Total: 1  
Task Status[MarketingOperationsBV].[Task].[Status] 
Project Status Prompt Query
(prompt)
Total: 1  
Project Status[MarketingOperationsBV].[Project Status].[Status] 


Program Tasks and Milestones
Description:A list of all milestones for the program selected in the parameter field. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Variance End Dates (string): IF ([Report Query].[Variance - End Dates] >= 0) THEN ('EARLY') ELSE IF ([Report Query].[Variance - End Dates] < 0) THEN ('LATE')
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Program Tasks and Milestones.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(list)
Total: 15Filters:
[MarketingOperationsBV].[Program Project].[Program ID] = ?Program Select?
[MarketingOperationsBV].[Task].[Milestone] in ?Milestones?
 
 
Task Number[MarketingOperationsBV].[Task].[Task Number]/ count 
Task Name[MarketingOperationsBV].[Task].[Task Name] 
Forecast/Actual Start[MarketingOperationsBV].[Task].[Forecast/Actual Start] 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End] 
Start Date[MarketingOperationsBV].[Task].[Start Date] 
End Date[MarketingOperationsBV].[Task].[End Date]/ maximum 
Notes[MarketingOperationsBV].[Task].[Notes] 
Milestone[MarketingOperationsBV].[Task].[Milestone] 
Program[MarketingOperationsBV].[Program].[Program Name] 
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Variance - End Datesif ([Days Diff] =0) then (0) else ([Days Diff])  
Forecast/Actual Effort[MarketingOperationsBV].[Task].[Forecast/Actual Effort String] 
Effort[MarketingOperationsBV].[Task].[Effort String] 
Project Name[MarketingOperationsBV].[Project].[Project Name] 
Days Diff_days_between([End Date],[Forecast/Actual End]) 
Select Programs Query
(prompt)
Total: 2  
Program ID[MarketingOperationsBV].[Program].[Program ID] 
Program Name[MarketingOperationsBV].[Program].[Program Name] 
Milestone Type Query
(prompt)
Total: 1  
Milestone[MarketingOperationsBV].[Task].[Milestone] 


Project On Time Analysis
Description:List of all projects that were done early or were overdue. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Variable1 (boolean): [Query1].[Variance] >= 0
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Project On Time Analysis.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 7Filters:
[MarketingOperationsBV].[Project].[Status]='Completed'
maximum([MarketingOperationsBV].[Task].[End Date]) is not null
maximum([MarketingOperationsBV].[Task].[Forecast/Actual End]) is not null
maximum([MarketingOperationsBV].[Task].[Forecast/Actual End]) between ?Start Date? and ?End Date?
 
 
Project Code[MarketingOperationsBV].[Project].[Project Code] 
Project Name[MarketingOperationsBV].[Project].[Project Name] 
End Date[MarketingOperationsBV].[Task].[End Date]maximum 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End]maximum 
Variance_days_between([End Date],[Forecast/Actual End])/ average 
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Project Description[MarketingOperationsBV].[Project].[Project Description] 


Project Performance Crosstab (Custom)
Description:Crosstab of project performance metrics including leads passed, revenue, and ROI, aggregated over initiative and project type vs. business units. 
# Queries:1
Layout:1 crosstabs(s)
Report Variables:ReportTypeRun (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Project Performance Crosstab (Custom).jpg
Query NameData ItemExpressionAgg/
Rollup Agg
ProjectPromptQuery
(prompt)
Total: 2  
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Project Name[MarketingOperationsBV].[Project].[Project Name (Code)] 
Project_Owner_Query
(prompt)
Total: 2  
User ID[MarketingOperationsBV].[Project Owner].[User ID] 
User Full Name - First Last[MarketingOperationsBV].[Project Owner].[User Full Name - First Last] 
Business_Unit_Query
Total: 1  
Business Unit[MarketingOperationsBV].[Project Custom Attributes].[Business Unit] 
Initiative_Query
Total: 1  
Initiative[MarketingOperationsBV].[Project Custom Attributes].[Initiative] 
Crosstab_Query
(crosstab)
Total: 9Filters:
[MarketingOperationsBV].[Project Owner].[User ID] in ?Project_Owner_Prompt?
[MarketingOperationsBV].[Project].[Status] in (?Project_Status_Prompt?)
[MarketingOperationsBV].[Project].[Project ID] in ?ProjectPrompt?
[MarketingOperationsBV].[Project].[Template Name] in (?Project_Type_Prompt?)
[MarketingOperationsBV].[Project].[Project Start Date] in_range ?Target_Date_Prompt? OR [MarketingOperationsBV].[Project].[Project End Date] in_range ?Target_Date_Prompt? OR ([MarketingOperationsBV].[Project].[Project Start Date] <= ?Target_Date_Prompt? AND [MarketingOperationsBV].[Project].[Project End Date] >= ?Target_Date_Prompt?)
 
 
Business UnitCASE WHEN [MarketingOperationsBV].[Project Custom Attributes].[Business Unit] is missing THEN '(No Value)' ELSE [MarketingOperationsBV].[Project Custom Attributes].[Business Unit] END  
ROI([MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Revenue]-[MarketingOperationsBV].[Project Budget].[Actual Total])/[MarketingOperationsBV].[Project Budget].[Actual Total] calculated/ calculated 
Total Revenue[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Revenue]total/ calculated 
Total Leads Generated (Actual)[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Leads Generated (Actual)]total/ calculated 
InitiativeCASE WHEN [MarketingOperationsBV].[Project Custom Attributes].[Initiative] is missing THEN '(No Value)' ELSE [MarketingOperationsBV].[Project Custom Attributes].[Initiative] END  
Template Displayif ([MarketingOperationsBV].[Project].[Template Display] ='') then ( [MarketingOperationsBV].[Project].[Template Instance Display]) else ([MarketingOperationsBV].[Project].[Template Display]) / automatic 
aggregate(Initiative)aggregate(currentMeasure within detail [Initiative]) 
aggregate(Template Display)aggregate(currentMeasure within set [Template Display]) 
aggregate(Business Unit)aggregate(currentMeasure within detail [Business Unit]) 
ProjectTypeQuery
(prompt)
Total: 2  
Template Name[MarketingOperationsBV].[Project Template].[Template Name] 
Template Displayif ([MarketingOperationsBV].[Project].[Template Display] ='') then ( [MarketingOperationsBV].[Project].[Template Instance Display]) else ([MarketingOperationsBV].[Project].[Template Display])  
ProjectStatusQuery
(prompt)
Total: 1  
Status[MarketingOperationsBV].[Project Status].[Status] 


Project Performance Summary
Description:Project listing with standard attributes. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportTypeRun (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Project Performance Summary.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 8Filters:
[MarketingOperationsBV].[Project].[Project ID] in (?ProjectPrompt?)
[MarketingOperationsBV].[Project Owner].[User ID] in (?Project_Owner_Prompt?)
[MarketingOperationsBV].[Project].[Template Name] in (?Project_Type_Prompt?)
[MarketingOperationsBV].[Project].[Status] in (?Project_Status_Prompt?)
[MarketingOperationsBV].[Project].[Project Start Date] in_range ?Target_Date_Prompt? OR [MarketingOperationsBV].[Project].[Project End Date] in_range ?Target_Date_Prompt? OR ([MarketingOperationsBV].[Project].[Project Start Date] <= ?Target_Date_Prompt? AND [MarketingOperationsBV].[Project].[Project End Date] >= ?Target_Date_Prompt?)
 
 
Budget Total[MarketingOperationsBV].[Project Budget].[Budget Total]total/ total 
Actual Total[MarketingOperationsBV].[Project Budget].[Actual Total]total/ total 
Project Name (Code)[MarketingOperationsBV].[Project].[Project Name (Code)] 
Status[MarketingOperationsBV].[Project].[Status] 
Project Start Date[MarketingOperationsBV].[Project].[Project Start Date] 
Project End Date[MarketingOperationsBV].[Project].[Project End Date] 
Forecast Total[MarketingOperationsBV].[Project Budget].[Forecast Total]total/ total 
Project URL[MarketingOperationsBV].[Project].[Project URL] 
ProjectPromptQuery
(prompt)
Total: 2  
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Project Name[MarketingOperationsBV].[Project].[Project Name (Code)] 
Project_Owner_Query
(prompt)
Total: 2  
User ID[MarketingOperationsBV].[Project Owner].[User ID] 
User Full Name - First Last[MarketingOperationsBV].[Project Owner].[User Full Name - First Last] 
Project_Type_Querry
(prompt)
Total: 2  
Template Name[MarketingOperationsBV].[Project Template].[Template Name] 
Template Display[MarketingOperationsBV].[Project Template].[Template Display] 
Project_Status_Query
(prompt)
Total: 1  
Status[MarketingOperationsBV].[Project Status].[Status] 


Project Performance Summary (Custom)
Description:List of projects grouped by initiative, with budget, cost, revenue, response rate and lead generation performance data. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportTypeRun (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Project Performance Summary (Custom).jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 20Filters:
[MarketingOperationsBV].[Project].[Project ID] in (?ProjectPrompt?)
[MarketingOperationsBV].[Project Owner].[User ID] in (?Project_Owner_Prompt?)
[MarketingOperationsBV].[Project Custom Attributes].[Business Unit] in (?Business_Unit_Prompt?)
[MarketingOperationsBV].[Project Custom Attributes].[Initiative] in (?Initiative_Prompt?)
[MarketingOperationsBV].[Project].[Template Name] in (?Project_Type_Prompt?)
[MarketingOperationsBV].[Project].[Status] in (?Project_Status_Prompt?)
[MarketingOperationsBV].[Project].[Project Start Date] in_range ?Target_Date_Prompt? OR [MarketingOperationsBV].[Project].[Project End Date] in_range ?Target_Date_Prompt? OR ([MarketingOperationsBV].[Project].[Project Start Date] <= ?Target_Date_Prompt? AND [MarketingOperationsBV].[Project].[Project End Date] >= ?Target_Date_Prompt?)
 
 
Business Unit[MarketingOperationsBV].[Project Custom Attributes].[Business Unit] 
Budget Total[MarketingOperationsBV].[Project Budget].[Budget Total]total/ total 
Actual Total[MarketingOperationsBV].[Project Budget].[Actual Total]total/ total 
Project Name (Code)[MarketingOperationsBV].[Project].[Project Name (Code)] 
Project URL[MarketingOperationsBV].[Project].[Project URL] 
Status[MarketingOperationsBV].[Project].[Status] 
Project Start Date[MarketingOperationsBV].[Project].[Project Start Date] 
Project End Date[MarketingOperationsBV].[Project].[Project End Date] 
Product Family[MarketingOperationsBV].[Project Custom Attributes].[Product Family] 
Forecast Total[MarketingOperationsBV].[Project Budget].[Forecast Total]total/ total 
Segment[MarketingOperationsBV].[Project Custom Attributes].[Segment] 
Total Revenue[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Revenue]total/ total 
ResponseRate Actual[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[ResponseRateActual]total 
Total Leads Generated (Actual)[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Leads Generated (Actual)]total/ total 
Total Leads Generated (Target)[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Leads Generated (Target)]total/ total 
ROI([MarketingOperationsBV].[Project Performance Summary Custom Metrics].[Total Revenue]-[MarketingOperationsBV].[Project Budget].[Actual Total])/[MarketingOperationsBV].[Project Budget].[Actual Total] calculated/ calculated 
Total Leads Generated Variance[Total Leads Generated (Actual)] - [Total Leads Generated (Target)]/ total 
ResponseRate Target[MarketingOperationsBV].[Project Performance Summary Custom Metrics].[ResponseRateTarget]total 
Response Rate Variance[ResponseRate Actual] - [ResponseRate Target] 
Initiative[MarketingOperationsBV].[Project Custom Attributes].[Initiative] 
ProjectPromptQuery
(prompt)
Total: 2  
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Project Name[MarketingOperationsBV].[Project].[Project Name (Code)] 
Project_Owner_Query
(prompt)
Total: 2  
User ID[MarketingOperationsBV].[Project Owner].[User ID] 
User Full Name - First Last[MarketingOperationsBV].[Project Owner].[User Full Name - First Last] 
Business_Unit_Query
(prompt)
Total: 1  
Business Unit[MarketingOperationsBV].[Project Custom Attributes].[Business Unit] 
Initiative_Query
(prompt)
Total: 1  
Initiative[MarketingOperationsBV].[Project Custom Attributes].[Initiative] 
Project_Type_Query
(prompt)
Total: 2  
Template Name[MarketingOperationsBV].[Project Template].[Template Name] 
Template Display[MarketingOperationsBV].[Project Template].[Template Display] 
Project_Status_Query
(prompt)
Total: 1  
Status[MarketingOperationsBV].[Project Status].[Status] 


Project Tasks
Description:A list of all tasks and their status for the specified project. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Due Date Variance (string): IF ([Report Query].[Due Date Variance] >= 0) THEN ('EARLY') ELSE IF ([Report Query].[Due Date Variance] < 0) THEN ('LATE')
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Project Tasks.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(list)
Total: 17Filters:
[MarketingOperationsBV].[Task].[Project ID]=?Project_id?
 
 
Task Number[MarketingOperationsBV].[Task].[Task Number] 
Task Name[MarketingOperationsBV].[Task].[Task Name] 
Forecast/Actual Start[MarketingOperationsBV].[Task].[Forecast/Actual Start] 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End] 
Actual Duration[MarketingOperationsBV].[Task].[Actual Duration String] 
Start Date[MarketingOperationsBV].[Task].[Start Date] 
End Date[MarketingOperationsBV].[Task].[End Date] 
Duration[MarketingOperationsBV].[Task].[Duration String] 
Notes[MarketingOperationsBV].[Task].[Notes] 
Milestone[MarketingOperationsBV].[Task].[Milestone] 
Stage Name[MarketingOperationsBV].[Stage].[Stage Name] 
Due Date Varianceif ([Days Diff] =0) then (0) else ([Days Diff])  
Project Code[MarketingOperationsBV].[Project].[Project Code] 
Project Name[MarketingOperationsBV].[Project].[Project Name] 
User Full Name - First Last[MarketingOperationsBV].[Task Member].[User Full Name - First Last] 
Stage Number[MarketingOperationsBV].[Stage].[Stage Number] 
Days Diff_days_between([End Date],[Forecast/Actual End]) 
Project Prompt Query
(prompt)
Total: 2  
Project ID[MarketingOperationsBV].[Task].[Project ID] 
Project Name (Code)[MarketingOperationsBV].[Project].[Project Name (Code)] 


Resource Task Load
Description:A list of tasks assigned to selected users. The report returns tasks for each project that falls into the specified date range. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportTypeRun (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Resource Task Load.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(list)
Total: 17Filters:
[MarketingOperationsBV].[Task Member].[User Full Name - First Last] <> null
[MarketingOperationsBV].[Task Member].[User ID] in (?User_Selection?)
[MarketingOperationsBV].[Task].[Status] in (?Status_Selection?)
[MarketingOperationsBV].[Project].[Template Name] in (?Type_Selection?)
[MarketingOperationsBV].[Project].[Project ID] in (?Project_Prompt?)
[MarketingOperationsBV].[Project].[Project Start Date] in_range ?Target_Date_Prompt? OR [MarketingOperationsBV].[Project].[Project End Date] in_range ?Target_Date_Prompt? OR ([MarketingOperationsBV].[Project].[Project Start Date] <= ?Target_Date_Prompt? AND [MarketingOperationsBV].[Project].[Project End Date] >= ?Target_Date_Prompt?)
 
 
User Full Name - First Last[MarketingOperationsBV].[Task Member].[User Full Name - First Last] 
Task Name' ' + [MarketingOperationsBV].[Task].[Task Name] 
Project Code' (' + [MarketingOperationsBV].[Task].[Project Code] + ')' 
Project Name[MarketingOperationsBV].[Task].[Project Name] 
Project URL[MarketingOperationsBV].[Task].[Project URL] 
Start Date[MarketingOperationsBV].[Task].[Start Date] 
Forecast/Actual Start[MarketingOperationsBV].[Task].[Forecast/Actual Start] 
End Date[MarketingOperationsBV].[Task].[End Date] 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End] 
StatusIF ([MarketingOperationsBV].[Task].[Status] is missing) THEN ('Pending') ELSE ([MarketingOperationsBV].[Task].[Status])  
% Complete[MarketingOperationsBV].[Task].[% Complete] 
Notes[MarketingOperationsBV].[Task].[Notes] 
Task Number[MarketingOperationsBV].[Task].[Task Number] 
ConditionCASE WHEN [Forecast/Actual Start] > [Start Date] THEN 'Late' WHEN [Forecast/Actual End] > [End Date] THEN 'Overdue' WHEN [Forecast/Actual Start] > [Start Date] and [Forecast/Actual End] > [End Date] THEN 'Overdue' ELSE '' END  
Task Count per Usercount([Task Name] for [User Full Name - First Last])notApplicable 
Project Start Date[MarketingOperationsBV].[Project].[Project Start Date] 
Project End Date[MarketingOperationsBV].[Project].[Project End Date] 
User Selection Query
(prompt)
Total: 2  
User ID[MarketingOperationsBV].[User].[User ID] 
User Full Name - First Last[MarketingOperationsBV].[User].[User Full Name - First Last] 
Template Selection Query
(prompt)
Total: 2  
Template Name[MarketingOperationsBV].[Project Template].[Template Name] 
Template Instance Display[MarketingOperationsBV].[Project Template].[Template Instance Display] 
Project_Query
(prompt)
Total: 2  
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Project Name (Code)[MarketingOperationsBV].[Project].[Project Name (Code)] 
Status_Query
(prompt)
Total: 1  
Status[MarketingOperationsBV].[Task].[Status] 


Resource Utilization Summary
Description:A summary of resource utilization percentages by user. Select start and end dates to specify a date range for the report. 
# Queries:1
Layout:2 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Resource Utilization Summary.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 7Filters:
[MarketingOperationsBV].[Task Member].[User Full Name - First Last] is not missing
[MarketingOperationsBV].[Project].[Project Start Date] >= ?Begin_Date?
[MarketingOperationsBV].[Project].[Project End Date] <= ?End_Date?
[MarketingOperationsBV].[User].[User ID] in (?People?)
[MarketingOperationsBV].[Users Roles].[role_id] in ?Role?
[MarketingOperationsBV].[Task Status].[Status Code]<>'SKIPPED'
 
 
No of Tasks Assignedcount( distinct [MarketingOperationsBV].[Task Member].[Task ID]) 
Total Efforttotal([MarketingOperationsBV].[Task].[Effort]) 
% Utilization[Total Effort]/[No of Working Days] 
No of Working Days[No of Days] - [Weekend_Days] 
User Full Name - First Last[MarketingOperationsBV].[Task Member].[User Full Name - First Last] 
No of Days_days_between(?End_Date?,?Begin_Date?)+1 
Weekend_Daysfloor((_days_between(?End_Date?,?Begin_Date?)+_day_of_week(?Begin_Date?,1))/7)*2 - if (_day_of_week(?Begin_Date?,1)=7) then (1) else (0) + if (_day_of_week(?End_Date?,1) = 6) then (1) else (0)  
Master_Query1
(list)
Total: 3Filters:
[MarketingOperationsBV].[Project].[Project Start Date] >= ?Begin_Date?
[MarketingOperationsBV].[Project].[Project End Date] <= ?End_Date?
 
 
No of Working Days[No of Days] - [Weekend_Days] 
No of Days_days_between(?End_Date?,?Begin_Date?)+1 
Weekend_Daysfloor((_days_between(?End_Date?,?Begin_Date?)+_day_of_week(?Begin_Date?,1))/7)*2 - if (_day_of_week(?Begin_Date?,1)=7) then (1) else (0) + if (_day_of_week(?End_Date?,1) = 6) then (1) else (0)  
People_Query
Total: 2Filters:
[MarketingOperationsBV].[Users Roles].[role_id] = ?Role?
 
 
User ID[MarketingOperationsBV].[User].[User ID] 
User Full Name - First Last[MarketingOperationsBV].[User].[User Full Name - First Last] 


System Programs
Description:Count of all Programs in the system by template type and status 
# Queries:1
Layout:1 crosstabs(s)
Report Variables:ReportRunType (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/System Programs.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(crosstab)
Total: 5  
Count of Programscount([MarketingOperationsBV].[Program].[Program ID])/ total 
Program StatusIf ([MarketingOperationsBV].[Program].[Program Status]='IN_PROGRESS') THEN ('In Progress') ELSE IF ([MarketingOperationsBV].[Program].[Program Status]= 'NOT_STARTED') THEN ('Not Started') ELSE IF ([MarketingOperationsBV].[Program].[Program Status]= 'CANCELLED') THEN ('Cancelled') ELSE IF ([MarketingOperationsBV].[Program].[Program Status]= 'ON_HOLD') THEN ('On Hold') ELSE IF ([MarketingOperationsBV].[Program].[Program Status]= 'COMPLETED') THEN ('Completed')ELSE ([MarketingOperationsBV].[Program].[Program Status])  
Template NameIF ([MarketingOperationsBV].[Program].[Template Display] is NULL or [MarketingOperationsBV].[Program].[Template Display]='') THEN ([MarketingOperationsBV].[Program].[Template Instance Display]) ELSE ([MarketingOperationsBV].[Program].[Template Display])  
aggregate(Template Name)aggregate(currentMeasure within detail [Template Name]) 
aggregate(Program Status)aggregate(currentMeasure within detail [Program Status]) 


System Projects
Description:A count of all projects in the system by status and template type. 
# Queries:1
Layout:1 crosstabs(s)
Report Variables:ReportRunType (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/System Projects.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Report Query
(crosstab)
Total: 5  
Status[MarketingOperationsBV].[Project].[Status] 
Count of Projectscount([MarketingOperationsBV].[Project].[Project ID])/ total 
Template DisplayIF ([MarketingOperationsBV].[Project].[Template Display] ='' OR [MarketingOperationsBV].[Project].[Template Display] is NULL) THEN ([MarketingOperationsBV].[Project].[Template Instance Display]) ELSE ([MarketingOperationsBV].[Project].[Template Display])  
aggregate(Template Display)aggregate(currentMeasure within detail [Template Display]) 
aggregate(Status)aggregate(currentMeasure within detail [Status]) 


System Users
Description:A list of all users in the system along with their status and most recent activity. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/System Users.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 8  
First Name[MarketingOperationsBV].[User].[First Name] 
Last Name[MarketingOperationsBV].[User].[Last Name] 
Email Address[MarketingOperationsBV].[User].[Email Address] 
Last Modified Date[MarketingOperationsBV].[User].[Last Modified Date] 
Login ID[MarketingOperationsBV].[User].[Login ID] 
Status Code[MarketingOperationsBV].[User].[Status Code] 
Count(User ID)[User ID]/ count 
User ID[MarketingOperationsBV].[User].[User ID] 


Task On Time Analysis
Description: 
# Queries:1
Layout:1 list(s)
Report Variables:ReportRunType (string): ReportOutput()
Late Task (boolean): [Query1].[Days Late] > 0
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Task On Time Analysis.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 9Filters:
[Days Late]>0 or [Days Late]<0
[MarketingOperationsBV].[Task].[Forecast/Actual End] >= ?Start Date?
[MarketingOperationsBV].[Task].[Forecast/Actual End] <= ?End Date?
 
 
Project Name[MarketingOperationsBV].[Project].[Project Name] 
Project Code[MarketingOperationsBV].[Project].[Project Code] 
Days Lateif ([Days Diff] =0) then (null) else ([Days Diff]) / average 
End Date[MarketingOperationsBV].[Task].[End Date] 
Project ID[MarketingOperationsBV].[Project].[Project ID] 
Forecast/Actual End[MarketingOperationsBV].[Task].[Forecast/Actual End] 
User Full Name - First Last[MarketingOperationsBV].[Task Member].[User Full Name - First Last] 
Task Name[MarketingOperationsBV].[Task].[Task Name] 
Days Diff_days_between([MarketingOperationsBV].[Task].[Forecast/Actual End], [MarketingOperationsBV].[Task].[End Date]) 


Vendor Spend Summary
Description:Displays total invoiced expenses for specified vendors in specified date range, including breakouts for paid, approved and received expenses. 
# Queries:1
Layout:1 list(s)
Report Variables:ReportTypeRun (string): ReportOutput()
Report Language1 (locale): ReportLocale()
reports/Affinium Plan/Vendor Spend Summary.jpg
Query NameData ItemExpressionAgg/
Rollup Agg
Query1
(list)
Total: 4Filters:
[MarketingOperationsBV].[Invoice].[Vendor Display] in (?Vendor_Prompt?)
[MarketingOperationsBV].[Invoice].[Due Date] in_range ?Invoice_Date_Range? OR [MarketingOperationsBV].[Invoice].[Paid Date] in_range ?Invoice_Date_Range?
 
 
Vendor Display[MarketingOperationsBV].[Invoice].[Vendor Display] 
Outstandingif([MarketingOperationsBV].[Invoice].[State Code] = 'DRAFT') Then ([MarketingOperationsBV].[Invoice].[Invoice Amount]) else (0) total/ total 
Payableif([MarketingOperationsBV].[Invoice].[State Code] = 'PAYABLE') Then ([MarketingOperationsBV].[Invoice].[Invoice Amount]) else (0) total/ total 
Actual Spentif([MarketingOperationsBV].[Invoice].[State Code] = 'PAID') Then ([MarketingOperationsBV].[Invoice].[Invoice Amount]) else (0) total/ total 
Vendor_Query
(prompt)
Total: 1  
Vendor Display[MarketingOperationsBV].[Invoice].[Vendor Display] 


Copyright

© Copyright Unica Corporation 2003 - 2010. All rights reserved.

Unica Corporation
Reservoir Place North
170 Tracer Lane
Waltham, MA 02451-1379
Examples and Data

All software and related documentation is subject to restrictions on use and disclosure as set forth in the Unica Software License and Services Agreement, with restricted rights for U.S. government users and applicable export regulations.

Companies, names, and data used in examples herein are fictitious unless otherwise noted.

Trademarks and Patents

Unica®, the Unica logo, Affinium®, MarketingCentral®, MarketSoft®, MarketSoft logo, DemandMore®, Leads to Revenue®, NetTracker®, My NetTracker®, and Ask NetTracker® are registered trademarks of Unica Corporation with the U.S. Patent and Trademark Office.

Affinium Campaign™, Affinium Campaign Collaborate™, Affinium Campaign eMessage™, Affinium Campaign Interact™, Affinium Campaign Optimize™, Affinium Detect™, Affinium Leads™, Affinium Leads Referral™, Affinium Leads Contact™, Affinium Model™, Affinium NetInsight™, Affinium Insight™, Affinium Plan™, Affinium Mart™, Universal Dynamic Interconnect™, Interconnect Modules™, Marketic™, Powering Marketing Success™, Drill Anywhere™, Marketing Objects™, Trend Anything™, Customer Value Maximization™, MAP™, One-to-One Optimization™, and Right-time Marketing™ are trademarks of Unica Corporation. All other trademarks are the property of their respective owners.

Portions of the software described in this document are covered by U.S. Patent Numbers: 6,317,752, 6,269,325, 6,542,894, and 6,782,390.

The NetTracker, Affinium Insight, and Affinium NetInsight products are licensed under the following patents and patent publications: US5,675,510, US6,115,680, US6,108,637, US5,796,952, US6,138,155, US6,653,696, US6,763,386, AU0701813, BR9609217, CA2223919, EP0843946, JP03317705, MX193614, NO09705728, AU735285, CA2246746, CN1174316, CN1547123, CN1547124, DK870234, DE69720186, ES2195170, AU727170, BR9808033, CA2284530, CN1251669, IL131871, JP2000514942, KR341110, NZ337756, WO9641495, EP0870234, EP1130526, EP1168196, US20040078292, WO9810349, US20050114511, US20040221033, WO9843380.

Markup functionality in Affinium Plan is enabled through use of third-party software components from AdLib™ eDocument Solutions and Adobe® Acrobat®. "Powered by AdLib™."

NOTICE: This document contains confidential and proprietary information of Unica Corporation ("Unica"). Use, duplication, or disclosure without the express written consent of Unica Corporation is prohibited.