Formula Help

[micro2005]

CR 10 with SQL server 2k.
I need to create a formula. I am referencing two fields from two different tables. These tables are linked with an equal inner join. The fields are a date field and a description field.

What I need to do is that get the description field only when the date is the latest (max). Which means that there could be more than one descriptions for several dates. I just need to get the one for the latest date. I am not sure how to proceed with this.

Any help would be appreciated.

 

[synapsevampire]

I'm guessing that you're omitting the most imporytant field, which is what this description is for?

An example would be for a part:

Group the report by the part number

In the Report->Select formulas->Group place:

{table.date} = maximum({table.date},{table.partnumber})

Now you can place your fields in the group header or footer and you should only get the latest description for each part number.

If this doesn't help, perhaps you only expect one row for the whole report, in which case you'd change the formula to:

{table.date} = maximum({table.date})

Note that if you have the same dates for the same entity (such as part number), this will show all of them as they all equal the maximum.

-k

 

[micro2005]

I am sorry for not giving enough information. Let me try it again. I have a report which is currently grouped on {Complaint.id}. Now what I need to do is get the report for all the formal complaints so I entered {complaint.complainttype} = "F" in the record selection. Now I have two other tables.
1. Complaintevents (contains the field start date)
2. Eventssubtype_ref (contains the description field)

There are several events entered in the eventsubtype_ref. I just need to get the last action performed. This is why I want to get the descrtiption field from the eventssubtype_ref table only for the latest start date from the Complaintevents table.

I hope this makes sense. If you need more clarification please let me know.
I appreciate your help.

 

[synapsevampire]

Exactly the scenario I supplied.

In the Report->Select formulas->Group place:

{Complaintevents.date} = maximum({Complaintevents.date},{table.Complaint.id})

You should now only get the maximum date for each complaintid displayed.

-k

 

[micro2005]

I tried your suggestion and when I enter this in the Group selection I get a message:

There must be a group that matches this field.

Everything after the "=" is highlighted.

Even if it worked this fromula would work on the group level. I dont want it work on the group level. There are other date fields in the detail section which might get affected by this. The field I am working on is in the details section.

Is there any way I can create a formula or a SQL expression to get what I am trying to get?

Here is what my report looks like

Group Level (compalint ID)

Details has these fields

Type
Complaintant
Date of formal filed
Age of case Last
Office
Last Event Action
Last Event Date

I was able to get the last event date by creating a formula
@lasteventdate
maximum ({complaintevents.startdate})
Now I need to create a formula for Last event action. This is the scenario which I described before. I need the {eventsubtype_ref.description} where ({complaintevents.startdate}) is maximum. I need to place this formula in the details section. Hope this makes more sense. If not, please let me know and I will try to explain again.

Thank you for your patience.