I am building a BAQ that provides a clean list of Aged Receivables by Customer - taking into account both open invoices and balance remaining (in case partial payments have been received).
Using subqueries, I am able to get a top level table of all customers (one customer per row) along with their open balance due. I would like to provide a ranking column to this table - by using the calculated balance due as the input to the rank. Thus, the customer with the largest amount due would be ranked “1”, the next largest amount due would be ranked “2”, and so forth.
I see there is a standard SQL function called RANK(), but am struggling with the syntax to actually using it within the calculated BAQ field.
Epicor Ver 10.1.400.x
Anyone have experience with this type of BAQ calculated column?
Thanks - Jeremy J
I forgot to address the “fixed number” element to my title.
Ideally, once I have the ranking column available, I will use it to generate a “Top 25” list from all customer that have balance due. I know how to complete that task - if I have the rank integer column available.
Thanks in advance for any help!
Looks like I was able to come up with the right syntax and solve this issue with more detailed Internet searching. Just adding my outcome into this thread so that others may benefit from my positive outcome.
Key elements for a column of rankings relative to a BAQ query are:
- Create a Calculated field that is of integer type
- DENSE_RANK is the proper function to use
- Call out the Table.Field that you want to order within the ORDER BY ()
- I needed to invert the results to return the highest value as Rank=1 by adding a " *-1 " to the order by calculation. This is likely related to how Epicor natively is storing the field - either as a positive or negative value.
Primary learning for me here is that the SQL Query Phrase displayed in the General tab will follow the proper SQL syntax from general SQL query help searching online - you just simply need to use the Epicor BAQ Calculated Field SQL Editor with several iterations to build the proper syntax. Going back and forth between the two interfaces was useful.
5 Likes