Maximum of field not in group formula

[djieon]

Hi All,

I have a report that has a row with a unique ID for each item, but can have several records against each ID with a date tested, IE unique ID '1' may have been tested on 07/05/2010, 07/05/2011 and 07/05/2012 so there is 3 records against ID 1.

I want a way of the report to only bring back the latest test, so at the moment I am using the formula

maximum ({RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE},{RM_COMMON_ATTRIBUTES.ITEM_UID})

This is ok but my problem is that I then want to be able to sort in the order of the earliest test first (so i can find out the most overdue tests!) at the moment becuase the group is on the unique ID I cant get it to sort in the order I want?

Is there a way to put this in the record select instead of in a group? or a better way of doing this...

Thanks in advance.

David.

 

[CoSpringsGuy]

Can you do group sorting?
select report -> Group sort expert -> All -> Max date -> descending

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

Hi no this does not appear to be working, when I try to do this it brings back all my records...

any other ideas?

Thanks.

 

[CoSpringsGuy]

in sorting .. instead of selecting ALL select Top N then enter 1.

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

in addition, on the right hand side in Group Sort expert, uncheck include Others, with the name:

then if you happen to have more than one group which may have the latest date select the option to include ties

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

no still struggling, it does not change, it brings all my results back.

At the moment I have

Group 1 - item uid
Group 2 - last test date

any my fields in the 'details' section.

In the details suppress X2 I have the following formula

maximum ({RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE},{RM_COMMON_ATTRIBUTES.ITEM_UID})

am I going about this right?

Thanks once again

 

[CoSpringsGuy]

I dont believe you need the second group.
Try this.. I tested on my database and if i understand your goal it works ...

-Group on user ID only
-add a summary field for max date
-add the fields you want to see in details
-add a suppression in details section like this
{RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE} <> maximum ({RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE},{RM_COMMON_ATTRIBUTES.ITEM_UID})

- Group Sort expert, group by top N, uncheck include Others, with the name:,select the option to include ties
-hide header
-hide footer


_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

Thanks for that, seems to be working so far, now what I need to do is I have a field called @datediff and I want the following to happen:-

sort in order by the field @datediff

hide/supress anything where the field @datediff is less than or equal to 0

is that easy? Thanks for all your help.

Cheers

D.

 

[CoSpringsGuy]

ok but first ... the process I gave you will return the latest date in the entire record selection and return all user IDs who share that date. Is that your goal or do you want to list all User IDs and the latest test date for each?


_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

Hi, I want to list all user ID's and the latest test date for each...

 

[CoSpringsGuy]

OK sorry ... do this then

-Group one user ID
-Group two {RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE} (make sure it is grouped by day)
-put fields you want to see in details section
-Group sort Expert first tab (user id) no sort
-Group sort Expert second tab Top N (1), uncheck include Others, with the name:,select the option to include ties
- suppress details with {Call_Logging_Audit.Date_Call_Began} <> Maximum ({Call_Logging_Audit.Date_Call_Began}, {Call_Logging_Audit.Date_Call_Began}, "daily")
-hide footers and headers for both groups

provide the code for your datediff formula so I can see how to incorporate

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

sorry about the suppression formula .. thats from my database .. replace with appropriate feilds from yours

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

Hi ill give this a try, whilst doing that here is the code your after


//@datediff
datediff ("d",{@test by},today)

//@testby
if isnull ({RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE}) then date (1900,01,01) else

if {RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE} = date (9999,01,01) then date (1900,01,01) else

if {RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE} = date (9999,01,18) then date (1900,01,01) else

if {RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE} = date (9999,01,20) then date (1900,01,01) else

dateadd ("yyyy",6,{RM_INVENTORY_ELECTRICAL_TESTING.ELECTRICAL_TEST_DATE})

probably a lot quicker way of doing this!!

 

[djieon]

I dont get a 2nd tab in group sort expert, I only get one tab?

 

[djieon]

scrap that, i had not clicked on the group 2 field name to bring up the tab for group 2, all seems to be working now...

 

[djieon]

when i mean all working, its still not sorting and removing the records I dont want to see, i assume your still working on that! thanks for this, really appreiate it!

 

[CoSpringsGuy]

so what exactly is working?

_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

ok I have got as far as getting one electrical test date (the latest one) per every unique ID

Next I have a field that tells me how many days overdue the test is, using the formulas I posted above. I want to sort the list so that the most over due is at the top of the sheet, and then I want to hide anything that is not overdue.

Any ideas?

Thanks.

 

[CoSpringsGuy]

try this .. I have not tested ...

right click your formula in detail.. summary with maximum to your GF1
go back to group sort expert
select first tab (user ID)
sort ALL
on right hand side select the summary field you just created then select descending



_____________________________________
Crystal Reports 2008 and XI
Intersystems Cache 2012 ODBC connection

 

[djieon]

No this does not appear to have worked, I am getting duplicate user ID's again...