Problem with ordering by formula value

[MadCatmk2]

Hi all

Crystal 9
Access 97

I have a problem with the report i am writing. At present i have details of visits made to patients.
i.e.
Patient_No, Patient_Referral_key, Referral_Date, Discharge_Date, Last_Contact_Made_Date, Date_Diff

i am grouping on the Patient_Referral_Key. This key may relate to many visits so the Last_Contact_Made_Date field is a formula which gets the maximum date in the visits field for Patient_Referral_Key. I have a formula called Date_Diff which calculates the difference in months between the last contacts and todays date. This all works. The problem i have is when i try to order the output so that the largest Date_Diff is shown first and goes down to the lowest.

The formula is not available for selecting as a group or for ordering. Could anyone shed some light on how this could be done as i am having no joy whatsoever.

Hope that made sense

Thanks in advance

 

[bdreed35]

You should be able to group on the formula if you are just doing a date diff.

Please post the date diff formula so we can see what you are doing in it. If there are any other formuals referenced in there, please post them as well.

~Brian

 

[MadCatmk2]

Thanks for the response.

Date_Diff is as follows:

datediff("M", Maximum ({CONTACTS.DATE_OF_VISIT}, {PATIENT_REFERRAL.PATIENT_REFERRAL_KEY}), {@CurrentDate})

@CurrentDate is:

today

the maximum call gets the latest visit date within that particular patient_referral_key

Thanks again.

 

[bdreed35]

The reason you can't use that formula is because of the maximum function.

I don't see why you need to group on the number of months since it will be the same for every record within that referral key. If you are trying to get the number of months since the last visit, you can do that a different way.

I would place the Date_Diff formula in your referral key group header. so you can get the number of months.
If you need information from the last visit, sort the records on last contact made date descending. You can copy any fields you need from the detail section to the group header. I would aslo suppress the details section ifyo udon't need it.

~Brian

 

[MadCatmk2]

The formula is in the header already but i now see what your saying. If i change the date_diff formula to just work on the visit date, which i will have set to descending so's to have the maximum value at the top.

I'll go give that a try.

Thanks very much for your advice, its much appreciated.

 

[lbass]

But are you trying to order the patient key groups in descending order by magnitude of the datediff?

-LB

 

[MadCatmk2]

LBass:

I want the data ordered by the date_diff formula. The Patient_Referral_Key which hasn't had a visit for the longest time at the top of the list and then descending.

Does that make sense?

 

[MadCatmk2]

I cannot get this working properly now. When i try to order on the diff_date it takes into account all of the contacts as apposed to just the latest contact (As shown in the Patient_Referral_Key group header). Is there any way to get round this problem?

 

[lbass]

If you use a SQL expression to only return the most recent date, you can then sort by it. Open the SQL expression editor and create {%lastcontact}:

(select max(AKA.`Last_Contact_Made_Date`) from Table AKA where AKA.`Patient_Referral_Key` = Table.`AKA.`Patient_Referral_Key`)

Replace "Table" with your table name and substitute the correct field names. Then in the record selection formula use:

{Table.Last_Contact_Made_Date} = {%lastcontact}

Now only the last contact will be returned. If you still need a group, then create the datediff formula like this:

datediff("m", {Last_Contact_Made_Date}, CurrentDate)

Insert a maximum on the result (This will be the same as the formula result itself, but it allows you to use the topN sort). Then go to report->topN/group sort and select "Maximum of {@datediff}". If you no longer need groups, {@datediff} should be available for sorting in report->sort records.

I would probably use days ("d") instead of months in the datediff formula, too, as datediff("m", Date(2004,07,31),Daet(2004,08,01)) would give you a 1 month difference.

-LB

 

[Madawc]

I've had similar problems. Sometimes I was able to get round it by a group within a group. Other times, I could do it using a subreport, which is horribly inefficient in a detail line, but worth doing if there is no huge shortage of time or resources.

Another option is a Stored Procedure, which you write in SQL and use to create your own 'table' which Crystal can access.

Madawc Williams (East Anglia)

 

[MadCatmk2]

This is going to sound like a bit of a stupid question but here goes. I have been looking for the SQL expression editor but could not find it. WHere abouts is it in crystal. I can view the SQL source but cannot alter it.

I have tried a query in the access database and used that in the report and this has worked fine. The problem with that is that the query must be in the database. This report will be run on many databases which are identical to the one i'm testing on but will not have the query in them.

If i can find the SQL designer that would be ideal.

Thanks for that post LBass.

 

[MadCatmk2]

Madawc:

The sub report idea unfortunately is not ideal because of the overheads in processing time etc.

When you suggest Stored Procedures (haven't used them as yet). Would i be able to set the database table locations in the report, which could be used for the numerous databases and then have the stored procedure held in the report file itself so that this will be run each time the report is accessed. If i couldn't do that, then would it mean that each database would have to have the stored procedure in it.

Thanks for the suggestions.

 

[bdreed35]

A Stored Procedure would live in the database, and you point to it in the report the same way you would point to a table.

You posted in your first post that you have CR9. I think we can do what you need using the SQL Command that was introduced in CR9. Can you open your report, go to Database, Show SQL Query, and post the SQL query? I think we can help you write the SQL you need and then show you how to use the SQL Command.

~Brian

 

[MadCatmk2]

SELECT `PATIENT_REFERRAL`.`DATE_OF_REFERRAL`, `PATIENT_REFERRAL`.`CASELOAD_HOLDER`, `PATIENT_REFERRAL`.`DISCHARGE_DATE`, `PATIENT_REFERRAL`.`PATIENT_NO`, `PATIENT_REFERRAL`.`PATIENT_REFERRAL_KEY`, `CONTACTS`.`DATE_OF_VISIT`
 FROM   ((`PATIENT_REFERRAL` `PATIENT_REFERRAL` INNER JOIN `CARE_PACKAGES` `CARE_PACKAGES` ON `PATIENT_REFERRAL`.`PATIENT_REFERRAL_KEY`=`CARE_PACKAGES`.`PATIENT_REFERRAL_KEY`) INNER JOIN `CARE_AIMS` `CARE_AIMS` ON `CARE_PACKAGES`.`CARE_PACKAGE_KEY`=`CARE_AIMS`.`CARE_PACKAGE_KEY`) INNER JOIN `CONTACTS` `CONTACTS` ON `CARE_AIMS`.`KEY`=`CONTACTS`.`CARE_AIM_KEY`
 WHERE  `PATIENT_REFERRAL`.`CASELOAD_HOLDER`=10718 AND `PATIENT_REFERRAL`.`DISCHARGE_DATE` IS  NULL 
 ORDER BY `PATIENT_REFERRAL`.`PATIENT_REFERRAL_KEY`, `CONTACTS`.`DATE_OF_VISIT` DESC

Thats the query as it stands at present.

I have the one group at present which has:
[Patient_No], [Patient_Referral_Key], [Discharge_Date], [Date_Of_Visit], and @Date_Diff formula

all in the header of the formula. I'd like to order on @date_diff.

Let me know if you need any more info.

I'm trying to get to grips with the SQL expression builder as i have found that now.

Thanks Brian

 

[MadCatmk2]

Hi people

I have it sorted. I found a reply to a post by Lbass:

thread767-869569

The method described using the Top N group sort seemed to work for me.

Thanks for all your help, i've learnt a fair bit with this one.

Cheers