Notifications
Clear all
Topic starter
I have a column(DOS) which is based on a formula. It pulls the latest office visit date of a patient. It is placed in the Group Footer section. I have many other columns which are also based on formulas. I need help to sort the DOS column. Can anyone give me suggestions on how to do it. Thank you in advance.
BAJ
Posted : September 12, 2012 11:10 pm
If you have the DOS in the Group Footer, it will be sorted by the field designated for the Group Footer. If you want to sort by the DOS, you'll have to change the Group Footer item to the DOS, or create another group.
Posted : September 12, 2012 11:49 pm
Topic starter
Hi JJordet
Thank you very much for your reply.
I have done this report in a way suggested in the class manual on how to do a report on Multiple Observations. The current group I have is PERSON.SEARCHNAME. So do you mean I have to create another group on my formula(@LastOfficeVisit) which calculates the DOS. But I am not able to see the formula in the Available Fields list when I try to do that. Can you pls help me.
Posted : September 13, 2012 12:04 am
If I understand, you have:
Group Header...SEARCHNAME
Detail
Group Footer...SEARCHNAME...@LastOfficeVisit
@LastOfficeVisit will show the last value calculated. You need to add @LastOfficeVisit to the Sort list as ascending in the Record Sort Wizard.
I do this all the time with the ClinicalDate.
Posted : September 13, 2012 12:34 am
Topic starter
Hi JJordet,
Thank you for your reply. I really appreciate your help. I have to do this by tomorrow.
I am not seeing @LastOfficeVisit in the sorting fields list. I forgot to tell you that @LastOfficeVisit is being pulled at runtime based on two parameters.
?StartDateFrom (for eg. 2012-8-1)
?StartDateTo (for eg. 2012-8-31)
LastOfficeVisit pulls the last office visit date the patient had within the above date range. I am assuming that's why I cannot use it as a sort field.
The report needs to pull the first 100 patients who had their office visits between this date range sorted in descending order, as given below:
DOS Patient Sex Age LatestHgbDate LatestBPDate LatestBP
1. 8/31/2012 Pt_GGG ........................................
2. 8/31/20112 Pt_AAA
3. 8/30/2012 Pt_XXX
....
100. 8/1/2012 Pt_YYY
I hope you are getting what I am talking about.
Is there any other way to do this? (arrays, subreports). will it be possible to take this column into an array and then sort it (since it is in the groupfooter section). If so, how can this be done?
Thank you again.
BAJ
Posted : September 13, 2012 1:04 am
I wouldn't think that the formula using parameters would matter. I just checked a report that uses parameters...you're right...won't show up in either sort or group list.
Posted : September 13, 2012 1:17 am
Topic starter
Hi JJordet,
Thank you very much for all the trouble you are taking to help me.
So how do you think this can be done? Do you think I can do this with arrays? It seems complicated (to me). Since, it is in the Group Footer of PERSON.SEARCHNAME, can we try to sort it on DOS. will it work?
I would like to know your suggestions? Any other ideas?
Posted : September 13, 2012 1:30 am
What's your @LastOfficeVisit formula look like?
Posted : September 13, 2012 1:42 am
Topic starter
Hi JJ,
WhilePrintingRecords;
dateVar last_off_visit_date:=cdate("1901,01,01");
stringVar physician:="";
if {@ConvertDate}>last_off_visit_date
then
(last_off_visit_date:={@ConvertDate};
physician:=TOTEXT({DOCUMENT.USRID});
);
last_off_visit_date;
BAJ
Posted : September 13, 2012 2:06 am
Topic starter
Also,
@ConvertDate
NumberVar ID := {DOCUMENT.CLINICALDATE};
NumberVar Sec := Truncate(ID / 1000000);
NumberVar Days := Truncate(Sec / 3600 / 24);
Date(DateTime(Date (1960,01,01 ) + Days, Time(0,0,0) + Sec));
BAJ
Posted : September 13, 2012 2:07 am
Topic starter
Hi JJ,
Also selection formula,
{DOCUMENT.DOCTYPE} = 1.00
Thank you.
BAJ
Posted : September 13, 2012 2:24 am
Sorry...no more ideas.
Posted : September 13, 2012 2:26 am
Topic starter
Thank you, JJ for all your effort and help. I really appeciate it.
Anyone else having any suggestions?
BAJ
Posted : September 13, 2012 2:30 am
Just thought of something...create a view that returns documents filtered by the maximum clinicaldate for each patient...then use this view instead of the DOCUMENT table...this would cut out the need for the lastofficevisit formula.
SELECT a.*
FROM ml.DOCUMENT AS a
WHERE a.CLINICALDATE = SELECT MAX(b.CLINICALDATE)
FROM ml.DOCUMENT AS b
WHERE b.PID = a.PID
I'm not a SQL guru, but I think this is close.
Posted : September 13, 2012 6:35 am
Topic starter
Thankyou JJ, I will look into it. The problem is, I would need to link USRINFO, LOCREG LASTLABS, PROBLEM tables. I need the physicians name who saw the patient at the last office visit, the location, Dx code, latest lab values, etc. The report has 24 columns pulling values from the database. Also many evaluated columns showing boolean values. I don't write SQL as such, but I will try.
But, I am sure you will be able to help me with this. Will you be able to give the SQL to get the latest values from the LASTLABS table.
Thank you once again.
BAJ
Posted : September 13, 2012 10:59 pm
Page 1 / 2
Next
