Getting Distinct Values in Framework Manager

[gosoccer]

Hi everyone,  :)
I have a Subject Query that is returning duplicate records based on a Car ID.

Unfortunately, in my Subject Query, the following statement results to:

select CAR ID, REPAIR DATE, STATUS
from CARS

CAR ID       REPAIR DATE      STATUS
11111        10/25/1995         Fixed
11111        10/19/1995         NEEDS REPAIR

Even after I use the following approaches, I'm still getting dups:

select DISTINCT CAR ID, REPAIR DATE, STATUS
from CARS

I'm trying to get the oldest record from the above for this Subject Q.
11111        10/19/1995         NEEDS REPAIR

But no luck!

Any help on this, I'll greatly appreciate it.

Thx in advance! :( :(

[gosoccer]

This one is resolved!!!  :) :)

[bus_pass_man]

Jolly good.  Are you at liberty to explain the solution for those who might be curious?

[gosoccer]

Sure, Lynn actually provided suggestions also :)

1) Created the Data Item as a part of the Report Query.
2) Added Case Statement shown below,
case
   when [View].[CAR].[CD] = '9'
   then [View].[CAR].[JAGUAR_AUTO_REC_ID]
   else null
end
3) Established the Aggregation Func. to Count for the Data Item.

The Report now shows the correct counts as a part of the Crosstab.

Thx for all Lynn's and everybody's help.

Hope this helps! 8)