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 https://github.com/wojzeh/tmxProjectSalesPerSegment. Ping me, if you have any questions.

Related topics: 

Computed column for union values from multiple outer joined data sources in view