Notifications
Clear all
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
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
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
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
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
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
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
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
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
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
