Example custom report: Project Performance Summary (Custom)
The IBM® 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.
A list of the changes that were made to the Marketing Operations data model and to the report in Cognos® to produce the Project Performance Summary (Custom) follows.
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 that were 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
Copyright IBM Corporation 2014. All Rights Reserved.