Solved

Generic Inquiry HAVING SQL Clause

  • 11 December 2020
  • 7 replies
  • 614 views

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.

icon

Best answer by TimRodman 23 December 2020, 06:06

View original

7 replies

Userlevel 6
Badge +5

@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 6
Badge +1

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.

Userlevel 2
Badge

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 7
Badge +11

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 6
Badge +1

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

Userlevel 7
Badge +8

Hello @TimRodman,

Can I create a KB article out of your brilliant solution?

Userlevel 6
Badge +1

Sure thing @Irina 

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 — 2024  Acumatica, Inc. All rights reserved