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

Опции темы Поиск в этой теме Опции просмотра
Старый 30.06.2022, 08:12   #1  
Blog bot is offline
Blog bot
25,006 / 827 (78) +++++++
Регистрация: 28.10.2006
Union query for Project transactions

Union query may be a very efficient and useful option when you need to fetch similar data fields from different tables. A good example of this may be the case when you to gather information about project related transactions as they can be of different types, like, expenses, fees, items, etc.

Say, we need to render some report collecting all these different types of transaction that can be posted against a given project and group them by a given financial dimension value in Project group. Let's see how Union query can help us.

In order to better understand the goal, you can take a look to the standard form ProjInvoiceJournal which perfectly explains how all these table relate to each other.

Most of them contain the same set of fields, and we simply need to get some of them into one view to populate by the report data provider.

So, at the first step, let's create all necessary queries for the source Project transaction related tables. Pick up the first and create a simple query as depicted.

Then create a view based on this query.

Then pick up the next and do the same.

Complete these two steps for all necessary transaction types.

Now you can create a Union query and add all your views together as they have the same set of fields.

If you need to distinguish them later who is who, say, in computed column methods, you can use unionAllBranchId field, but this is out of the current focus.

Finally you can elaborate it by adding a new view based on the latter with a simple SUM aggregation for Amount field.

Basically, you achieve your goal with no coding.

I am not going into details about the whole project, which you can get by this URL Ping me, if you have any questions.

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
emeadaxsupport: Microsoft Dynamics AX general performance analysis scripts page 5 Blog bot DAX Blogs 0 01.09.2014 14:11
dynamicsaxtraining: Purchase Blog bot DAX Blogs 0 11.03.2012 05:25
NAV Sustained Engineering Team Blog: Simple query to check the recent performance history II - now including Query Plan information Blog bot Dynamics CRM: Blogs 0 14.10.2008 13:01
palleagermark: Sample union query from AX 2009 Blog bot DAX Blogs 0 11.07.2008 20:05
Опции темы Поиск в этой теме
Поиск в этой теме:

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

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

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

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