Welcome to the NEW CHUG— your ultimate destination for cutting-edge insights and discussions at the intersection of healthcare and technology.

Our platform is dedicated to exploring the dynamic landscape of healthcare technology, empowering professionals, enthusiasts, and innovators alike with the knowledge and resources to navigate the rapidly evolving field.

From telemedicine revolutionizing patient care to the transformative potential of artificial intelligence in diagnostics, we delve into a myriad of topics shaping the future of healthcare delivery. Whether you’re a seasoned healthcare IT professional, an aspiring entrepreneur in the digital health space, or simply curious about the latest innovations, our curated content, expert interviews, and thought-provoking articles provide a comprehensive exploration of healthcare technology’s most pressing issues.

Join us as we navigate the forefront of innovation, driving forward the transformation of healthcare for a healthier, more connected world.

Have questions or suggestions? Please use the contact button below.

Notifications

Clear all

Sorting on a column based on a formula.

Page 1 / 2 Next

Trading Post

Last Post by Anonymous 1 second ago

25 Posts

2 Users

0 Reactions

193 Views

RSS

baj

(@baj)

Posts: 99

Estimable Member

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

jjordet

(@jjordet)

Posts: 545

Honorable Member

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

baj

(@baj)

Posts: 99

Estimable Member

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

jjordet

(@jjordet)

Posts: 545

Honorable Member

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

baj

(@baj)

Posts: 99

Estimable Member

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

jjordet

(@jjordet)

Posts: 545

Honorable Member

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

baj

(@baj)

Posts: 99

Estimable Member

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

jjordet

(@jjordet)

Posts: 545

Honorable Member

What's your @LastOfficeVisit formula look like?

Posted : September 13, 2012 1:42 am

baj

(@baj)

Posts: 99

Estimable Member

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

baj

(@baj)

Posts: 99

Estimable Member

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

baj

(@baj)

Posts: 99

Estimable Member

Topic starter

Hi JJ,

Also selection formula,

{DOCUMENT.DOCTYPE} = 1.00

Thank you.

BAJ

Posted : September 13, 2012 2:24 am

jjordet

(@jjordet)

Posts: 545

Honorable Member

Sorry...no more ideas.

Posted : September 13, 2012 2:26 am

baj

(@baj)

Posts: 99

Estimable Member

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

jjordet

(@jjordet)

Posts: 545

Honorable Member

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

baj

(@baj)

Posts: 99

Estimable Member

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

24 Forums
9,940 Topics
39.9 K Posts
0 Online
6,470 Members

Forum Icons: Forum contains no unread posts Forum contains unread posts

Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed