/usr/share/doc/gnumed/user-manual/Gnumed/GmManualReportGenerator.html is in gnumed-doc 1.4.6+dfsg-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en_US" lang="en_US">
<head>
<title> GmManualReportGenerator < Gnumed < Foswiki</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <meta name="robots" content="noindex" /> <link rel="alternate" type="application/rss+xml" title="RSS Feed" href="WebRss.html" />
<link rel="icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" /> <link rel="shortcut icon" href="../rsrc/System/ProjectLogos/favicon.ico" type="image/x-icon" />
<link rel="alternate" href="http://wiki.gnumed.de/bin/edit/Gnumed/GmManualReportGenerator?t=1391005513" type="application/x-wiki" title="edit GmManualReportGenerator" />
<meta name="description" content="GmManualReportGenerator" />
<!--[if IE]></base><![endif]-->
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/base.css');
</style>
<style type="text/css" media="all">
@import url('../rsrc/System/SkinTemplates/default.css');
</style>
<!--[if IE]><style type="text/css" media="screen">
pre {
overflow-x:auto;
padding-bottom:expression(this.scrollWidth > this.offsetWidth ? 16 : 0);
}
</style>
<![endif]-->
<meta name="foswiki.PUBURL" content="http://wiki.gnumed.de/pub" /> <!-- PUBURL -->
<meta name="foswiki.PUBURLPATH" content="/pub" /> <!-- PUBURLPATH -->
<meta name="foswiki.SCRIPTSUFFIX" content="" /> <!-- SCRIPTSUFFIX -->
<meta name="foswiki.SCRIPTURL" content="http://wiki.gnumed.de/bin" /> <!-- SCRIPTURL -->
<meta name="foswiki.SCRIPTURLPATH" content="/bin" /> <!-- SCRIPTURLPATH -->
<meta name="foswiki.SERVERTIME" content="29%20Jan%202014%20-%2015:25" /> <!-- SERVERTIME -->
<meta name="foswiki.SKIN" content="twikinet%2c%20pattern" /> <!-- SKIN -->
<meta name="foswiki.SYSTEMWEB" content="System" /> <!-- SYSTEMWEB -->
<meta name="foswiki.TOPIC" content="GmManualReportGenerator" /> <!-- TOPIC -->
<meta name="foswiki.USERNAME" content="KarstenHilbert" /> <!-- USERNAME -->
<meta name="foswiki.USERSWEB" content="Main" /> <!-- USERSWEB -->
<meta name="foswiki.WEB" content="Gnumed" /> <!-- WEB -->
<meta name="foswiki.WIKINAME" content="KarstenHilbert" /> <!-- WIKINAME -->
<meta name="foswiki.WIKIUSERNAME" content="Main.KarstenHilbert" /> <!-- WIKIUSERNAME -->
<meta name="foswiki.NAMEFILTER" content="%5b%5cs%5c*%3f~%5e%5c%24%40%25%60%22'%26%3b%7c%3c%3e%5c%5b%5c%5d%23%5cx00-%5cx1f%5d" /> <!-- NAMEFILTER --><!--JQUERYPLUGIN::FOSWIKI::META-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/jquery-1.4.3.js'></script><!--JQUERYPLUGIN-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/livequery/jquery.livequery.js'></script><!--JQUERYPLUGIN::LIVEQUERY-->
<script type='text/javascript' src='../rsrc/System/JQueryPlugin/plugins/foswiki/jquery.foswiki.js'></script><!--JQUERYPLUGIN::FOSWIKI-->
<script type='text/javascript' src='../rsrc/System/JSTreeContrib/jquery.jstree.js'></script><!--JQUERYPLUGIN::JSTREE-->
</head>
<body class=""><div class="foswikiPage">
<a name="PageTop"></a>
<p></p>
<p></p>
<h1><a name="The_GNUmed_Report_Generator"></a> The GNUmed Report Generator </h1>
<p></p>
GNUmed offers two fundamentally different ways to search the database:
<p></p> <ol>
<li> across the EMR of the currently active patient
</li> <li> across the entire medical database regardless of the active patient
</li></ol>
<p></p>
The second approach is sometimes called <em>data mining</em>. GNUmed has a plugin called <em>Reports</em> to enable you to create database-wide reports.
<p></p>
<h2><a name="Scope"></a> Scope </h2>
<p></p>
The plugin is intended to generate simple reports. The powers of this tool do not go beyond what you can do from within PostgreSQL. However, one can enhance PostgreSQL with the <a href="http://www.joeconway.com/plr/" target="_top">"R" procedural language</a> (or, in fact, <a href="http://www.postgresql.org/docs/8.2/static/external-pl.html" target="_top">any other one</a>) in order to unleash considerable statistical powers right from within the SQL query.
<p></p>
For anything more sophisticated than that (say, post-processing the report results) one will have to turn to custom scripting, off-the-shelf report generators or data mining tools such as NetEpi.
<p></p>
<h2><a name="Usage"></a> Usage </h2>
<p></p>
<h3><a name="Generating_Reports"></a> Generating Reports </h3>
<p></p>
To generate a report from the database you need to run an SQL query. The query has to be typed into the <em>Command (SQL)</em> field of the <em>Reports</em> plugin. Then hit the button <em>[Run]</em>. The results will be shown in the list at the bottom. The columns of the list will correspond to the columns of the database table(s) you collect data from with the query. You may want to use the SQL <em>AS</em> <a href="http://www.postgresql.org/docs/8.2/static/queries-select-lists.html#QUERIES-COLUMN-LABELS" target="_top">column alias syntax</a> to re-express ("map") database column names AS friendlier display labels.
<p></p>
Here are a few things to know:
<p></p> <ul>
<li> you don't need to worry about leading or trailing whitespace/linefeeds
</li> <li> your query may span several lines
</li> <li> you don't need to end your query with a semicolon (";") - but you can
</li> <li> you can paste query text from elsewhere via the clipboard
</li> <li> you can drag a file onto the <em>Reports</em> plugin and GNUmed will interpret the file content as the query to run <ul>
<li> note that you need to drag the file onto an area of the plugin <strong>outside</strong> the actual query command field (this will be improved later)
</li></ul>
</li></ul>
<p></p> <ul>
<li> the plugin will artificially <strong>limit the results list to 1000 rows</strong> (one thousand) such as to somewhat safeguard against queries going berserk
</li></ul>
<p></p> <ul>
<li> the queries are run in a <strong>read-only</strong> connection with the credentials of the user who logged on with this client <ul>
<li> no writing to the database is possible, not even as a side-effect of a <code>select my_writing_func()</code> query
</li></ul>
</li></ul>
<p></p> <ul>
<li> you might want to make your query return a column named <code>pk_patient</code> <ul>
<li> this will enable you to double-click on any row in the results list which will activate the patient identified by the database ID found in <code>pk_patient</code>
</li> <li> if there is no such column nothing will happen (besides an error message being shown)
</li> <li> note that you can, of course, make a query return an arbitrary number in a column named <code>pk_patient</code> in which case the corresponding patient will, indeed, be activated upon double-clicking a row but there may not, in fact, be any other meaningful correlation of the patient with that row
</li></ul>
</li></ul>
<p></p> <ul>
<li> you can limit your query to the currently-active patient by including, in the WHERE clause, the placeholder $<ID_active_patient>$ with something like: <code><table_or_view_relatable_to.pk_identity> = $<ID_active_patient>$</code> or something like
</li></ul>
<p></p>
<pre>
EXISTS (
SELECT 1
FROM
table_or_view_containing_else_relatable_to_pk_patient
WHERE
column_containing_pk = $<ID_active_patient>$
)
;
</pre>
<p></p>
<h3><a name="Visualizing_reports"></a> Visualizing reports </h3>
<p></p>
Hitting the <em>[Visualize]</em> button will let you select a column from the report results list from each of the x- and y-axis. The data in those columns is extracted from the report and sent to <a href="http://www.gnuplot.info/" target="_top">gnuplot</a> for display.
<p></p>
<h3><a name="Reusing_report_definitions"></a> Reusing report definitions </h3>
<p></p>
The <em>Report</em> field acts as a phrasewheel offering names of reports that were previously saved in the database. You can either type part of a name our part of a query (such as a table name) and select a report definition from the appearing dropdown match list. The corresponding query will be loaded from the database.
<p></p>
If you press <em>[Save]</em> the report definition will be saved in the database. If the report name is already known in the database the existing report definition will be overwritten. If not a new report definition will be created.
<p></p>
Hitting <em>[Contribute]</em> will email the report definition (<strong>name and query - nothing else</strong>) to the mailing list of the GNUmed community for all to share. This will happen anonymously. If you want to receive credit for it you'll have to actively claim it on the mailing list.
<p></p>
Note that report <em>results</em> are only preserved as long as the client instance they were generated in stays open. They will, however, survive changing the active patient.
<p></p>
The <em>[Schema]</em> button will take you to the <a href="DatabaseSchema.html">GNUmed database schema documentation in our wiki</a> for your reference.
<p></p>
<h3><a name="Sample_queries"></a> Sample queries </h3>
<p></p>
How many believed-to-be-alive patients remain in the praxis database?
<pre>
select count(*) from dem.identity
where deleted is FALSE / TRUE
where deceased is NULL / NOT NULL
</pre>
<p></p>
List for me the (hopefully less than 1024) patients in this database:
<pre>
select lastnames, firstnames, title, pk_identity AS pk_patient
from dem.v_basic_person
where dem.v_basic_person.lastnames is NOT NULL
order by lastnames, firstnames
</pre>
<p></p>
List for me patients having a particular postal code
<pre>
select number, street, dem.v_basic_person.lastnames, dem.v_basic_person.preferred, dem.v_basic_person.firstnames, suburb, urb, postcode, pk_identity as pk_patient
from
dem.v_basic_person
inner join
dem.v_pat_addresses
using (pk_identity)
where
LOWER(dem.v_pat_addresses.postcode) = 'inputDesiredPostalCodeHereInLowerCase'
order by
street, number
</pre>
<p></p>
List for me the patients waitlisted (without a waiting_zone specied) for more than 14 days:
<pre>
select lastnames, firstnames, title, comment, waiting_time_formatted, pk_identity as pk_patient
from
clin.v_waiting_list
where
waiting_time < '14 days'
and waiting_zone is NULL
</pre>
<p></p>
A query that (new in gnumed_v9) can search for patients based on the diagnostic code
<pre>
select *
from
dem.v_basic_person
inner join
clin.v_coded_item_narrative
using (pk_identity)
where
code = ...
and coding_system = ...
and soap_cat = ...
;
</pre>
<p></p>
A query that would return the number of patients seen, based on encounters of particular type, during an interval of time. Notice that here, the selection is made based on when the encounters of interest started (note: to count each patient only once, one must use 'select distinct fk_patient' in place of 'select fk_patient'):
<p></p>
<pre>
select count(1)
from dem.identity d_i
where
d_i.pk in (
select fk_patient from clin.encounter
where
fk_type in (select pk from clin.encounter_type where description in ('list', 'of', 'interesting', 'types')
and
started between 'year-01-01' and 'year-12-31'
)
;
</pre>
<p></p>
<p></p>
A query that, upon removing or fixing ", ...", would return all patients whose encounter of types of interest ('seen in clinic' etc) which the current user has created or modified in the past 21 days:
<p></p>
<pre>
SELECT
to_char(c_e.modified_when,'yyyy.mm.dd hh:mm') modified,
d_n.lastnames || ', ' || d_n.firstnames person_encountered,
c_et.description encounter_type,
to_char(c_e.started,'yyyy.mm.dd hh:mm') started,
c_e.assessment_of_encounter aoe,
c_e.pk AS pk_encounter,
c_e.fk_patient pk_patient
FROM
clin.encounter c_e INNER JOIN dem.names d_n ON c_e.fk_patient = d_n.id_identity INNER JOIN clin.encounter_type c_et ON c_e.fk_type = c_et.pk
WHERE
c_e.modified_by = "current_user"()
AND
c_e.modified_when > now() - interval '21 days'
AND
c_e.fk_type IN (select pk from clin.encounter_type where description IN ('seen in clinic', 'seen elsewhere', 'not seen but called', ...))
ORDER BY
c_e.started DESC
;
</pre>
<p></p>
and a query that would return the list of encounter descriptions from which to be able to build the query above:
<p></p>
<pre>
select * from clin.encounter_type ORDER BY description ;
</pre>
<p></p>
<p></p>
A query that would help by providing more fields (and sample values) that could be altered and used to find a patient when the standard patient search field did not permit a patient to be found, perhaps including the communication channels (phone numbers). Such queries could be
<p></p>
<pre>
select *
from
dem.v_basic_person
inner join
dem.v_person_comms / dem.v_person_jobs / dem.v_external_ids4identity
using (pk_identity)
where
dem.v_person_comms.url = ... /
dem.v_person_jobs.l10n_occupation = ... /
dem.v_external_ids4identity.value = ...
;
</pre>
<p></p>
A query that would fetch, from the inbox audit table, the messages deleted within the past 7 days, ordered by recency of last-modified
<p></p>
<pre>
SELECT *
FROM audit.log_message_inbox
WHERE
fk_staff = <staff ID of provider>
AND
audit_action = 'DELETE'
AND
audit_when > (now() - '7 days'::interval)
ORDER BY
<audit_when / orig_when / modified_when> DESC
;
</pre>
<p></p>
A query that could identify auto-created persons as might result from a data importer
<p></p>
<pre>
SELECT * from dem.clin_ext_id_type, dem.identity where
dem.clin_ext_id_type.name = "lab autoimport fake person" WHERE
dem.clin_ext_id_type.fk_person = dem.identity.pk
;
</pre>
<p></p>
There has been discussion offlist between Karsten and Jim "on theory of primary care" modeling levels of clinician diagnostic certainly. Once this would be captured in the encounters it would make for interesting queries to the effect of
<p></p>
<pre>
The patients I would most worry about would be those who
- remain our responsibility (they did not abandon us)
--> last seen in the most recent 6 (?) months
- and have an active issue or episode of certainty of A or B or C that is
--> persisting over multiple encounters
>= 2 encounters if symptom(s) are "alarming" or "worsening"
>= 3 encounters if B or C
Many patients have a chronic single symptoms at level A, and maybe a
chronic symptom complex at level B, but &#8211; as long as their episode is
not worsening (or provided the patient's episodes are not becoming more
frequent which would be a separate clinically informative query) &#8211; then
it may be tolerable to optionally and by default omit such patients with
chronicity of > 6 or 9 months from inclusion in the result of a query if
the purpose is "who must I make sure I do not overlook a condition that I
should perhaps be diagnosing?"
</pre>
<p></p>
<h2><a name="Backend_note"></a> Backend note </h2>
<p></p>
Report queries are stored in cfg.report_query.
<p></p>
<a name="TopicEnd"></a>
<p></p>
<p></p>
<p></p>
<p></p>
</div>
</body></html>
|