Notifications
Clear all
Topic starter
Hi All,
I thought we used to do crystal question here too, hopefully that is still the case.
We are undertaking an initiative to clean up our old and unused forms/encounter types/document templates but I am having trouble reporting on the frequency of use. My hope is that I can tie the document SDID (and count on it) back to the document template that makes up said encounter but I can't seem to connect the two together, has anyone ever reported like this before?
My roadblock right now is connecting the specific encounter type back to the actual documents in the EMR. As an example we have about 30 different office visit type encounters so I cannot just report back to the document type and go from there (how will the system know which encounter type was opened if the link is just office visit to office visit?).
Any help would be much appreciated.
Thanks
Mike
Posted : May 13, 2015 3:54 am
Below are SQL scripts you can use with your Crystal Reports or Create Views in SQL to link to your crystal.
This script is to get frequency for Document Types
SELECT *
FROM (
SELECT DESCRIPTION, ABBR, COUNT(ABBR) AS FREQUENCY
FROM (
SELECT DTID, DESCRIPTION, ABBR
FROM dbo.[DOCUMENT] INNER JOIN
dbo.DOCTYPES ON dbo.[DOCUMENT].DOCTYPE = dbo.DOCTYPES.DTID) AS GETCOUNTDATA
GROUP BY DTID, ABBR, DESCRIPTION) AS POSTDATA
ORDER BY FREQUENCY DESC
And this is probably the closest you are going to get for the frequency of Encounter Type
SELECT *
FROM (
SELECT DESCRIPTION, COUNT(DESCRIPTION) AS FREQUENCY
FROM (
SELECT NAME AS DESCRIPTION
FROM dbo.[DOCUMENT] INNER JOIN
dbo.ENCTYPE ON dbo.[DOCUMENT].DOCTYPE = dbo.ENCTYPE.DOCTYPEID) AS GETCOUNTDATA
GROUP BY DESCRIPTION) AS POSTDATA
ORDER BY FREQUENCY DESC
Posted : May 13, 2015 5:44 am
1. Create query to count number of encounter types for all documents:
SELECT dbo_ENCTYPE.NAME, dbo_ENCTYPE.DOCTEMPID, Count(dbo_ENCTYPE.NAME) AS CountOfNAME
FROM dbo_ENCTYPE INNER JOIN dbo_DOCUMENT ON dbo_ENCTYPE.DOCTYPEID = dbo_DOCUMENT.DOCTYPE
GROUP BY dbo_ENCTYPE.NAME, dbo_ENCTYPE.DOCTEMPID;
2. link results to DocTemplate by DOCTEMPID, include Text field:
SELECT [Forms per Encounter Type Num Docs].DOCTEMPID, [Forms per Encounter Type Num Docs].dbo_ENCTYPE.NAME, dbo_DOCTEMPLATE.TEXT, [Forms per Encounter Type Num Docs].CountOfNAME
FROM [Forms per Encounter Type Num Docs] INNER JOIN dbo_DOCTEMPLATE ON [Forms per Encounter Type Num Docs].DOCTEMPID = dbo_DOCTEMPLATE.DTID;
3. The TEXT field is in RTF,e.g.: {\rtf1\ansi\ftnbj{\fonttbl{\f0 \fswiss Arial;}}{\colortbl ;\red255\green255\blue255 ;\red0\green0\blue0 ;}{\stylesheet{\f0\fs20\cf2\cb1 Normal;}{\cs1\additive\cf2\cb1 Default Paragraph Font;}}\margl1440\margr1440\margt540\margb1440\headery540\footery720\formshade\sectd\marglsxn1440\margrsxn1440\margtsxn540\margbsxn1440\headery540\footery720\sbkpage\pgncont\plain\plain\fs20\pard\plain\fs20\cf0 [MLI_FORM:1638193065956490]\par [MLI_FORM:1709480347556490]\par}
4. the MLI_Form:xxxxxxxxxx identifies the form, linked via FACTORID in the FORM table. The problem is to parse the TEXT. Make a table of all the MLI_FORM:xxxxxxxxxxxxx. Link to the FORM table to list the form names. I can envision how to do this in Crystal, beyond my level of expertise in SQL. Add up the occurrences of a given form and you have the form frequencies.
I would be very interested if anyone has suggestions to solve #4.
Posted : May 14, 2015 9:58 am
Topic starter
Thank you both for the insight, I will take a crack at this and post the report/what I did if I find success.
Thanks
Mike
Posted : May 15, 2015 7:56 am
I have a Crystal Report which extracts forms per encounter. Error in my initial response, the MLI_Form:xxxxx is linked to the FSID in table FORMSET. My next step is to link back to document frequency and restrict to given period of interest. This should be straightforward. If you have Crystal Report, happy to share my work. [email protected]
Posted : May 15, 2015 8:26 am
What about counting the times an individual form is used? Any report/query suggestions?
Thanks!
Posted : April 14, 2016 3:33 am
That, I do not think it is possible, because when you sign a document, that part that states what forms are being used get removed from the database.
Posted : April 14, 2016 5:10 am
I have a different approach to offer that may or may not help, it is tied to documents, you can group by user, and it shows you the form name. It does not show the FSID or the form path, but it still holds form data after the document is signed.
select * from DOCCONTB where CONTB_ACTION = 25
Posted : April 14, 2016 6:10 am
