AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 20.12.2012, 20:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
addicted2ax: Dynamics AX 2012: Computed Columns in Views
Источник: http://addicted2ax.de/index.php?/arc...-in-Views.html
==============

A very useful new feature in Dynamics AX 2012 are Computed Comumns in views.

Computed Columns is one of the small and understated but highly usefull new features in Dynamics AX 2012.
Computed Columns in Views is an old but well established and useful feature in SQL Server since many versions. But this feature was available in Dynamics AX since version 2012.

Well, here is an example how I used Computed Columns in a View: In CustTrans-table there are two columns of interest. First “AmountCur” which represents the Amount which has to be payed. “SettledAmountCur” is the amount which already was paid. Now I am interesed in the difference between “AmountCur” and “SettledAmountCur”. Unfortunately there is no column in “CustTrans”-table which holds just this specific information. So I had to create a view with Computed Columns.

First I created a View “InvoiceDiscountDeclined”. Data source is “CustTrans”-table with the name ”CustTrans_1”.
Then I created an Method direct at the View with the name “DeclinedDiscount” (and also another one with name “DateDifference”, but this computed column is not in the scope of this article). Then I wrote the method below:



Important: The value returned by the method has to be string (str) and is computed as static method on the server.

For the calculation I used the “SysComputedColumn”-class of Dynamics AX 2012 which has lot of methods for various mathematical operations. I my example I used “subtract”-method which needs two table fields.

“SysComputedColumn”-class needs three different information:
1. Name of the view (red)
2. Name of the data source (blue). In my example the name is “CustTrans_1” and not just “CustTrans”!
3. The fieldname (together with the table-name; now we use “TransTable”)

In contrast to display-methods you just cannot drop the method into the fields list. You have to create a new computed column-field by right-clicking fields-note. Name the field. Set the “ViewMethod”-property to the name of the method (as in my example “DeclinedDiscount”).



When you press “Save”-button Dynamics AX 2012 creates a “Create View”-SQL-statement which creates a view in the underlying SQL Server-database. While synchronizing the view to the database, the method is called. After synchronization the method is not called any more, until your resynchronize the view to the database.

Well, that’s sounds good and indeed it is most time. But creating a view with computed columns could be tricky. My client crashed several times when I tried to change a just created view with computed columns.

But in the end the creation of the view with the computed columns had a happy end:



Some words why I have created this view and why I used computed columns to archive the goal:
In Dynamics AX document management I created a word-template which represents a collection letter send to clients if there are unsettled amounts open. (There are some special scenarios which cannot be handled by using “CollectionLetter”-SSRS-Report.)

In Dynamics AX 2009 you had to calculate the amounts with Word-Formulas and Bookmarks which was kind of tricky. Using a Computed column and just passing the calculated value to the Word-Document is much easier.



Источник: http://addicted2ax.de/index.php?/arc...-in-Views.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
Старый 31.05.2013, 19:25   #2  
Wamr is offline
Wamr
----------------
Лучший по профессии 2014
Лучший по профессии AXAWARD 2013
 
1,737 / 858 (32) +++++++
Регистрация: 15.01.2002
Адрес: Москва
Записей в блоге: 7
Краткий перевод.
Чтобы получить вычисляемое поле во view надо написать метод, который вернет SQL строку для столбца.
Например:
Field1 * Field2
DATEADD(MM,1,GETDATE())

и указать его в свойстве поля viewMethod
За это сообщение автора поблагодарили: S.Kuskov (1).
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
DAX: Official Dynamics AX 2012 R2 Content (update) - Where is it, and how can you find out about updates? Blog bot DAX Blogs 0 03.12.2012 11:11
ax-erp: Creating SSRS-Reports in Dynamics AX 2012 – What’s no longer possible in AX-reports Blog bot DAX Blogs 0 18.07.2012 12:11
emeadaxsupport: New Content for Microsoft Dynamics AX 2012 : October 2011 Blog bot DAX Blogs 0 27.10.2011 17:11
axinthefield: Dynamics AX Event IDs Blog bot DAX Blogs 0 01.03.2011 22:11
daxdilip: Whats New in Dynamics AX 2012 (A brief extract from the recently held Tech Conf.) Blog bot DAX Blogs 7 31.01.2011 12:35
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 17:40.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.