Report with Sum at Header using Duplicated Query

Your report needs the summation of some fields at the Header portion but you do not want to hack the report classes to achieve that.

Dynamics Ax subtotal is available only at the footer. If you need it in some other section, you would have to find a way around. We will look at an approach where the report query is duplicated for the necessary calculation at the section header.

The demo subsequently will produce the report illustrated in the following figure. The section footer is the subtotal produced by the standard report. The section header on the other hand shows the output of the approach suggested.

Motivation

There are ways to achieve this. The use of duplicated query is motivated by the following factors.

• It could be applied to existing report without affecting the overall structure. In other words, no changes will be required on the data source and other sections in the report.
• The calculation is in accordance to the filters set by the user without additional work.
• The report query will be handled by Dynamics Ax report subsystem while we will only work on the duplicated copy of the query.

Technique Highlight

Basically we will have two copy of the same query. At the section group header, we will loop the duplicated copy until the next header group is reached. The necessary work is performed while we loop and the value to be shown at the group heading is prepared prior to executing the group header. Then the report subsystem will take over and loop the original copy of the query.

The following code segment illustrates the technique explained above.

// loop as long as within the same subheading group.
do {
  custTransLoop = qrHead.get(custTransLoop.TableId);
  if (custTransLoop.AccountNum == CustTrans.AccountNum) {
    rSum += custTransLoop.AmountCur;
  }
  else {
    break; //heading group changed.
  }
} while (qrHead.next());

mrSum = rSum; //value prepared for heading.

super(); //execute group heading.


The following code segment illustrates how the report query is duplicated into a QueryRun object.

qrHead = new QueryRun(element.query());

Demo

We will create a simple report with CustTrans as the only data source to demonstrate the highlighted approach. We dissect the whole process into three sections. The first will cover creating report while the second is regarding adding subtotal just to verify the outcome of the calculation. The final section is the one that cover the approach recommended. Those that are well versed with Dynamics Ax reporting tool may opt to skip the first two sections.

Create Report

The following steps cover report creation. Alternatively, you may use the Report Wizard to perform this.

1. Create a new project to group objects if you have not.
2. Right click on your project and select New > Report. You will have a report with the name Report1 created.
3. Right click on Data Sources > Query > Data Sources and select New Data Source. Change the Table to CustTrans under the Property Page.
4. Expand the node CustTrans(CustTrans) and right click on the node Sorting. Select New > Field. Ensure the Field is AccountNum. Change the properties AutoHeader and AutoSum to Yes.
5. Right click on the Designs node and select New Report Design. You will see a new node named ReportDesign1 with a child node named AutoDesignSpecs.
6. Right click on ReportDesign1 and select Generate Design. You will see a node named Generated Design added. You will also see a node named Section Group: CustTrans too.
7. Expand the Section Group: CustTrans and right click on Body:CustTrans_Body. Select New Control > Field Group from CustTrans. Change the field group's DataGroup property to AutoReport.

Validation preparation

The following steps add a subtotal to a field for verification.

1. Change the AutoFieldGroupOrder property to "Save the fields' properties" for the field group.
2. Expand the Group and find control named Real: CustTrans_AmountCur and change the SumAll property to Yes.
3. Right click on Footer: CustTrans_Footer and select New Control > Sum. Change the properties of the newly added for Table to CustTrans and Field to AmountCur.

Sum at Header

You now have a report with subheading. The following steps demonstrate the approach mentioned earlier.

1. Add the following declaration to classDeclaration of the report. The classDeclaration will now look like the following.
public class ReportRun extends ObjectRun
{
  AmountCurDebCred  mrSum;
  QueryRun          qrHead;
}

2. Add a new method to Header: CustTrans_Header and copy the following code to it.
display AmountCurDebCred SumAmtCur()
{
  return mrSum;
}

3. Then add a new a control with the display method to the Header. This could be done by dragging the method and dropping it at the Header: CustTrans_Header node.
4. Override the method executeSection() of the header with the following code. The method CustTrans_Header:executeSection() shall looks like the following.
public void executeSection()
{
  CustTrans  custTransLoop;
  real       rSum;

  // dupplicate the query on first execution of the header.
  if (!qrHead) {
    qrHead = new QueryRun(element.query());
    qrHead.next();
  }

  // loop as long as within the same subheading group.
  do {
    custTransLoop = qrHead.get(custTransLoop.TableId);
    if (custTransLoop.AccountNum == CustTrans.AccountNum) {
      rSum += custTransLoop.AmountCur;
    }
    else {
      break;
    }
  } while (qrHead.next());

  mrSum = rSum; //prepare value to be shown at group header.

  super(); //execute the group header.
}