Example custom report: Project Performance Summary (Custom)

The Unica Plan 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 Unica Plan 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