[Solved] GI with 2 or more different values
Just throwing out there to see if any ideas one has ... I need a GI which shows list of customers with their total sales and total payments with specific time period. If I choose to see all sales from Nov and also what payments I received from my customers during same time period, there could be orphan records or exception then how do we show 2 values from 2 diff DAC or is there a GI within a GI concept or how do we solve this?
The only way that I can think to do it would be to use subqueries.
And the only way I know of currently to do subqueries would be to create a SQL View and turn it into a DAC.
You might want to add your vote to this idea for subquiries in Generic Inquiries:
Also, Power BI (including the latest version of Excel) handles this kind of scenario quite nicely.
There is a trick I like to use in SQL to pull information together like this. The idea is to join on a list of numbers running from 1 to however many "types" of things you are trying to join on that you don't want interfering with each other. In your case, you are joining on 2 things, AR Invoice and AR Payment, so you need to be able to join on a 1 and a 2.
You'll need to guarantee a clean list of numbers for this to work. I made a combo attribute "MYPIVOT" and gave it values 1-10 with sort order 1-10 which was very quick and easy.
In your GI, you will want to start by adding tables: Customer, CSAttribute, ARInvoice, and ARPayment.
You first relation needs to be Customer to CSAttribute. CSAttributeDetail.AttributeID ='MYPIVOT' and CSAttributeDetail.SortOrder <= 2
If you would check, this gives you results like:
The idea now is to join ARInvoice to Customer and "1" and ARPayments to Customer and "2". I attached a sample GI of this. If you create the MYPIVOT attribute, you can see the results and how it behaves. If a Customer has only invoices and no payments, there will be "1" rows with values, and one "2" row with just NULLs. Once you get the hang of the concept, it can help get around the "No Sub-query" limitation in some cases.
Ahh, I am on 2019 R1. I took some screen shots to help you out.
You should be able to get around it by just using the pencil (or typing) and setting the fields manually:
The Validate button doesn't like it, but it will give you the results you want.
Holy cow, this is an awesome technique! I love it! Thanks for sharing @wyatt-erp!
I'm adding the phrase UNION ALL to this post since UNION ALL is how I would have done this in SQL. That way I can search Google for this phrase to find this post in the future:
"UNION ALL" site:augforums.com
I think this technique could also work for printing multiple copies of a report. And it's better than the "2 copies" technique mentioned below because you could do more than 2 copies.