cancel
Showing results for 
Search instead for 
Did you mean: 

Filter for most recent date

Former Member
0 Kudos

Hi All,

I have a report that simplified looks as follows

Member Number          Date of Evaluation               Evaluation Result

1                                12/1/12                               100%

1                                11/1/12                                50%

1                                 10/1/12                              75%

2                                 11/30/12                             60%

2                                  10/15/12                            75%

2                                 9/15/12                               80%

etc.

I want to filter based on the most recent evaluation date.  The most recent can be any date so I can't filter based on a date range.  I just need in the above example for the first row from each client to display.  Any suggestions?

Thanks,

Mike

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Michael,

Here's what you need to do:

1) Create a group on Member Number from the Group Expert

2) Create another group on Date of Evaluation. While in the Group Expert Select the Date Field > Click the Options button > Select "For each day" from the drop-down for "this section will be printed"

3) Suppress Group Header 1, Group Footer 1, Group Footer 2 and Details Section

4) Place all the fields in the Group Header 2 section

5) Go to the Report tab on the top and click on Selection Formulas > Group and use this formula:

{Date_of_Evaluation} = Maximum({Date_of_Evaluation},{Member Number})

That's it! Let me know how this goes!

-Abhilash

JWiseman
Active Contributor
0 Kudos

hi Michael,

the method that Abhilash suggested has the advantage of bringing back the additional records for each Member so that they can also be used in the report for summaries, charts etc. even if you're hiding them from the details section. it shows the advantages of using the Group Selection formula.

if you don't want to bring back these additional records at all, i.e. for performance records against a large database, then you've got a couple of other options.

option 1 > change your report to use a Command object instead of tables...the sample syntax below is based on Access syntax and therefore your syntax may vary. this type of query will limit the records being returned to just one record per Member.

SELECT

`YourTable`.`YourDate`,

`YourTable`.`MemberNumber`,

`YourTable`.`EvaluationResult`,

(

SELECT MAX(`YT2`.`YourDate`)

FROM YourTable YT2

WHERE `YT2`.`MemberNumber` = `YourTable`.`MemberNumber`

)

FROM   `YourTable` `YourTable`

WHERE  `YourTable`.`YourDate`=(

SELECT MAX(`YT2`.`YourDate`)

FROM YourTable YT2

WHERE `YT2`.`MemberNumber` = `YourTable`.`MemberNumber`

)

ORDER BY `YourTable`.`MemberNumber`

option 2 > use a sql expression in your current report to get the max date

this is not officially supported but usually works anyway so you can decide if you want to go this route

- in your Field Explorer create a new SQL Expression called MaxDate

- the MaxDate syntax will be something like below...again your syntax may vary depending on your database.

(

SELECT MAX(`YT2`.`YourDate`)

FROM YourTable YT2

WHERE `YT2`.`MemberNumber` = `YourTable`.`MemberNumber`

)

- now go to your Record Selection formula and change it so that you have a selection filter like

{YourTable.YourDate} = {%MaxDate}