Personal tools
You are here: Home / OSCAR EMR version 15 / 3.0 Administration / Diabetes ACR Indicator Template

Diabetes ACR Indicator Template

a working indicator template for the local Indicator Dashboard

Extensible Markup Language (XML) icon ACR.xml — Extensible Markup Language (XML), 5 kB (5841 bytes)

File contents

<?xml version="1.0" encoding="UTF-8"?>
<indicatorTemplateXML>
	<author>Peter Hutten-Czapski 2017</author>
	<uid></uid>
	<heading>
		<category>CDM</category>
		<subCategory>Diabetes</subCategory>
		<name>Diabetes with ACR in range</name>
		<definition>% of patients with diabetes aged 40 years and older who have had ACR tests outside of range within the past 24 months</definition>
		<framework>PHC</framework>
		<frameworkVersion>1</frameworkVersion>
		<notes>This is a test template for the Diabetes with ACR Testing Indicator query</notes>
	</heading>
	<indicatorQuery>
		<version>11-07-2017</version>
		<params>
			<parameter id="loinc" name="LOINC Code" value="'13705-9'" />
			<parameter id="provider" name="Provider Number" value="loggedInProvider" />
			<parameter id="active" name="Active Patients" value="'%AC%'" />
			<parameter id="dxCodes" name="Dx Codes" value="250,E10,E11,DB-610" />
		</params>
		<range>
			<lowerLimit id="date" label="From Date" name="Date" value="DATE_SUB( NOW(), INTERVAL 24 MONTH )" />
			<upperLimit id="date" label="Date Today" name="Date" value="NOW()" />
			
			<upperLimit id="acr" label="Max ACR Result" name="ACR Tests" value="2.8" />
			<lowerLimit id="acr" label="Min ACR Result" name="ACR Tests" value="0.0" />
		</range>
		<query>
			SELECT

				
				IF ( COUNT(fin.patient) &gt; 0,
				SUM( CASE WHEN fin.acr BETWEEN ${lowerLimit.acr} AND ${upperLimit.acr} THEN 1 ELSE 0 END ) 
				, 0) AS "0% &lt; ACR &lt;= 2.8%",
				
				IF ( COUNT(fin.patient) &gt; 0,
				SUM( CASE WHEN fin.acr &gt; ${upperLimit.acr} THEN 1 ELSE 0 END )
				, 0) AS "ACR > 2.8%"
				
			FROM ( 
				SELECT 
					d.demographic_no AS patient, 
					ACR.dataField AS acr
				FROM demographic d 
				INNER JOIN dxresearch dxr 
				ON ( d.demographic_no = dxr.demographic_no) 
			
				-- ACR measurement
				LEFT JOIN ( 
					SELECT m.dataField, m.demographicNo 
					FROM measurements m
					JOIN (
						SELECT "ACR" AS 'name' 
					) mm
					ON (mm.`name` = m.type)
					WHERE DATE(m.dateObserved) > ${lowerLimit.date}
					AND m.demographicNo > 0
					GROUP BY m.demographicNo HAVING COUNT(m.demographicNo) > -1
				) ACR 
				ON (d.demographic_no = ACR.demographicNo) 
			
				WHERE d.patient_status LIKE ${active} 
				AND dxr.dxresearch_code IN ${dxCodes}
				AND dxr.`status` NOT LIKE "%D%" 
				AND d.demographic_no > 0
				AND d.provider_no LIKE ${provider} 
			) fin;
		</query>
	</indicatorQuery>
	<drillDownQuery>
		<version>09-11-2017</version>
		<params>
			<parameter id="loinc" name="LOINC Code" value="'13705-9'" />
			<parameter id="provider" name="Provider Number" value="loggedInProvider" />
			<parameter id="active" name="Active Patients" value="'%AC%'" />
			<parameter id="dxCodes" name="Dx Codes" value="250,E10,E11,DB-610" />
		</params>
		
		<range>
			<lowerLimit id="date" label="From Date" name="Date" value="DATE_SUB( NOW(), INTERVAL 24 MONTH )" />
			<upperLimit id="date" label="Date Today" name="Date" value="NOW()" />
		</range>
		
		<displayColumns>
			<column id="demographic" name="d.demographic_no" title="Patient Id" primary="true" />
			<column id="name" name="CONCAT( d.last_name, ', ', d.first_name )" title="Patient Name" primary="false" />
			<column id="dob" name="DATE_FORMAT( CONCAT(d.year_of_birth,'-',d.month_of_birth,'-',d.date_of_birth), '%m-%d-%Y' )" title="Date of Birth (mm-dd-yy)" primary="false" />
			<column id="age" name="FLOOR( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,'-',d.month_of_birth,'-',d.date_of_birth) ), NOW() ) ) / 365.25 )" title="Age" primary="false" />
			<column id="a1c" name="IFNULL( ACR.dataField, 0)" title="ACR" primary="false" />
			<column id="a1cDate" name="IFNULL( DATE_FORMAT( ACR.dateObserved, '%m-%d-%Y' ), 0)" title="Test Date (mm-dd-yyyy)" primary="false" />
			<column id="codeSystem" name="CONCAT( dxr.coding_system, ' ', dxr.dxresearch_code )" title="Dx System/Code" primary="false" />
		</displayColumns>
		
		<exportColumns>
			<column id="demographic" name="d.demographic_no" title="Patient Id" primary="true" />
			<column id="name" name="CONCAT( d.last_name, ', ', d.first_name )" title="Patient Name" primary="false" />
			<column id="dob" name="DATE_FORMAT( CONCAT(d.year_of_birth,'-',d.month_of_birth,'-',d.date_of_birth), '%m-%d-%Y' )" title="Date of Birth (mm-dd-yy)" primary="false" />
			<column id="age" name="FLOOR( ABS( DATEDIFF( DATE( CONCAT(d.year_of_birth,'-',d.month_of_birth,'-',d.date_of_birth) ), NOW() ) ) / 365.25 )" title="Age" primary="false" />
			<column id="a1c" name="IFNULL( ACR.dataField, 0)" title="ACR" primary="false" />
			<column id="a1cDate" name="IFNULL( DATE_FORMAT( ACR.dateObserved, '%m-%d-%Y' ), 0)" title="Test Date (mm-dd-yyyy)" primary="false" />
			<column id="codeSystem" name="CONCAT( dxr.coding_system, '/', dxr.dxresearch_code )" title="Dx System/Code" primary="false" />
		</exportColumns>
		<query>	
			SELECT *	
			FROM demographic d 
			INNER JOIN dxresearch dxr 
			ON ( d.demographic_no = dxr.demographic_no) 
			
			LEFT JOIN ( 

				SELECT m1.dataField, m1.demographicNo, m1.dateObserved
				FROM measurements m1
				INNER JOIN (
					SELECT MAX(id) AS id
					FROM measurements m
						JOIN (
							SELECT "ACR" AS 'name' 
						) mm
					ON (mm.`name` = m.type)
					WHERE DATE(m.dateObserved) > ${lowerLimit.date}
					AND m.demographicNo > 0 
					GROUP BY m.demographicNo
				) m2
				ON (m1.id = m2.id)

				
			) ACR 
			ON (d.demographic_no = ACR.demographicNo) 

			
			WHERE d.patient_status LIKE ${active} 
			AND dxr.dxresearch_code IN ${dxCodes}
			AND dxr.`status` NOT LIKE "%D%" 
			AND d.demographic_no > 0 
			AND d.provider_no LIKE ${provider}
		</query>
	</drillDownQuery>
	<shared>false</shared>
	<sharedMetricSetName></sharedMetricSetName>
	<sharedMetricDataId></sharedMetricDataId>
	<sharedMappings>
	</sharedMappings>
</indicatorTemplateXML>

Document Actions