Grouping in SSRS D365 reports

Grouping in SSRS D365 reports :

While implementing a FetchXml based SSRS report, there are basically two ways of grouping data:

Server-side: Grouping can be applied by using FetchXml aggregation. To add grouping to your existing FetchXml query, execute these steps:

1. Inside your fetch query, set the aggregate attribute to true.
2. Set the attribute to groupby to true. Many attributes are allowed for grouping.
3. Set the attributes to aggregate to true. Many attributes are allowed for aggregation.
4. Remove all other attributes from the query, which have neither grouping nor aggregation applied to create a valid query.

Example:

<fetch distinct='false' mapping='logical' aggregate='true'> 
   <entity name='opportunity'> 
      <attribute name='opportunityid' alias='opportunity_count' aggregate='count'/> 
      <attribute name='estimatedvalue' alias='estimatedvalue_sum' aggregate='sum'/> 
      <attribute name='estimatedvalue' alias='estimatedvalue_avg' aggregate='avg'/> 
      <attribute name='actualclosedate' groupby='true' dategrouping='quarter' alias='quarter' />
      <attribute name='actualclosedate' groupby='true' dategrouping='year' alias='year' />
      <order alias='year' descending='false' />
      <order alias='quarter' descending='false' />
      <filter type='and'>
          <condition attribute='statecode' operator='eq' value='Won' />
      </filter>
   </entity> 
</fetch>

Next, enter your FetchXml query in your SSRS report's dataset and display the result in a Tablix format.

Client-side: Grouping can be applied during the report render process (actually on the SSRS server). In this case, keep your FetchXml without grouping clause and use Microsoft Visual Basic Expressions to aggregate data:

1. Enter you FetchXml query (without grouping) in your SSRS report's dataset and add a Tablix control to your report.
2. Find Row Groups and Column Groups.
3. In order to add a column group, right-click the Column Group and select Group Properties...
4. Select the Group expression (fx) and enter an expression.

Example:

=MonthName(Month(Fields!scheduledend.Value), true)

Conclusion: Using server-side grouping, your report will execute slightly faster, because there is less logic to be processed during report generation. On the other hand, with client-side grouping you get more options by using Microsoft Visual Basic Expressions to aggregate data.

Grouping in SSRS D365 reports