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.
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
