Question

Generic Inquiry HAVING SQL Clause

  • 11 December 2020
  • 5 replies
  • 116 views

Userlevel 1
Badge

Does anyone have a clever way to use a Parameter in a Generic Inquiry to generate a HAVING clause in SQL right after the GROUP BY clause? It generates a WHERE clause which doesn't allow me to work with the aggregation results.

I can do an on-the-fly filter, but I was hoping to use a Parameter.


5 replies

Userlevel 4
Badge

@timrodman  Tim, it looks like we are seeking for same kind of function.
I don’t know how to achieve this directly in GI. However, I’ve thought out a workaround that might get the “Having” filter out results.
You can take a look at below:
 

 

I also would like to know if you have better thoughts.

Userlevel 3
Badge

You can do it when using an on-the-fly filter by clicking on a column heading and applying a filter. But it doesn’t look like it works with a Parameter.

Hi Tim,

 

I’m not sure regarding your particular case, but you can consider 2 possible workarounds:

  1. Use Pivot Tables. 
  2. Create custom DAC + SQL view with aggregation and base your GI on it. In this case you will work with parameter in WHERE clause.
Userlevel 4
Badge +4

Hi @TimRodman 

Can you attach your GI XML and point to the column and the way you want to see the filtering to work? We will explore further for a solution.

Regards,

Userlevel 3
Badge

Thanks for following up on this @mrysev and @vkumar.

I was able to come up with a pretty cool workaround with help from @wyattERP:

https://www.augforums.com/forums/acumatica-generic-inquiries/using-parameters-to-filter-aggregate-results

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