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 |