Example custom report: Project Performance Summary (custom)

The Unica Marketing Operations reports package provides two versions of the Project Performance Summary. The Project Performance Summary uses only default attributes. The Project Performance Summary (Custom) contains custom attributes and metrics. This section describes the changes that were made to the Unica Marketing Operations data model and report in Cognos to produce the Project Performance Summary (Custom) .

Custom attributes and metrics identified

The following custom attributes and metrics were needed to produce the Project Performance Summary (Custom).

Attribute	Column	Lookup table
Initiative	dyn_projectatts.init_type_id	lkup_initiative
Business unit	dyn_projectatts.business_unit_id	lkup_business_unit
Product Family	dyn_projectatts.prod_family_id	lkup_prod_family
Segment	dyn_projectatts.segment_id	lkup_segments

The following are the custom metrics needed for the report.

Total Revenue: metricid = 'TotalRevenue' (actual)

Response Rate: metricid = 'ResponseRate' (actual)

Total Leads Generated: metricid = 'NumberOfLeadsGeneratedPassed' (actual, target)

ROI: metricid = 'ROI' (actual)

Metadata associated with the custom attributes

The following columns in the dyn_projectatts table were imported to support the custom attributes.

init_type_id

segment_id

business_unit_id

prod_family_id

The following lookup tables were imported to support the custom attributes.

lkp_initiative

lkup_segments

lkup_business_unit

lkup_prod_family

Relationships and Queries defined in Model View

In Model View in Cognos Framework Manager, relationships were defined as shown here.

The Query Subject definition for Project was updated with query items for the custom attributes as shown here.

Query Items added in Business View

The following query items were added in the Business View in Cognos Framework Manager.

Column	Type/extra info	Query item
Initiative	String; group-by column	Project Custom Attributes.Initiative
Business Unit	String	Project Custom Attributes.Business Unit
Segment	String	Project Custom Attributes.Segment
Product Family	String	Project Custom Attributes.Product Family
Total Revenue	Currency	Project Performance Summary Metrics.Total Revenue
Response Rate (Actual)	Percent	Project Performance Summary Metrics.Response Rate (Actual)
Response Rate (Target)	Percent	Project Performance Summary Metrics.Response Rate (Target)
Response Rate Variance	Percent, Calculation	ResponseRate Actual – ResponseRate Target
Total Leads Generated (Actual)	Number	Project Performance Summary Metrics. Total Leads Generated (Actual)
Total Leads Generated (Target)	Number	Project Performance Summary Metrics. Total Leads Generated (Target)
Total Leads Generated Variance	Number, Calculation	Total Leads Generated (Actual) – Total Leads Generated (Target)
ROI	Percent, Sort Column, Calculation	Project Performance Summary Custom Metrics].[Total Revenue]-[Project Budget].[Actual Total])/[Project Budget].[Actual Total]

Columns added and deleted from the report

In Cognos Report Studio, all the columns were removed from the report except for Project Name (Code), Project Start Date, and Project End Date.

The following columns were added to the report.

Initiative

Business Unit

Segment

Product Family

Total Revenue

Response Rate (Actual)

Response Rate (Target)

Response Rate Variance

Total Leads Generated (Actual)

Total Leads Generated (Target)

Total Leads Generated Variance

ROI

Prompts created

The following two prompts were created.

Prompt	Prompt type	Query Subject
Initiative	Search & Select	Project Custom Attributes.Initiative
Business unit	Search & Select	Project Custom Attributes.Business Unit