I need to display data in a Cystal Reports XI based on the following elements:
Date Put into Service
Machine - only highest revision number, such as 1,2,3
I can get the machine to display with the highest rev value only if I do not enter the location variables into the grouping....
I need the report to pull according to the groups shown above but only want the machine to show once, based on the highest revision number.
So, a machine may have been placed into service on 3.4.08 - Rev1..then moved on 8.8.08, Rev 2 ....then 11.1.08 Rev 3. I need the Original date placed into service (3.4.08) but the current location and Rev3 only.
Does not appear to be difficult mentally but keep missing something to get this to work.
The Machine number is identified by machine_id.
Querying the main table returns the following results:
machine_id Area Location Zone Revision DIS
3221 1 5 T 1 1/1/2008
3221 1 7 Z 2 1/1/2008
3221 1 3 R 3 1/1/2008
My Sort Order must be:
Date in Service, (DIS)
We only want the machine with the latest revision to appear on the report. I can not suppress 1 & 2 because other machines have revision 1 & 2.
I have created groups for the above and sorted on machine and revision but the machines still appear under each zone they had previously been.
I was thinking about a suppression formula that, in essence states :
If there is a 1 present but no following revision, then display 1
It there is a 2 present but no following revision, then display 2
If there is a 3 present but not following revision, then display 3
Just not sure how to get the formula to suppress the records for revision 1 & 2