Generic Inquiry HAVING SQL Clause

  • 11 December 2020
  • 5 replies

Userlevel 1

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

@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

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.


Userlevel 3

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

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


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