We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Crystal Report that looks like the most recent transaction/date

schraudog
schraudog asked
on
Medium Priority
489 Views
Last Modified: 2012-05-11

I am trying to modify a report that looks at several dates when the data was modified. For instance:

Order No:12345 Date Processed: 1/1/2011 9am   Hours assigned to Order:3
Order No:12345 Date Processed:1/1/2011 3pm    Hours assigned to Order:3

So in this case I want the latest Processed date (3pm) and only 3 hours for the (3pm) order. Any suggestions?
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Do you need the other records for that order in the report?

One way to do this is to group by the order number
Sort by the date time field
The record you want is in the group header if you sort descending otherwise it is in the group footer.

mlmcc

Author

Commented:

Yes, I do.

I basically want to eliminate the oldest transaction for a particular order. Then sum up all the orders hours. If I were to do as you suggest I would get duplicates like i do now and my numbers would be severely inflated.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can an order have more than 2 records?
If so what do you want?

What do you want displayed on the report?

What totals are you calculating?

mlmcc

Author

Commented:

Each order can potentially have several steps. For instance, Order 1234 Can have a step 1, 2, 3.
My problem is that if the order is modified more than once, first at 9am and the second at 3pm. I only want to see and calculate the hours for the 3pm modification.

I only want to see the most recent modification for each order and its steps. I am calculating the total hours for each order and its steps.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
So if it has a 9am, 11am and 4pm mod you only need the 4pm time?

Do you need the other records at all for the report?

mlmcc

Author

Commented:
No, just the the most recent modification.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
You could use SQL like

SELECT OrderNumber,  Max(ModDate) AS LastMod
FROM YourTable
GROUP BY OrderNumber

That should get your order with only the last modification date.

mlmcc

Author

Commented:

That was very helpful however I now have another problem. The hours that were removed by going with the most recent date are still being calculated on my Orderno summary. Is there a way just to get a sum of the hours being displayed only?
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Is the display correct?

Where did you use the SQL?

mlmcc
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:

What I ended up doing was creating a formula for the (ModDate) field. Here it how it looks:
{ModDate} = Maximum ({ModDate},{Order.JobNo}). I also changed the last table to Left inner join.  

Thanks for your help!  

Author

Commented:
It was the SQL type of answer, I had to figure how to use that logic in Crystal but mlmcc got be started. This site is a great resource.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.