最新消息:ww12345678 的部落格重装上线,希望大家继续支持。

AX 2012视图计算列中的月份和年份的最后一天 / Last Day Of Month and Year as View Computed Column in AX 2012

网络文摘 William 434浏览 0评论
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 CustTransView with Computed Column
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址