0

I have the following code in the init() of a report:

QueryBuildDataSource    qbdsTable;
QueryOrderByField       QueryOrderByFieldTransDate;
QueryOrderByField       QueryOrderByFieldDimZone
QueryOrderByField       QueryOrderByFieldDimCC;
;

super();

qbdsTable = query.dataSourceTable(tableNum(Table));
QueryOrderByFieldTransDate = qbdsTable.addOrderByField(fieldNum(Table, TransDate));
QueryOrderByFieldTransDate.autoSum(true);
QueryOrderByFieldDimZone = qbdsTable.addOrderByField(fieldNum(Table, DimZone),SortOrder::Descending);
QueryOrderByFieldDimZone.autoSum(true);
QueryOrderByFieldDimCC = qbdsTable.addOrderByField(fieldNum(Table, DimCostCenter));
QueryOrderByFieldDimCC.autoSum(true);

and the autosum property is functioning properly (I have set the SumAll property for the field I use to calculate these subtotals).

The problem is that, whenever I try to add an groupBy field or a selection field, the autosum property isn't honored anymore (the subtotals are not displayed anymore):

qbdsTable.addSelectionField(fieldNum(Table, AmountMST), selectionField::Sum);

or

qbdsTable.addGroupByField(fieldNum(Table, TransDate));

I have tried to use:

qbdsTable.addSortField(fieldNum(Table, TransDate));
qbdsTable.autoHeader(1, true);

but I have the same problem

Does anyone has an Idea how I can use both autosum and addGroupByField on the same datasorce of a report?

Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
Alex
  • 78
  • 1
  • 1
  • 7

1 Answers1

0

For historical reasons old style AX reports behaves differently when called directly (run on the report node) or through on a report menu item.

The execution order of the first is:

  1. init
  2. fetch
  3. dialog

The second runs via class RunbaseReportStd in the following order:

  1. init
  2. dialog
  3. fetch

This matters because you have change the query after the user has made any changes.

So move your code changes from init to fetch, like this:

public boolean fetch()
{
    QueryBuildDataSource qbdsCustTrans = query.dataSourceTable(tableNum(CustTrans));
    ;
    qbdsCustTrans.addSelectionField(fieldNum(CustTrans, AmountMST), selectionField::Sum);
    qbdsCustTrans.addGroupByField(fieldNum(CustTrans, AccountNum));
    qbdsCustTrans.addGroupByField(fieldNum(CustTrans, TransDate));
    qbdsCustTrans.addGroupByField(fieldNum(CustTrans, CurrencyCode));
    //info(qbdsCustTrans.toString());
    return super();
}

This will only work, if called through the menu item. Also, I could not get the auto-sum functionality to work, when added by code.

Instead you will have to add the order by and autosum using Sorting node of the report query.

I don't know why, but maybe this is because you use auto design, which is generated at run time.

Jan B. Kjeldsen
  • 17,817
  • 5
  • 32
  • 50
  • I didn't post the whole code but I have qbdsTable.addGroupByField for every field displayed on the report; I also have qbdsTable.addSelectionField(fieldNum(Table, AmountMST), selectionField::Sum); and the query is generated like it supposed to 'SELECT SUM(AmountMST) FROM Table GROUP BY Table.TransDate, Table.DimZone, Table.DimCostCenter ORDER BY Table.TransDate ASC, Table.DimZone DESC, Table.DimCostCenter ASC' – Alex Mar 10 '15 at 09:31
  • Consider reproducing the problem on standard tables then export to pastebin.com. – Jan B. Kjeldsen Mar 10 '15 at 11:14
  • can you put it on pastebin.com, please? I have the demo VM for AX2012 R3 – Alex Mar 10 '15 at 16:05
  • the code written on the init method was just for testing; for the report I'm working on, I've written a class that extends RunBaseReport and calls the report from lastValueElementName(); either way, I had the same result. I'l try the fetch() aproach. Thank you for your support so far – Alex Mar 10 '15 at 16:10
  • the reason that I want to add autosum by code is to have multiple designs , each with different subtotals, for the same report. Maybe someone else have and ideea. – Alex Mar 11 '15 at 10:32