Microsoft Dynamics 365 Customer Experience Analyst : Configure rollup queries

A rollup query in Dynamics 365 is a specialized saved query that defines the criteria for which records should be included in a rollup field calculation. Rollup fields are used to automatically aggregate data, such as counting related records, calculating totals, or finding the most recent activity. For example, a rollup query can specify which opportunities to include when calculating the total estimated revenue for an account. By creating rollup queries, organizations can control and filter the exact data that contributes to rollup fields, ensuring accurate and meaningful insights. 

 What are Rollup Queries?

A Rollup Query is a saved query in Dynamics 365 that defines which related records should be included in a rollup field calculation. Rollup fields allow you to aggregate data (like count, sum, max, min, or latest date) from related records automatically.

For example:

• You can create a rollup field on Account to calculate the total estimated revenue of all open opportunities related to that account.
• The rollup query defines which opportunities (e.g., only those with status = Open and estimated revenue > \$0) should be included.

Why use Rollup Queries?

• Precision: Instead of including all related records, you can filter which ones matter.
• Flexibility: You can create queries to target specific conditions (e.g., only high-priority cases, only active opportunities, etc.).
• Business Insights: Provides summarized or KPI-style data right on the entity record.

Key Features

• Entity-based – Rollup queries are tied to a specific entity (e.g., Opportunity, Case).
• Reusable – Once created, you can use the same rollup query in multiple rollup fields.
• Customizable – You can add filters (conditions) and relationships (like child entities).
• Scheduled Calculation – Rollups are not instant; they refresh automatically every 12 hours (by system job), but users can also refresh them manually.

Example Scenarios

• Sales – On an Account record, show the total estimated revenue of all active Opportunities (using a rollup query that filters opportunities by “Open” status).
• Customer Service – On a Contact record, display the number of active cases (rollup query filters for cases where Status = Active).
• Projects – On a Project record, calculate the sum of billable hours from related tasks (rollup query filters for tasks marked as Billable).

How Rollup Queries Work with Rollup Fields

• Create a Rollup Query → Define the conditions (filters) for the related records.
• Create a Rollup Field on the target entity (e.g., Account).
• Link the Rollup Query to the Rollup Field.
• Dynamics 365 automatically calculates and updates the rollup field based on the query’s logic.

Limitations to Keep in Mind

• Rollups are read-only and cannot be manually edited.
• They update once every 12 hours by default (system job), but can be refreshed on-demand.
• Maximum of 100 rollup fields per entity and 10 rollup fields per entity can be evaluated at once in a calculation cycle.
• Rollups cannot span across more than one N\:N (many-to-many) relationship.

In summary:

Rollup Queries in Dynamics 365 are powerful tools to filter and define which related records should be included in rollup field calculations. They allow organizations to surface meaningful KPIs directly on records (like Accounts, Contacts, or Projects), making it easier for users to see aggregated insights without building complex reports or dashboards.