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

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

Crystal Report that looks like the most recent transaction/date


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?
0
schraudog
Asked:
schraudog
  • 6
  • 6
1 Solution
 
mlmccCommented:
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
0
 
schraudogAuthor 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.
0
 
mlmccCommented:
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
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
schraudogAuthor 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.
0
 
mlmccCommented:
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
0
 
schraudogAuthor Commented:
No, just the the most recent modification.
0
 
mlmccCommented:
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
0
 
schraudogAuthor 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?
0
 
mlmccCommented:
Is the display correct?

Where did you use the SQL?

mlmcc
0
 
mlmccCommented:
You could try

SELECT OrderNumber,  Max(ModDate) AS LastMod, Last(HoursField) as LastHours
FROM YourTable
GROUP BY OrderNumber

mlmcc
0
 
schraudogAuthor 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!  
0
 
schraudogAuthor 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.
0

Featured Post

New feature and membership benefit!

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

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