Skip to main content

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.

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

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.

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,

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

Hello @TimRodman,

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

Sure thing @Irina 

Reply


Terms & ConditionsCookie settings