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.
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 https://github.com/wojzeh/tmxProjectSalesPerSegment. 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|
|Опции темы||Поиск в этой теме|