Reporting Database Guide

Key Reporting Table

Entity Relationship Model

Novari Reporting Views

Overview

Novari Health provides high-level database views which can be used by report-writers to mine information quickly and easily. The idea is to provide commonly-queried data elements, pre-joined in a series of views.

In this way, instead of joining through to tables manually, the report-writer only needs to join to the view in order to access a wide variety of fields (both stored and calculated) across the database. One advantage to this is some calculated fields can be mined out by the report-writer as though they were stored fields. This will lead to faster, easier and more consistent reporting.

The following tables provide field level summary of included views.

Report Wait List

Report Cancelled Cases

This view gives information on cases that have been removed from the wait list without having treatment completed.

Report WTIS Cases

Sample Reports

Get the percentage of WTIS cases entered as “New Referral”

select distinct report_wait_list.referral_type_desc as REFERRAL_TYPE,

COUNT(distinct report_wait_list.list_code) as CASE_COUNT,

(100*COUNT(distinct report_wait_list.list_code) /

(SELECT count(report_wait_list.list_code)

FROM dbo.report_wait_list,

dbo.report_wtis_cases

where report_wtis_cases.LIST_CODE = report_wait_list.list_code)) AS PERCENTAGE_OF_TOTAL

from dbo.report_wait_list

inner join dbo.report_wtis_cases on report_wtis_cases.LIST_CODE = report_wait_list.list_code

group by report_wait_list.referral_type_desc

Get a list of all cases cancelled in the past 30 days

select report_wait_list.doc_name,

patient.name,

report_cancelled_cases.reason_removed

from dbo.report_wait_list

inner join dbo.patient on patient.patient_id = report_wait_list.patient_id

inner join dbo.report_cancelled_cases on report_cancelled_cases.list_code = report_wait_list.list_code

where (dbo.getDate() - report_cancelled_cases.date_removed) <= 30