Sorting Formula for Crystal Reports Needed ASAP

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)
Machine Name 
Revision Number
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
Thank you

Open in new window

SQLwatcherExpert Technical Support ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MIKESoftware Solutions ConsultantCommented:
Add GROUP for MACHINE...then subtotal there...hide DETAILS for the group.....
MIKESoftware Solutions ConsultantCommented:
You'll need to display your column data within the FOOTER for the MACHINE group...and again...hide whatever sections you don't need....I'm thinking the DETAILS and GROUP HEADER for the MACHINE group.

Make sure the display for the LATEST REVISION is showing the trick is to copy/paste the object from the DETAILS section into the MACHINE GROUP will auto set to MAX for the field.

LET me know if this is what you are looking for.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SQLwatcherExpert Technical Support ConsultantAuthor Commented:
I have the following groups:

Date, Zone, Area, Location, Machine-
I put objects into the machine footer and sorted in ascending order, so the last one shows first.  I tried to sort with max but that option was not available. The revision number is an object not a summary, so I was thinking I could sort with TopN but that does not appear to be an option since I am not working with summaries.  I think the key is we did not have the group headers visible, therefore showing as subcategories however that does not help me only show the max value.    

So, if I follow you mentally, put the machine name and revision into DETAILS then COPY and place into the MACHINE FOOTER and then sort on MAX.  This action will force the MAX on the revision number.  

Since this is not a summary field, forgive my stupidity,,how do I sort on Max -- or just rely on CR to do it for me?

This should not be difficult....pretty much the report just needs to show the lastest revision for the machine and where it resides now....some times the easy ones elude me......

I will give that a  try...

PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Can you add a sort based on the revision number field?

If you make descendingthen the record informatiion goes in the machine group header.  If you make it descending then put the information in the machine group footer.  You don't need anything in the details.,

SQLwatcherExpert Technical Support ConsultantAuthor Commented:
I placed the machine name and revision number into the machine group footer before I began this post.  I will try it again and post a response.  Thank you again
Define conditional totals that give you the MIN date in service but the MAX revision number and put those fields into a group footer set to the machine ID.  It looks like you'd display the record line to show all your detail as well.
SQLwatcherExpert Technical Support ConsultantAuthor Commented:
Thank you
MIKESoftware Solutions ConsultantCommented:
I'm glad I was able to help out.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.