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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(list) | Total: 14 | Filters:
[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 Late | if ([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] | ||
Status | if([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(list) | Total: 15 | Filters:
[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 Dates | if ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 7 | Filters:
[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() |
![]() |
Query Name | Data Item | Expression | Agg/ 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: 9 | Filters:
[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 Unit | CASE 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 | |
Initiative | CASE WHEN [MarketingOperationsBV].[Project Custom Attributes].[Initiative] is missing THEN '(No Value)' ELSE [MarketingOperationsBV].[Project Custom Attributes].[Initiative] END | ||
Template Display | if ([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 Display | if ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 8 | Filters:
[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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 20 | Filters:
[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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(list) | Total: 17 | Filters:
[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 Variance | if ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(list) | Total: 17 | Filters:
[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] | ||
Status | IF ([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] | ||
Condition | CASE 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 User | count([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 7 | Filters:
[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 Assigned | count( distinct [MarketingOperationsBV].[Task Member].[Task ID]) | ||
Total Effort | total([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_Days | floor((_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: 3 | Filters:
[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_Days | floor((_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: 2 | Filters:
[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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(crosstab) | Total: 5 | ||
Count of Programs | count([MarketingOperationsBV].[Program].[Program ID]) | / total | |
Program Status | If ([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 Name | IF ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Report Query
(crosstab) | Total: 5 | ||
Status | [MarketingOperationsBV].[Project].[Status] | ||
Count of Projects | count([MarketingOperationsBV].[Project].[Project ID]) | / total | |
Template Display | IF ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ 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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 9 | Filters:
[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 Late | if ([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() |
![]() |
Query Name | Data Item | Expression | Agg/ Rollup Agg |
---|---|---|---|
Query1
(list) | Total: 4 | Filters:
[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] | ||
Outstanding | if([MarketingOperationsBV].[Invoice].[State Code] = 'DRAFT') Then ([MarketingOperationsBV].[Invoice].[Invoice Amount]) else (0) | total/ total | |
Payable | if([MarketingOperationsBV].[Invoice].[State Code] = 'PAYABLE') Then ([MarketingOperationsBV].[Invoice].[Invoice Amount]) else (0) | total/ total | |
Actual Spent | if([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-1379Examples and DataAll 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 PatentsUnica®, 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.