Versions Compared

Old Version 3

changes.mady.by.user Priya Joshi

Saved on

compared with

New Version 4

changes.mady.by.user Priya Joshi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Sr. No.

Object Type

Object Name

Added Columns

Comments

1

DB View

VSO_PatientVisit

EHRComplete

2

DB View

VSO_EncObs

BHWFormType

MidwifeFormType

3

SQLlab Query

VSO_Visitswithoutforms

Query -

select g.visit_id, sum(case g.concept_id
when 163213 then 1 else 0 end) as 'PhyEx',
sum(case g.concept_id when 163212 then 1
else 0 end) as 'CurrCom',
sum(case g.concept_id
when 163211 then 1
else 0 end) as 'FamilyHis',
sum(case g.concept_id
when 163210 then 1
else 0 end) as 'MedHis'
from VSO_EncObs g
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
group by visit_id

4

SQLlab Query

VSO_Patient_Visit_Experience

Query -

select concat(Year,"/",Month) as YearMon,
last_day(date_started) as Date_Started,
0 as Value_Text,
0 as 'Val_WT',
0 as 'PAT_WT',
0 as 'Weight',
(count(distinct visit_id) -
count(distinct(case concept_id
when 163343 then visit_id
else null
end))) as Visits
from VSO_EncObs
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
and location <> 'Remote'
group by Year,
Month,
last_day(date_started)
union
select concat(Year,"/",Month) as YearMon,
last_day(date_started) as Date_Started,
value_text,
1 as Value_WT,
1count(distinct(visit_id)) as 'PAT_WT',
value_textcount(distinct(visit_id)) as 'Weight',
count(distinct(visit_id)) as Visits
from VSO_EncObs
where visit_voided = 0
and encounter_voided = 0
and obs_voided = 0
and location <> 'Remote'
and concept_id = 163343
group by Year,
Month,
last_day(date_started),
value_text
order by 1,2,3,4;

Connecting to Google Sheets as Database

Superset has a feature to connect to Google Sheets as a database. There are certain setups that are necessary to be done in order to make this feature available.

Step1:

  1. To implement Google authentication, create a service account as follows:

    1. Login to Google Console Admin using your intelehealth email id (https://console.cloud.google.com/iam-admin/serviceaccounts)

    2. Image Added

    3. Ensure you select “Enable G-suite domain wide delegation” checkbox

    4. Download the key created by google for your service account as json file

  2. Manage API client access at https://admin.google.com/${DOMAIN}/AdminHome?chromeless=1#OGX:ManageOauthClients

    1. Add the "Unique ID" from step 1 as the "Client Name"

    2. Add the scope as “https://www.googleapis.com/auth/spreadsheets.readonly”

  3. Connect to superset server 13.233.116.180 (user: ubuntu)

  4. Run following commands in the sequence mentioned below:

    1. cd superset/my_viz

    2. . bin/activate

    3. pip install gsheetsdb

    4. pip install gsheetsdb[cli]

    5. pip install gsheetsdb[sqlalchemy]

  5. Go to path /home/ubuntu/superset/my_viz/lib/python3.6/site-packages/gsheetsdb

    1. Access file auth.py under this folder

    2. Add the following lines at the start of function “get_credentials_from_auth”:

      1. if not subject:
        subject='priya@intelehealth.io'

      2. if not service_account_file:
        service_account_file='/home/ubuntu/superset/my_viz/intelehealthsuperset-4001d197be72.json/intelehealthsuperset-4001d197be72.json'

  6. stop the superset python process running on the server & restart the same

  7. Connect to Superset application in browser as admin using https://dashboards.intelehealth.io

  8. Go to menu Sources → Databases

  9. Add a new database with below details:

    1. Database Name: Google_Sheets

    2. SQLAlchemy URI : gsheets://

    3. Click the button “Test Connections”

    4. Once the connection is established successfully, save the new database by hitting the save button

  10. Now you can create a table based on google sheets as follows:

    1. Access the menu Sources → Tables

    2. Create a new table where database is “Google_Sheets” and Table Name is the shareable link of the concerned google sheet.