DR Report - Crystal Reports-Meditech Magic | Forum

Topic location: Forum home » Reporting » DR Reporting
Mar 11 '15
2015-03-11 11:25:50
I am trying to pull the diagnosis codes 1,2,3,4,5,...... into separate columns on a Crystal Report. I am only able to get the first dx code and not the rest.
My formula is IF{BarDiagnoses.DiagnosisSeqID}=1 THEN {BarDiagnoses.DiagnosisCodeID}
Has anyone been successful in pulling the sequential diagnosis codes?
Joy C. Merritts
Assistant IT Director
J.C. Blair Memorial Hospital
1225 Warm Springs Avenue
Huntingdon, PA 16652
(814) 643-8557
(814) 643-8771 (fax)
jmerritts@jcblair.org<mailto:jmerritts@jcblair.org>
[Employee Signature PH Affiliate]

Important Notice
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
Mar 11 '15
2015-03-11 12:48:53
Hi Joy,
There are a couple of different techniques to do this using T-SQL (assuming you are "feeding" Crystal this way). Typically when we report patient diagnoses, we would use AbsDrgDiagnoses, but the technique will be the same from BarDiagnoses.
If you need each diagnosis in its own column (FirstDx, SecondDx, ThirdDx, etc.) the simplest method is to JOIN multiple times on your diagnoses table, once for each DiagnosisSeqID. This method works well if you have a fixed number of diagnoses to display (e.g. the first 3, first 5, etc.) It looks something like this, presuming you start from AbstractData:
SELECT
ABD.AccountNumber,
ADX1.Diagnosis AS PrimaryDiagnosis,
IsNull(ADX2.Diagnosis, 'N/A') AS SecondaryDiagnosis,
IsNull(ADX3.Diagnosis, 'N/A') AS TertiaryDiagnosis
FROM livedb.dbo.AbstractData ABD
INNER JOIN livedb.dbo.AbsDrgDiagnoses ADX1
ON ABD.SourceID = ADX1.SourceID
AND ABD.AbstractID = ADX1.AbstractID
AND ADX1.DiagnosisSeqID = 1
LEFT JOIN livedb.dbo.AbsDrgDiagnoses ADX2
ON ABD.SourceID = ADX2.SourceID
AND ABD.AbstractID = ADX2.AbstractID
AND ADX2.DiagnosisSeqID = 2
LEFT JOIN lived.dbo.AbsDrgDiagnoses ADX3
ON ABD.SourceID = ADX3.SourceID
AND ABD.AbstractID = ADX3.AbstractID
AND ADX3.DiagnosisSeqID = 3
Note how the first join (DiagnosisSeqID = 1) is INNER, since we always expect at least one diagnosis. The remaining joins are LEFT, since we may or may not have more than one, but always want to keep the rest of the patient record.
The second method is useful if you need all the diagnoses, regardless of how many there are. You could use the code above and have a LEFT join for the highest possible number of diagnoses - performance will not suffer. However another technique is to use a function that dynamically finds the number of diagnoses per patient record and simply strings them together in a single column, separating each item with a comma (or character of your choice.) The end result of that might look like this:
Diagnoses
414.01, 411.1, 401.1

The code for this is too long to include here, but if you think this is better suited to your needs, let me know and I'll email it to you offline.
Ian Proffer
Vice President, Consulting Services
781.329.4300 x202
[Acmeware_logo_email_signature]<http://www.acmeware.com/>;
Data Repository Experts Since 1998

Mar 11 '15
2015-03-11 13:04:55
Hi Joy,
There are a lot more elegant solutions than the one I created but when I created a similar view this little 'project to do in my spare time' was only one of about 15 other irons I had in the fire. [I know I am such a whiner-only 15 irons in the fire!]
I created a little view for each DxCode and DxCodeDescriptions I needed. So I created little views for Dx1 through Dx5.
Then I added these little views to the one major View I created using some BAR table. This allowed me to arrange the five Dx codes and their Descriptions in a horizontal row for each patient.
Yes, I am embarrassed to publically admit I didn't take the time to figure out how to use the PIVOT Table or Coalesce or whatever function I should have used. BUT the job was done, the users requesting the information love the spreadsheet I created by connecting to the view... and I moved on to another project. :)
Someone, please tell Joy the correct way to create this so she won't have to suffer the shame and public ignominy.
Doug
Doug Hiteshew, B.A., MT(ASCP)
Senior Systems Analyst
Johnson Memorial Hospital
Franklin, IN 46131
Voice: 317-736-3397
Fax: 317-346-3011
E-mail: dhiteshew@johnsonmemorial.org<mailto:dhiteshew@johnsonmemorial.org>
** Call me Joy if you want know to the down and dirty method. :)
Maxxx
Maxxx Jun 11
инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинйоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфоинфо инфоинфоинфоинфоtuchkasинфоинфо