Solved

How to do a proper grouping after a full join in report designer

  • 18 January 2021
  • 2 replies
  • 83 views

Userlevel 5
Badge +3

Hello,

         I have a requirement to display all invoice lines and its related shiplines(the key is the shipped 0 line, which has not converted into invoice) by invoice# 
        I can get the data using the sql below:

 

select* from ARTran a 
full join SOShipLine c on a.SOShipmentLineNbr=c.LineNbr and a.SOShipmentNbr=c.ShipmentNbr
left join SOOrderShipment b on c.ShipmentNbr=b.ShipmentNbr
where 
a.RefNbr='xxxxx' 
or 
b.InvoiceNbr='xxxxx'

 

The key here is the “or” where condition.

 

However , when it comes to report designer

I can only either group by artran.ref / arinvoice.ref

or

group by soordershipment.invoicenbr

 

either way, I would get data lost. 

      

icon

Best answer by lauraj46 27 October 2021, 16:05

View original

2 replies

Userlevel 3
Badge +1

Hi @ray20 ,

I just came across this post… maybe you’ve already got the answer. 

If I’m understanding you correctly, I think you should be able to do a conditional grouping by using a formula in the grouping expression, something like this:

 

 

For the data field, you could use an expression like this one: =IsNull([SOOrderShipment.InvoiceNbr],[ARTran.RefNbr])

Hope that helps!

Laura

Userlevel 5
Badge +3

@lauraj46 Thank you for your trick. I did not know this talent move before.
I was using a sub report to solve my need before.
Next time, I will try this method. Thank you.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2020  Acumatica, Inc. All rights reserved