Skip to main content
Solved

Generic Inquiry HAVING SQL Clause

  • 11 December 2020
  • 7 replies
  • 746 views

rodman
Freshman I

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.

Best answer by TimRodman

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

View original
Did this topic help you find an answer to your question?

7 replies

Forum|alt.badge.img+5
  • Captain II
  • 398 replies
  • December 11, 2020

@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:
 

https://community.acumatica.com/reports-and-generic-inquires-115/can-we-filter-sorting-gi-lines-by-highlighted-color-4226

 

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


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • December 11, 2020

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.


Forum|alt.badge.img
  • Acumatica Support Team
  • 13 replies
  • December 23, 2020

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.

Forum|alt.badge.img+11
  • Acumatica Support Team
  • 785 replies
  • December 23, 2020

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,


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • Answer
  • December 23, 2020

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


Irina
Community Manager
Forum|alt.badge.img+8
  • Acumatica Support Analyst
  • 504 replies
  • February 2, 2021

Hello @TimRodman,

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


TimRodman
Pro I
Forum|alt.badge.img+1
  • Pro I
  • 144 replies
  • February 2, 2021

Sure thing @Irina 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings