Showing latest date from group. 2

[Jackmetickler]

Hello there

This is one of those I am sure I have done before but for the life of me I cannot get it right.

SQL2005 CRX1

I have a part field and a date field showing the dates that a part has been issued to a job and the cost of that issue.
The rpt is grouped on the part number. Therefore I have a number of issue dates for each part.

What I want to do is just show the line which was the last date it was issued.

I have a feeling this is going to be something I should know but I have been on holiday for 2 weeks and I obviously need re-training.

Any assistance/pointers much appreciated
Jack

 

[synapsevampire]

You have two quick means of doing so:

In the Report->Selection Formula->Group place:

{table.date} = maximum({table.dazte},{table.part})

Or you can just sort the dates ascending (since you're already grouped), and place the fields in the group footer and suppress the details.

-k

 

[Jackmetickler]

Many thanks for your help.
I took the formula route because I also wanted to sum the cost of the part and it worked A1. However that brought another problem.

The formula shows the maximum transaction date exactly as I asked for but if I then sum the costs of the part, the sum is of all the transactions for that group of parts not just the last transaction.

I should have explained this in my post but explaining everything got a bit messy.

Is there any way round this - presuming I am making some sense here.

Your help much appreciated
Jack

 

[lbass]

When using group selection, you have to use running totals, since non-group selected records contribute to inserted summaries. You should be able to use sum of cost, evaluate for each record, reset on change of group. The running total will not pick up the non-group selected records.

-LB

 

[goranm]

Hi!

Maybe you can try this:
Create a formula, like synapsevampire suggested for the Group selection.
maximum({table.dazte},{table.part})
Place the formula in your part no group.

/Goran

 

[mwmark]

I am creating an inventory reorder report and have a similar question.
I want to display only the last date purchased of the selected sku_id.
I grouped by Sku_id and suppress the details, then added the purchase date to the group footer with other displayed info. This gives me the first purchase date not the last.

What am I doing wrong???

 

[Madawc]

mwmark, check Report > Record Sort Expert. Probably the date is Decending, which Crystal understands as most recent first. (It is also best to start a new thread, referencing the old one if necessary, and to give your Crystal version.)

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10