Print      Open PDF Version of Online Help

Previous Topic

Next Topic

Forecasts Reporting Subject Area


This subject area is available in all editions of Oracle CRM On Demand.

Business Purpose

This subject area allows you to report on the data generated by the Oracle CRM On Demand Forecast Module. It also allows you to compare the forecasting summary with the latest real-time opportunity data by account, territory, user, opportunity, product, date, and user quota dimensions. You can create a performance report for a sales team, showing the pipeline coverage, quota attainment, and forecast accuracy. It also allows sales managers to monitor the performance of individual team members, using the user hierarchy.

Relationship Type



This subject area has the following dimensions:

  • Account
  • Account Revenue (see Note 1)
  • Account Territory
  • Book
  • Close Date (see Note 2)
  • Contact
  • Forecast
  • Forecast Date (see Note 3)
  • Opportunity (see Note 4)
  • Opportunity Product Revenue (see Note 4)
  • Owned By User
  • Product
  • Product Category
  • Quota

Note 1: You cannot combine the Account Revenue dimension with the Opportunity or the Opportunity Product dimensions.

Note 2: For quotas, the close date is the first day of the month for the quota period. For forecasts, the close date is the closed date for the opportunities that are in the forecast snapshots. For opportunities, the closed date is the closed date for the opportunity.

Note 3: The forecast date is the date when the forecast was run. An opportunity can be in multiple forecasts.

Note 4: Opportunity and Opportunity Product Revenue can be associated only with the Opportunity and Opportunity Product Revenue metrics.

Optimized Filtering Fields

This subject area has fields that are optimized for reducing the query time when they are used in filters. Optimized fields end with the words Code or UTC. For example, the Account dimension has an Account Type field. There is also an Account Type Code field, which is the optimized version of the Account Type field. Similarly, there is a Last Call Date UTC field, which is the optimized version of the Last Call Date field. Using the optimized field in the filter generates faster queries. This method is faster than using the standard field. For information on using optimized filtering fields, see Using Optimized Filtering Fields. The following dimensions in this subject area have optimized filtering fields:

  • Account
  • Contact
  • Opportunity


The complete list of metrics for this subject area is as follows:

  • Account Revenue Metrics
    • Number (#) of Products
    • Average Number (Avg #) of Products
  • Forecast Metrics
    • My Account Revenues
      • My Account Revenue
    • My Contact Revenues
      • My Contact Revenue
    • My Opportunities
      • My Opportunity Revenue
    • My Product Revenues
      • My Product Revenue
    • My Products
      • Closed Quantity
      • Closed Revenue
      • Expected Revenue
      • Forecasted Quantity
      • Pipeline Quantity
    • Team Summary
      • Forecast
      • Closed Revenue
      • Pipeline
      • Best Case
    • Team Product Summary
      • Closed Quantity
      • Forecasted Quantity
      • Pipeline Quantity
      • Closed Revenue
      • Expected Revenue
    • Closed Revenue
    • Pipeline
    • Forecast
    • Expected Revenue
    • Best Case
  • Quota Metrics
    • Quota Value
  • Opportunity Metrics
    • Opportunity Custom Metrics
      • Indexed Number
      • Average (Avg) Indexed Number
      • Indexed Number
      • Average (Avg) Indexed Number
    • Revenue
    • Expected Revenue
    • Closed Revenue
    • Revenue (000)
    • Expected Revenue (000)
    • Closed Revenue (000)
    • Number (#) of Opportunities
    • Number (#) of Open Opportunities
    • Number (#) of Closed Opportunities
    • Number (#) of Wins
    • Average Number (Avg #) of Days in Stage
  • Opportunity Product Revenue Metrics
    • Number (#) of Products
    • Average Number (Avg #) of Products

Usage Notes

The following restrictions apply:

  • Multi-Fact. Because the Forecasts subject area is a Multi-Fact subject area, you must ensure there is only one metric as part of a report.
  • Book dimension. The book dimension and hierarchy allow you to group data records into books. But the data that you can see is still governed by your visibility mode in Analytics. It works only with account, account revenue, opportunity, and opportunity-product.
  • Hierarchies. All hierarchies are limited to eight levels.
  • Revenue Split. When the revenue split feature is configured (see Setting Up Your Company Profile and Global Defaults), the forecast summary reflects the split, but the real-time opportunity metrics do not.
  • Quota Metrics. Comparing quota metrics to other metrics is only valid at the month level and user level.
  • Forecast summary custom fields. These fields are not available for reporting. The forecast summary generated by Oracle CRM On Demand contains rolled-up totals. Therefore, the forecast metrics in your report make sense only if you view the data by user. Viewing the forecast metrics without the user causes the value to appear incorrect.
  • Opportunity and multiple forecasts. The same opportunity can appear in multiple forecasts. Therefore, forecast metrics must be viewed by a specific date, using the filtered-metric mechanism. Without doing this, the forecast metrics might appear incorrect.
  • Forecast and user quota metrics. The data for revenues is the same as the forecast summary date, so all opportunities appear to be on the first day of the month. The user quota is also set for a monthly period and appears on the first day of the month. For this reason, reports that include forecast and user quota metrics make sense only if the data is aggregated for the month or at a higher level.
  • User quota metrics. Visibility to user quota metrics is controlled using the Manager visibility regardless of the user’s visibility. Hence, it is not possible for users who are not company administrators to report user quotas along with opportunity metrics when their visibility is set to Team visibility.
  • User’s percentile ranking. You cannot build a user’s percentile ranking without having access to all the underlying data.
  • Calendar month and multiple forecasts. Each calendar month can have multiple forecasts. So, a report showing the total forecast metrics must be qualified by a specific forecast date.
  • Quota and Revenue. If your report showing the user quota and real-time opportunity revenue does not show the expected results, then it could be because there are no records that have opportunity closed dates in the same period as the quota records.
  • Monthly Forecast. Your monthly forecast summary totals are already rolled up. So, a total for all users gives incorrect results. If you want to obtain the total this way, you must use the revenue metrics from the My Revenues folder.
  • Analytics Manager Visibility. Use Analytics Manager Visibility if the forecast includes only people who report to you.
  • Analytics Team Visibility. Use the Analytics Team Visibility if those people in the forecast are part of your team. If the people in the forecast are neither in your team, nor reporting to you, then the opportunity and opportunity product view does not match the forecast summary.
  • Forecast metrics. The forecast metrics must reference a specific forecast. Use the FILTER metric function to reference the metrics by forecast date and forecast type. For example, use the FILTER metric function to obtain the forecast value for a specific forecast date, such as Sep 16, 2009. Because there are multiple forecast types, add a further filter to get only the values for Opportunity Revenue Forecast. For example:

    FILTER ("Forecast Metrics"."Forecast Revenue" USING ("Forecast Date.Date = date '2009-09-16' AND Forecast."Forecast Type" = 'Opportunity Revenue Forecast'))

    Using the FILTER metric function is not the same as adding a filter to the report. If you add a report filter to the filter by Forecast Date and Forecast Type, you do not get any values for the user quota or real-time revenue. If you have multiple quotas defined for each user, you must use the FILTER metric function to reference the specific quota value. For example:

    FILTER("Quota Metric"."Quota Value" USING (Quota."Quota Name" = 'Sales')

    In this example, the FILTER metric function obtains the quota value for a specific quota name (Sales). Without this filter metric, you get only the total value of all the quotas.

    The Account and Contact dimensions include a Book hierarchy, which enables you to analyze metrics and related attributes at any level. The Book dimension enables you to analyze metrics and related attributes of accounts and contacts at any level of the book hierarchy.

Published 5/4/2012 Copyright © 2005, 2012, Oracle. All rights reserved. Legal Notices.