Example Query Subject for a custom metric

You can define a single Query Subject for all of the metrics associated with an object type. Here is an example Query Subject for metrics associated with projects:

Select
UAP_PROJECTS.PROJECT_ID,
a.METRIC_VALUE1 as TotalRevenue,
b.METRIC_VALUE1 as ResponseRateActual,
b.METRIC_VALUE2 as ResponseRateTarget,
c.METRIC_VALUE1 as TotalLeadsGeneratedActual,
c.METRIC_VALUE2 as TotalLeadsGeneratedTarget,
d.METRIC_VALUE1 as TotalCostPassed
From
UAP_PROJECTS
LEFT JOIN
(select PROJECT_ID, METRIC_VALUE1 from UAP_PROJ_METRICS where UAP_PROJ_METRICS.METRIC_ID = 'TotalRevenue') as a
ON a.PROJECT_ID = UAP_PROJECTS.PROJECT_ID
LEFT JOIN
(select PROJECT_ID, METRIC_VALUE1, METRIC_VALUE2 from UAP_PROJ_METRICS where UAP_PROJ_METRICS.METRIC_ID = 'ProjectResponseRate') as b
ON b.PROJECT_ID = UAP_PROJECTS.PROJECT_ID
LEFT JOIN
(select PROJECT_ID, METRIC_VALUE1, METRIC_VALUE2 from UAP_PROJ_METRICS where UAP_PROJ_METRICS.METRIC_ID = 'NumberOfLeadsGeneratedPassed') as c
ON c.PROJECT_ID = UAP_PROJECTS.PROJECT_ID
LEFT JOIN
(select PROJECT_ID, METRIC_VALUE1 from UAP_PROJ_METRICS where UAP_PROJ_METRICS.METRIC_ID = 'TotalCostPassed') as d
ON d.PROJECT_ID = UAP_PROJECTS.PROJECT_ID

IBM Unica Marketing Operations
 
8.5.0
Copyright IBM Corporation 2011. All Rights Reserved.
For more information, see our support and community site: Customer Central