Solved

GI count unique/district

  • 21 February 2022
  • 6 replies
  • 430 views
A
Rank
Userlevel 3
Badge

I have a GI, Shipment Details, that is driven off SOShipment, SOShipLine, and SOOrder to shows each line of a shipment and its corresponding order. It is currently serving its primary function, but I would to get more out of it by reporting the number of distinct/unique shipments there are.

Is there a way to do a distinct/unique count in the GI results grid? I know this can be done easily through a pivot table, but my goal is to have this information right at the bottom of the GI results grid by using the Total Aggregate Function. Having it hear saves that extra click and load time that pivot tables inevitability have.

Another way of phrasing this is, does anyone have an equations that will count a shipment number once?

My first idea was to divide 1 by the number of lines on a shipment. When added back up each shipment would equal 1 and all of them would give me the shipments count. 

icon

Best answer by lauraj46 22 February 2022, 17:17

View original

6 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @abrunner78 ,

I think you could accomplish this using self joins on the SOShipLine table.  By comparing line numbers, you could count and sum only the rows with the highest line number on each shipment.  You do need to be careful, if you have many lines on each shipment then this query might not run efficiently because of the self joins since it must compare every line to every other line on each shipment.  In my test environment with 4000+ shipments and 12000+ lines the performance seemed to be fine.

The XML is attached.  Sample output below:

 

 

Hope this helps!

Laura

1 Attachment
Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Chris Hackett
Rank
Userlevel 7
Badge

Hi @abrunner78 did the solution offered by @lauraj46 help solve your issue? Thank you!

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
A
Rank
Userlevel 3
Badge

I haven’t responded since I haven’t had the chance to test it, but I suspect it is ultimately going to be the solution. 

TimRodman
Rank
Userlevel 6
Badge +1

@abrunner78 It would be nice to have a DISTINCTCOUNT option available instead of just COUNT. This is an example of why I tend to go with Power BI when I’m getting into an analytical situation since you don’t run into these kinds of limitations in Power BI. But, then you’re using an outside tool which has downsides.

Acumatica Podcast: AugForums.com/Podcast ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ Video Training Course on Acumatica Generic Inquiries: AugForums.com/Course ⚡⚡⚡⚡⚡⚡ Independent Consulting, focused on Reporting in Acumatica: AugForums.com/Consulting
A
Rank
Userlevel 3
Badge

@TimRodman I totally agree. The even more frustrating part is that Acumatica does have ‘Count Distinct’ in pivot tables and widgets. I would have expected them to unify they aggregate functions.  

TimRodman
Rank
Userlevel 6
Badge +1

@abrunner78 Ya, it’s just yet another feature that would require developer time. There are hundreds of things like this that illustrate the difference between a “Reporting” tool like we have in Acumatica and a “Business Intelligence” tool like Power BI.

Acumatica Podcast: AugForums.com/Podcast ⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡⚡ Video Training Course on Acumatica Generic Inquiries: AugForums.com/Course ⚡⚡⚡⚡⚡⚡ Independent Consulting, focused on Reporting in Acumatica: AugForums.com/Consulting

Reply


Terms & ConditionsCookie settings
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