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
Area
Location
Zone
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.
Date Put into Service
Area
Location
Zone
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)
Area
Location
Zone
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
Marcus Aurelius
Add GROUP for MACHINE...then subtotal there...hide DETAILS for the group.....
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
Thanks
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...
Thanks
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.,
mlmcc
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.,
mlmcc
ASKER
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.
ASKER
Thank you
I'm glad I was able to help out.
M
M