Using Query with Range and Grouping in AX 2012 R3

static void Test_Query_CustTrans_with_Grouping(Args _args)
{
    Query query,queryParam;
    QueryRun qr;
    QueryBuildDataSource qbdsCustTransSum,qbdsQuery;

    CustTrans  _custTrans;
    TransDate               toDate;

    toDate = str2Date('31102016',123);

    if (toDate == dateNull())
    {
        toDate = DateTimeUtil::date(DateTimeUtil::utcNow());
    }

    query = new query();

    qbdsCustTransSum = query.addDataSource(tableNum(CustTrans));

    qbdsCustTransSum.addRange(fieldNum(CustTrans, AccountNum)).value(queryValue(strFmt('1051')));
    qbdsCustTransSum.addRange(fieldNum(CustTrans, TransDate)).value(SysQuery::range(dateNull(), toDate));

    qbdsCustTransSum.orderMode(ordermode::GroupBy);
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, AccountNum));
    qbdsCustTransSum.addGroupByField(fieldnum(CustTrans, TransDate));

    qbdsCustTransSum.addSelectionField(fieldNum(CustTrans, AmountMST), SelectionField::Sum);
    query.addHavingFilter(qbdsCustTransSum, fieldStr(CustTrans, AmountMST), AggregateFunction::Sum).value(SysQuery::valueNot('0'));

    qr = new QueryRun(query);

    while(qr.next())
    {
        _custTrans = qr.get(tableNum(CustTrans));
        info(strFmt('%1 %2',_custTrans.AmountMST,_custTrans.TransDate));
    }

}

Create Number Sequence on AX 2012 R3

public static void Create(
NumberSequenceCode  _numSeqCode,
Name    _txt,
NumberSequenceRange _highest,
NumberSequenceRange _lowest,
NumberSequenceRange _nextRec,
Name    _prefix,
Name    _format,
str 1 _manual,
str 1 _cont,
Name    _numSeqScopeStr
)
{
    NumberSequenceTable numberSeqTable;
    NumberSequenceScope numberSeqScope;
    NumberSequenceReference numberSeqRef;
    RefRecId _numSeqScopeR,_numSeqIdR;
    container   segments, segmentReset;
    str annotatedFormat, format;
    ;
    _nextRec =1;
    select firstonly * from numberSeqScope where numberSeqScope.DataArea == _numSeqScopeStr;
    _numSeqScopeR = numberSeqScope.RecId;

    if(!NumberSequenceTable::findByNaturalKey(_numSeqCode,_numSeqScopeR))
    {
        ttsBegin;
        numberSeqTable.clear();
        numberSeqTable.NumberSequence   = _numSeqCode;
        numberSeqTable.Txt              = _txt;
        numberSeqTable.Highest          = _highest;
        numberSeqTable.Lowest           = _lowest;
        numberSeqTable.NextRec          = _nextRec;

        if(_manual == 'Y')
        { numberSeqTable.Manual           =  NoYes::Yes; }

        if(_cont == 'Y')
        { numberSeqTable.Continuous       =  NoYes::Yes; }

        segments = segmentReset;
        //segments += [[-1, conPeek(rec, 8)]];// 0 company, -1 constant, -2 alphanumeric
        if(_prefix)
        { segments += [[-1, _prefix]]; }
        segments += [[-2, _format]];

        annotatedFormat  = NumberSeq::createAnnotatedFormatFromSegments(segments);
        format           = NumberSeq::createAnnotatedFormatFromSegments(segments, false);
        numberSeqTable.AnnotatedFormat      = annotatedFormat;
        numberSeqTable.Format               = format;
        numberSeqTable.NumberSequenceScope  = _numSeqScopeR;
        numberSeqTable.validateWrite();
        numberSeqTable.insert();
        ttsCommit;
    }
}

Open Source and Freeware Tools for Productivity

File Editor

Notepad Plus Plus
notepad-plus-plus.org

File and Folder Comparison
 
Winmerge
winmerge.org


Image Editor

Paint .Net
www.getpaint.net

Icon Editor 

Green Fish Icon Editor
greenfishsoftware.blogspot.co.id


Color Finder

Hex Color Finder
www.hexcolorfinder.com

Multimedia Converter

Format Factory
www.pcfreetime.com/formatfactory

PDF Creator

doPDF
www.dopdf.com

DVD Burner

InfraRecorder
infrarecorder.org

Archiver

Peazip
peazip.org

Computed column date to get max day on month for AX 2012 R3

public static server str getMaxDateTransDate()
{
    #define.ViewName(InventValueTransViewSumLoc)
    #define.DataSourceName("InventValueTransView")
    #define.FieldDisplayValue("RecID")

    str sWhereClause1;
    DictView dictView;
    SysDictTable dicTable;

    // Construct a DictView object for the present view.
    dictView = new DictView(tableNum(#ViewName));
    //Construct a table object for the account table
    dicTable = new SysDictTable(tableNum(InventValueTransView));

    // Create the where clause
    sWhereClause1 = DictView.computedColumnString
    (
        'InventValueTransView',
        fieldStr(InventValueTransView, RecId),
        FieldNameGenerationMode::WhereClause
    );

    return strFmt
    (
        //'select year(TransDate) from %1 where %2 = %3',
        'SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,TRANSDATE)+1,0))',
        dicTable.name(DbBackend::Sql),
        dicTable.fieldName(fieldNum(InventValueTransView, RecId), DbBackend::Sql),
        sWhereClause1
    );
}

Rebuild Management Report 2012 Datamart for AX 2012 R3

DELETE
=======
1. Backup datamart database. i.e.: ManagementReporterDM
2. Run MR Config Console
3. Select database connection on ERP Integration
4. Disable Integration
5. Remove
6. Stop both MR Services
7. Delete Database : ManagementReporterDM

RECREATE
=========
8. Run both MR Services
9. Configure datamart