Skip to content
This repository has been archived by the owner on Sep 9, 2024. It is now read-only.

Admin FAQ How to create new charts on the patient dashboard

Jump to bottom
Anonymous edited this page Sep 13, 2022 · 1 revision

How to create new charts on the patient dashboard?

Table of contents

Custom charts on the patient dashboard

Connect for Life™ allows implementers to include a custom chart widget in the patient dashboard – the view generated for each patient. This view is built from many different widgets, some coming from core OpenMRS, that show the user an overview of the given patient. A chart can be added to this dashboard, built from a custom underlying SQL query, which gives a lot of flexibility and virtually allows including any kind of data on this dashboard.

Chart types

There are two types of charts.

The first type is a bar chart without any grouping on the X-axis:

The second type is a bar chart that has data grouping on the X-axis:

Adding a chart without data grouping

First, we need to add a new data set definition with the SQL query that will be used.

  1. Go to System Administration

  2. Select Advanced Administration

  3. Select Data Set Definitions under Manager Report Definitions

  4. Click the ‘+’ icon next to SQL Dataset to add a new data set definition

  5. Give the data set a name and description and click "Submit"

  6. Enter an SQL query in the form. You need to follow a few pointers when creating the query:

  • The query must return integer values (most often these are count(*) functions)
  • Each select statement should be marked with an alias
  • Patient id, actor id and person id are set automatically, they can be used via parameter names preceded by a colon e.g. :patientId, :actorId, :personId. Since this is a chart shown on the patient dashboard, using at least the patient ID is a must.
  • You can use subqueries

An example query for returning the number of past and future patient visits:

select 
(select count(*) from visit where date_started > now() and patient_id = :patientId) as futureVisits, 
(select count(*) from visit where date_started < now() and patient_id = :patientId) as pastVisits;

As we can see, the query returns integer values (from count(*) functions) and each "select" statement is marked with an alias. This query requires patient id for each particular patient so :patientId parameter has been used.

Note: In order to preview the query in these administration UI tools, you have to provide the pre-set parameters used in the query, such as :patientId.

The query can return for example:

The next step is to add a new app JSON file with the dashboard configuration. This app JSON will enable the chart on the dashboard UI.

  1. Go to System Administration

  2. Select Manage Apps

  3. Select Add App Definition

  4. Enter the JSON in the *Definition" field

JSON to enter:

{
   "id":"messages.graph1Test",
   "description":"Adds visit graph to patient dashboard",
   "extensions":[
      {
         "id":"messages.graph1Test.visitGraph",
         "order":6,
         "extensionPointId":"patientDashboard.secondColumnFragments",
         "extensionParams":{
            "provider":"messages",
            "fragment":"patientdashboard/graph",
            "fragmentConfig":{
               "icon":"icon-bar-chart",
               "label":"Visit Graph",
               "additionalTitle":"Number of past and future patient visits",
               "xAxisLabel":"Past/Future Visits",
               "yAxisLabel":"Number of visits",
               "colorRange":[
                 "#a34a3d",
                 "#10708e"
               ],
               "columnsInfo":{
                  "pastVisits":"Past Visits",
                  "futureVisits":"Future Visits"
               },
               "graphDataSetName":"Visit chart"
            }
         }
      }
   ]
}

The most important part of this file is ‘fragmentConfig’ part and its fields:

  • "icon": "icon-bar-chart" - icon that is displayed next to graph label (optional field)
  • "label": "Visit Graph" - header of the graph (required field)
  • "additionalTitle": "Number of past and future patient visits" - additional label displayed under the main label (optional field)
  • "xAxisLabel": "Past/Future Visits" - label that is displayed under the X axis (optional field)
  • "yAxisLabel": "Number of visits" - label that is displayed next to the Y axis (optional field)
  • "colorRange": ["#a34a3d", "#10708e"] - array of hex values of column colors, should be as many values as number of aliases in ‘columnsInfo’ section below, first value from "colorRange" array matches first alias in "columnsInfo", second color matches second alias etc. (required field)
  • "columnsInfo": { "pastVisits":"Past Visits", "futureVisits": "Future Visits"} - here is a map with aliases returned in the sql query we created earlier, with related labels which are displayed on graph (required field).
  • "graphDataSetName":"Visit chart" - the name of data set definition to use, the one we created earlier (required field)

After saving the new app, the chart will now be visible after entering a patient dashboard.

Adding a grouped chart

The procedure for adding a grouped chart is exactly the same as for adding the non-grouped chart described in the previous section. The only two differences are the contents of the SQL query and the JSON app definition. Please follow the instructions from the previous section, but use the SQL and JSON provided below.

SQL for retrieving data in a grouped form:

select  
    response as responseAlias,  
    week(answered_time,1) as weekNumber,  
    count(*) as numberOfResponses  
    from messages_actor_response  
    where question = 165874  
    and patient_id = :patientId 
    and actor_id = :actorId 
    and answered_time > (select now() - interval 1 month) 
    and answered_time < now() 
    group by weekNumber, responseAlias 
union select  
    'Not responded' as responseAlias, 
    week(date_created,1) as weekNumber,  
    count(*) as numberOfResponses  
    from     messages_scheduled_service  
    where patient_template_id = (select messages_patient_template_id from messages_patient_template where patient_id = :patientId and actor_id = :actorId 
    and template_id = (select messages_template_id from messages_template where name = "Adherence report daily"))  
    and service = "Adherence report daily" 
    and group_id not in  
    (select source_id from messages_actor_response where question = 165874 and actor_id = :actorId and patient_id = :patientId  
    and answered_time > (select now() - interval 1 month) 
        and answered_time < now())  
      group by weekNumber, responseAlias;

When executed, it will return for example:

As we can see in this type of graph our query should return possible results (responseAlias column), number of particular results (numberOfResponses columns) and values by which data should be grouped (weekNumber column). It is important that the query will return these three columns. How they are retrieved by the query from the database is totally up to the implementer and their requirements.

The JSON app file for a grouped chart is similar to the one used for the ungrouped chart:

{
    "id":"messages.graph2Test",
    "description":"Adds visit graph to patient dashboard",
    "extensions": [
     {
       "id": "messages.graph2Test.adherenceGraph",
       "order": 6,
       "extensionPointId": "patientDashboard.secondColumnFragments",
       "extensionParams": {
       "provider": "messages",
       "fragment": "patientdashboard/graph",
        "fragmentConfig": {
           "icon": "icon-bar-chart",
           "label": "Adherence Graph",
           "additionalTitle": "Patient adherence results",
           "xAxisLabel": "Week number",
           "yAxisLabel": "Number of responses",
           "colorRange": ["#a34a3d", "#10708e", "#eba45b"],
           "columnsInfo": {"165913":"Taken on Time","166007":"Not taken","Not responded":"Not Responded"},
           "graphDataSetName":"Adherence graph",
           "responseAlias":"responseAlias",
           "countResultAlias":"numberOfResponses",
           "groupByAlias":"weekNumber"
          }
        }
      }
    ]
}

The fields to pay attention to are:

  • "columnsInfo": {"165913":"Taken on Time","166007":"Not taken","Not responded":"Not Responded"} - here we have to include all possible results which we want to take into account when displaying the graph
  • "responseAlias":"responseAlias" - here we have to set alias name which we used in SQL query for returning possible results (look in query above)
  • "countResultAlias":"numberOfResponses" - here we have to set alias name which we used in SQL query for returning number of results from possible results (look in query above)
  • "groupByAlias":"weekNumber" - here we have to set alias name which we used in SQL query to determine by which value we want to group returned results (look in query above)

The chart should then show up on the patient dashboard:

Home

ADMIN GUIDE

Configuration
Modules
FAQ

USER GUIDE

Modules

DEVELOPER GUIDE

Installation
Governance
Clone this wiki locally