Last Day Of Month and Year as View Computed Column in AX 2012

View Computed Columns in Dynamics Ax 2012 support the use of TSQL functions to calculate additional view columns. Here is  an example how to calculate the Last Day of Month and Last Day of Year based on the TransDate at the CustTransView. First, Extend the SysComputedColumnDatePart  Base Enum and add two additional values Month and Year. Extend the getDateDiffFormatSelector() method at the SysComputedColumn class.

private static server str
getDateDiffFormatSelector(SysComputedColumnDatePart _dateDiffFormat)
{
    switch(_dateDiffFormat)
    {
        case SysComputedColumnDatePart::Day :
            return ‘d’;
        case SysComputedColumnDatePart::Second :
            return ‘s’;
        case SysComputedColumnDatePart::Month:
            return ‘m’;
        case SysComputedColumnDatePart::Year :
            return ‘yy’;
    }

    return ‘d’;
}

Last Day Of Month

Add a new static method to the view and call it lastDayOfMonth.

private static server str lastDayOfMonth()
{
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(‘-1’,
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff(
    ‘0’,sTransDate,SysComputedColumnDatePart::Month)+ ‘+1′,’0’,
    SysComputedColumnDatePart::Month),
    SysComputedColumnDatePart::Day),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayofMonth() method as ViewMethod

Last Day of Year

Add a new static method to the view and call it lastDayOfYear

private static server str lastDayOfYear()
{  
    DictView    ctView = new DictView(tableNum(‘CustTransView’));
    str sTransDate = ctView.computedColumnString(‘CustTrans’,’TransDate’);
    str sLastDay = "";

    sLastDay = SysComputedColumn::cast(
    SysComputedColumn::getDateAdd(
    SysComputedColumn::getDateDiff
    (‘0’,sTransDate,SysComputedColumnDatePart::Year) + ‘+1’,
    ‘-1’,
    SysComputedColumnDatePart::Year),
    ‘DateTime’);

    return sLastDay;
}

Add a new DateTime column to the view and set the lastDayOfYear() method as ViewMethod

The View

Dynamics AX 2012 translates both fields in TSQL Code. The view looks like this

SELECT    
ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION, 1010 AS RECID,
CAST(CAST(DATEADD(yy, DATEDIFF(yy, 0, TRANSDATE) + 1, – 1) AS DateTime)
AS DATETIME) AS LASTDAYOFYEAR,
CAST(CAST(DATEADD(d, – 1, DATEADD(m, DATEDIFF(m, 0, TRANSDATE) + 1, 0)) AS DateTime) AS DATETIME) AS LASTDAYOFMONTH
FROM        
dbo.CUSTTRANS AS T1
GROUP BY ACCOUNTNUM, VOUCHER, TRANSDATE, DATAAREAID, PARTITION

The result in Dynamics AX looks like this