To build a custom sales performance report comparing targets, opportunity revenue, and sales orders in Microsoft Dynamics 365 Sales, you need to retrieve and combine data from both Forecast-related entities and standard sales tables in Dataverse.
Retrieving Forecast (Target) Data
The standard Forecast feature stores data across multiple msdyn_ tables. The most important entities for reporting are:
- Forecast Instance (msdyn_forecastinstance)
- This is the primary table for reporting. It contains:
- Target/Quota → msdyn_targetamount
- Aggregated values such as:
- Pipeline → msdyn_pipelineamount
- Best Case → msdyn_bestcaseamount
- Committed → msdyn_committedamount
- Forecast Configuration (msdyn_forecastconfiguration)
- Forecast Definition (msdyn_forecastdefinition)
- Forecast Recurrence (msdyn_forecastrecurrence)
Retrieving Sales Data (Actuals & Pipeline)
To complete the performance view, combine forecast data with standard tables:
Opportunities (opportunity)
- Used for pipeline and expected revenue
- Key fields:
- estimatedvalue
- estimatedclosedate
- Can be aligned using forecast category (msdyn_forecastcategory)
Sales Orders (salesorder)
- Represents actual closed revenue
- Key fields:
- totalamount
- datefulfilled
Data Modeling Approach
To build a meaningful report, align all datasets based on:
- Owner / Salesperson (or Territory hierarchy)
- Time Period (Month / Quarter from forecast recurrence)
This allows you to compare:
- Target (Forecast) vs Pipeline (Opportunities) vs Actuals (Orders)
Recommended Reporting Solution
Power BI (Best Approach)
Use the Dataverse connector in Microsoft Power BI to:
Extract:
- msdyn_forecastinstance (targets)
- opportunity (pipeline)
- salesorder (actuals)
Create relationships based on:
- Owner / hierarchy
- Time period
Build a report such as:
Salesperson Period Target Pipeline Actual Revenue
Additional Considerations
Snapshots (for trend analysis)
- Enable forecast snapshots if you need historical tracking of target vs actual changes over time.
Programmatic Access
Developers can use:
- Web API
- msdyn_ForecastApi action
- to retrieve forecast data dynamically.
Use Forecast Instance (msdyn_forecastinstance) for targets, combine it with Opportunity and Sales Order data, and build the report in Power BI for the most scalable and flexible solution.
Share
Report
17
All responses (
Answers (
