Max value within multiple rows

building reports desktopintelligence
1

Hello,

Before I pull out what’s left of my hair, I thought I would ask the experts.

I have a report that has PO, PO line, Receipt sequence and Receipt date. For every PO line, there is an existing Receipt record (sequence 0) that is blank. Then for every subsequent receipt, there is an increase in the sequence.

Example:

PO | PO Line | Receipt Seq | Receipt Date
55 | 10 | 0 | Blank
55 | 10 | 1 | Feb 11/2004
56 | 10 | 0 | Blank
57 | 10 | 0 | Blank

As you can see, some PO lines have a blank receipt date because they haven’t been received yet, but some have a receipt date because they have been received.

I want to filter for the max receipt date within the PO line (from the example above, I want to remove the PO 55, Line 10, sequence 0 record and only show the one with the receipt date). If the PO line has not been received yet, I want to still show it.

dusted (BOB member since 2003-09-18)

2

This is actually one of the more powerful features of the BusObj query panel. In the conditions box, place the Sequence object, choose equal to as the operator, and for the operand select Calculation (the last option). This will start the Calculation Wizard, which will let you specify that you want the max sequence for each PO, PO Line combination. Warning, along with “power” often comes complexity, and this wizard is definitely an intermediate / advanced functionality. Give it a try, and if need some help with it, let us know.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

This ROCKS!!! Thank you!

dusted (BOB member since 2003-09-18)

4

Yes it does! If you need some further reference material, our own beloved Debbie Kelly made this presentation at the International User Conference last year. Note that you will need a tech support ID to get the presentation from that site.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)