[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

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.



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

Open in new window

0
SQLwatcher
Asked:
SQLwatcher
1 Solution
 
MIKESoftware Solutions ConsultantCommented:
Add GROUP for MACHINE...then subtotal there...hide DETAILS for the group.....
0
 
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 MAX...one trick is to copy/paste the object from the DETAILS section into the MACHINE GROUP FOOTER....it will auto set to MAX for the field.

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

M
0
 
SQLwatcherAuthor 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...

Thanks
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
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
0
 
SQLwatcherAuthor 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
0
 
cthulthuCommented:
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.
0
 
SQLwatcherAuthor Commented:
Thank you
0
 
MIKESoftware Solutions ConsultantCommented:
I'm glad I was able to help out.

M
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now