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

Patient BP - Crystal Report

Trading Post

Last Post by Anonymous 1 second ago

10 Posts

4 Users

0 Reactions

83 Views

RSS

carrie.walls

(@carrie-walls)

Posts: 4

Active Member

Topic starter

I'm looking for help in writing a CR to return a patient's latest BP. In the EMR database there is the OBS table from which I need to pull OBS.HDID = 54 (Systolic BP) for the MAX(OBS.OBSDATE) and by PID.

For example:

Obs ID	OBS Value	OBS Date	Patient ID (in the person table)
54	130	9/4/2013	1234
54	128	7/3/2013	1234
54	150	10/1/2013	1234

For this data, I would want to return the OBSValue of 150, for patient ID 1234 and OBsID = 54.

I know the SQL would be:

Select

o1.ObsID,

o1.obsvalue,

o1.obsdate,

p.patientid

from obs o1

left join person p

on o1.pid = p.pid

where o1.obsdate = (select max(obsdate)from obs o2 where

o1.pid=o2.pid) and p.patientid = ‘1234’ and o1.hdid ='54'

but I don’t know how to grab it in a formula in CR.

Does anyone have any ideas or a CR already developed that returns a OBS term value which corresponds to the MAX(OBSDATE)?

Posted : September 9, 2014 5:10 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

Does anyone on your staff have experience writing stored procedures? I write all the custom reports for our organization, and I build an SP for almost every one of them. It lets SQL Server do all that heavy lifting and all CR needs to do is display the results.

Another option is to use a SQL Expression Field in CR. You should see that option in your Field Explorer pane. Create a new one, I'm calling it "MAXobs" in this example, and set the query like so:

(
SELECT MAX(OBSDATE)
FROM OBS
WHERE PID = "PERSON"."PID"
AND HDID = 54
AND XID = 1000000000000000000
)

Your record selection formula would look something like this:

{OBS.HDID} = 54
AND
{OBS.XID} = 1000000000000000000
AND
{OBS.OBSDATE} = {%MAXobs}

...plus whatever patient filter you're putting in there. That should get you on the right track, I think. You can make that expression accommodate any obs term by making the HDID a parameter, and using the parameter in the SQL Expression rather than hard-coding it.

Posted : September 9, 2014 5:48 am

carrie.walls

(@carrie-walls)

Posts: 4

Active Member

Topic starter

Ron,

Thanks for the assistance! I tried to enter he SQL expression as you stated but keep getting the error, "Error in compiling SQL Expression: Failed to retrieve data from the database." I have the applicable tables pulled into CR (the obs and person tables) but our DB is Oracle. Would the Oracle v. SQL database keep it from compiling?

I wish I knew more about stored procedures - it might make my job much easier.

Thanks again,

Carrie

Posted : September 17, 2014 4:41 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

It's entirely possible there's a SQL/Oracle difference, but I don't know enough about Oracle to say for sure. There should be some kind of details available for the error, no?

Posted : September 17, 2014 4:54 am

carrie.walls

(@carrie-walls)

Posts: 4

Active Member

Topic starter

It says, "Table or view does not exist" but I can see that I have the obs and person tables pulled in to the CR instance I'm working with. I'm puzzled.

Posted : September 17, 2014 4:57 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

Try either removing the quotes from PERSON.PID, or adding them to OBS. It's a shot in the dark, I'm just plucking stuff from Google.

Posted : September 17, 2014 4:58 am

baj

(@baj)

Posts: 99

Estimable Member

Tables/Views

PERSON

LASTLABS (view)

RPTOBS(view)

Links

PATIENT.PID-->LASTLABS.PID

LASTLABS.PID-->RPTOBS.PID

LASTLABS.HDID-->RPTOBS.HDID

LASTLABS.MAXLABDATE-->RPTOBS.OBSDATE

Record Selection

{RPTOBS.HDID}=54

and {RPTOBS.CHANGE}=2

Hope this helps.

BAJ

Posted : September 17, 2014 5:41 am

carrie.walls

(@carrie-walls)

Posts: 4

Active Member

Topic starter

Ahhh! Success! The log-in credentials I was using for the DB had limited permissions. I changed to another log-in and "Boom" - it worked. Thanks!

Posted : September 17, 2014 6:18 am

bruins1986

(@bruins1986)

Posts: 233

Reputable Member

It looks like this is solved but in Crystal syntax something like:

if RPTOBS.HDID= 54 and RPTOBS.Value = Maximum(RPTOBS.Value, PERSON.Pid) then RPTOBS.Value

You will need to have a group on the PERSON.Pid for that to work though.

should work.

When I'm just looking for maximum (or minimum) values I like to use something like the above in my group selection formula - I find it easier then messing with the detail section/formulas.

Posted : September 19, 2014 2:48 am

ronmoses

(@ronmoses)

Posts: 158

Estimable Member

The main reason I suggested trying to pull some kind of MAX() out of SQL is, if you do it via report grouping, Crystal is going to pull all the records for that patient, which could be inefficient in terms of run time and network traffic. But that's just a general practice - in this particular case, it may not make a lot of difference.

Posted : September 19, 2014 2:57 am

24 Forums
9,940 Topics
39.9 K Posts
1 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