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

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 20.08.2019, 15:09   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
How to concatenate financial dimension values in a View for a given hierarchy
Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
==============

Following my previous posting How to filter existing transactions based on a financial dimension value set ,
I would like to show how we can construct a view with all financial dimenion values for a given hierarchy, including placeholders for those values are absent.

In the aforementioned example, it was CDPDimensionAttributeValuesUnionConcatView view as depicted.



Instead of a series of dependant views, we can create one view as follows (CDPDimAttrSelectedView is just a set of selected attributes; explained in the previous link)





The key point is a computed column method, which creates a final string in the same sequence of attributes as selected in the hierarchy.



private static server str finDimValues() // X++
{
return @"STUFF((SELECT '-' +
ISNULL(
STUFF((SELECT '-' + t3.DisplayValue
from DimensionAttributeValueSetItemView as t3
JOIN DimensionHierarchyLevel as t17
on t17.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
join CDPFinDimAttrForAggr t25
on
t17.DIMENSIONHIERARCHY = t25.DIMENSIONHIERARCHY
where
t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet
and t7.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
order by t17.LEVEL_
FOR XML path('')
), 1, 1, '')
, 'N/A')

FROM DIMENSIONATTRIBUTE t6
JOIN DimensionHierarchyLevel as t7
on t7.DIMENSIONATTRIBUTE = t6.RECID
join CDPFinDimAttrForAggr t15
on
t7.DIMENSIONHIERARCHY = t15.DIMENSIONHIERARCHY
FOR XML PATH('')), 1, 1, '')";

}





Thanks a lot for all participants on the forum thread, and especially to Kair84 who helped me with SQL command.




Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
Старый 20.08.2019, 15:11   #2  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
How to concatenate financial dimension values in a View for a given hierarchy
Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
==============

Following my previous posting How to filter existing transactions based on a financial dimension value set ,
I would like to show how we can construct a view with all financial dimenion values for a given hierarchy, including placeholders for those values are absent.

In the aforementioned example, it was CDPDimensionAttributeValuesUnionConcatView view as depicted.



Instead of a series of dependant views, we can create one view as follows (CDPDimAttrSelectedView is just a set of selected attributes; explained in the previous link)





The key point is a computed column method, which creates a final string in the same sequence of attributes as selected in the hierarchy.



private static server str finDimValues() // X++
{
return @"STUFF((SELECT '-' +
ISNULL(
STUFF((SELECT '-' + t3.DisplayValue
from DimensionAttributeValueSetItemView as t3
JOIN DimensionHierarchyLevel as t17
on t17.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
join CDPFinDimAttrForAggr t25
on
t17.DIMENSIONHIERARCHY = t25.DIMENSIONHIERARCHY
where
t1.DimensionAttributeValueSet = t3.DimensionAttributeValueSet
and t7.DIMENSIONATTRIBUTE = t3.DIMENSIONATTRIBUTE
order by t17.LEVEL_
FOR XML path('')
), 1, 1, '')
, 'N/A')

FROM DIMENSIONATTRIBUTE t6
JOIN DimensionHierarchyLevel as t7
on t7.DIMENSIONATTRIBUTE = t6.RECID
join CDPFinDimAttrForAggr t15
on
t7.DIMENSIONHIERARCHY = t15.DIMENSIONHIERARCHY
FOR XML PATH('')), 1, 1, '')";

}





Thanks a lot for all participants on the forum thread, and especially to Kair84 who helped me with SQL command.




Источник: http://alexvoy.blogspot.com/2019/08/...dimension.html
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
How to filter existing transactions based on a financial dimension value set Blog bot DAX Blogs 31 25.09.2019 16:33
How to lookup and set a new value for Financial dimension Blog bot DAX Blogs 0 03.10.2015 02:35
ax_gfm_framework_team: Dimension defaulting in accounting distributions, Part 3 - Splitting and defaulting using Financial dimension default templates and Main account allocation rules Blog bot DAX Blogs 0 18.12.2013 09:11
NAV Team: Filtering on Dimension Values Blog bot Dynamics CRM: Blogs 0 12.06.2012 18:02
Khue Trinh: Setting up a link between a financial dimension and the site inventory dimension Blog bot DAX Blogs 0 27.09.2010 13:05
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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