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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 06.08.2020, 09:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
dynamicsaxinsight: Filter voucher transactions by cost center
Источник: https://dynamicsaxinsight.wordpress....y-cost-center/
==============

Purpose:

The purpose of this blog post is to demonstrate how can we filter voucher transactions by cost center financial dimension for each user and the technical challenges behind it.

Product:

Dynamics 365 Finance and Operations

Problem:

Business requirement is that users should only view voucher transactions for their cost centers. Although we can define the owner for a cost center financial dimension but we cannot assign cost centers for each user in the application. Further, another business requirement is a user can be linked to as many as 500 cost centers!

Solution:

I designed a solution to achieve this business requirement by adding new table MAUserOperatingUnitTable to store user-cost center relationship.

Then the challenge was how to apply filter to the voucher transactions inquiry form so that it works for a user having access to as many as 500 cost centers. One option is to apply range to the DimensionAttributeValueCombination form datasource of LedgerTransVoucher form. However, since a user can have more than 100 cost centers, adding a range for each of these cost centers will result in the following error message:

Stack trace: Binding operation failed to allocate buffer space
Object server azure


The second option is go for an exists join query. The challenge with an exists join query is that if you see DimensionAttributeValueCombination table in AOT, it stores financial dimensions for voucher transactions in DisplayValue field which is a combination of financial dimensions and the main account as per the account structure and advanced rule structure configurations in the general ledger.

So we cannot do an exists join on DisplayValue field for a cost center value since parent table field must match with child table field for a successful exists join. Although DimensionAttributeValueCombination table in AOT does not show fields for individual financial dimensions, it does actually contain fields for these financial dimensions in the SQL table.

To reference COSTCENTERVALUE dynamic field in X++ for using it in exists join we can use the following method:

DimensionAttributeValueCombination::getDimensionValueFieldId

Please find the code snippet below.

/// /// This method adds a range to the form datasource to/// filter voucher transactions per user cost center./// Developed by Muhammad Anas Khan./// /// Form datasource/// Form datasource event args/// /// Developed by Muhammad Anas Khan./// [FormDataSourceEventHandler(formDataSourceStr(LedgerTransVoucher, GeneralJournalEntry), FormDataSourceEventType::QueryExecuting)]public static void GeneralJournalEntry_OnQueryExecuting(FormDataSource sender, FormDataSourceEventArgs e){ const Name dimensionCostCenter = "COSTCENTER"; FieldId costCenterValueFieldId; FormRun formRun = sender.formRun(); FormDataSource dimensionAttributeValueCombination_ds = formRun.dataSource(formDataSourceStr(LedgerTransVoucher, DimensionAttributeValueCombination)); QueryBuildRange qbr; QueryBuildDataSource qbds; str qbrValue; costCenterValueFieldId = DimensionAttributeValueCombination::getDimensionValueFieldId(dimensionCostCenter); qbds = sender.query().dataSourceName(dimensionAttributeValueCombination_ds.name()).addDataSource(tableNum(MAUserOperatingUnitTable)); qbds.joinMode(JoinMode::ExistsJoin); qbds.addLink(fieldNum(MAUserOperatingUnitTable, OMOperatingUnitNumber), costCenterValueFieldId); qbr = qbds.addRange(fieldNum(MAUserOperatingUnitTable, User)); qbr.value(curUserId());}



Источник: https://dynamicsaxinsight.wordpress....y-cost-center/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
dynamicsax-fico: Cost accounting (6) Blog bot DAX Blogs 0 13.08.2017 14:20
dynamics-coe: “Cost accounting” for multi-level process manufacturing Blog bot DAX Blogs 0 11.02.2016 22:12
dynamicsaxtraining: Purchase Blog bot DAX Blogs 0 11.03.2012 05:25
NAV Team: Simulation of Average Cost Calculation Blog bot Dynamics CRM: Blogs 0 13.01.2012 16:54
wiki.dynamicsbook: Changes Made in Navision Attain 3.60 Blog bot Dynamics CRM: Blogs 0 02.09.2008 13:23
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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