Advanced Report Expressions (Other Examples)

• Totals for Complete/Missed/Cancelled/Aborted: 

When a customer needs to have an advanced report but still have totals or counts for specific column types, then there are a few things to be mindful of.

This example is a report to only report missed/complete/cancelled/aborted based on patients over a specific weight. We have to create a table for each reason for close. There is also a parameter to add to each column to not show data for that column in the total row.

1. First note that there is a table for each reason for close, also a title above each

2. Row Grouper Contents:

a. CASE WHEN ((cast(data(id, 'transfer item field', 'weight') AS float) > '279' AND data('transfer item field', 'weightunit') = 'lbs') OR
(cast(data(id, 'transfer item field', 'weight') AS float) > '126' AND data('transfer item field', 'weightunit') = 'kg'))
and
data(id, 'request reason for close level 1')
= 'Request Complete'
THEN data('request number')
ELSE null
END

3. Right-clicking on the number, you will get the option to add a totals row

4. Add the parameter, total case() to each of the columns that you don’t want to have data appear in the totals row.

total_case(strftime('%m/%d/%Y',data('request time', '#dateOfService'),'localtime'), '')

5. Add a column for totals, simply place count(*) in the column. See images below.

• Doing count by county/base

Row Grouper should be then 1 else null end

case when (data('request call type') = 'Scene Flight' and data('request reason for close level 1') = 'Mission Complete')
then 1 else null end

For columns make sure you have the id,…
select count(*) from row where data(id, 'request base') = 'Alice Base'

select count(*) from row where data(id, 'patient leg pick up county') = 'Jim Hogg'

• Latitude and Longitude for Patient Waypoints

Patient leg pick up waypoint (latitude)

select data(id, 'waypoint latitude') from row where data(id, 'waypoint id') = data(id, 'patient leg pick up waypoint id')

Patient leg pick up waypoint longitude

select data(id, 'waypoint longitude') from row where data(id, 'waypoint id') =
data(id, 'patient leg pick up waypoint id')

Patient leg drop off waypoint (latitude)

select data(id, 'waypoint longitude') from row where data(id, 'waypoint id') =
data(id, 'patient leg drop off waypoint id')

Patient leg drop off waypoint (longitude)

select data(id, 'waypoint latitude') from row where data(id, 'waypoint id') =
data(id, 'patient leg drop off waypoint id')

• If interested in learning the code for SQLITE:

Date and Time Functions

• Scheduled Times

case when data(id, 'request scheduled time') != '' then data(id, 'request number') else null end

• Creating table by Row instead of columns (use this for Row Grouper)

case when (data('transfer item field', 'weight') != '' and data('transfer item field', 'weight_unit')='kg') then
case when cast(data(id, 'transfer item field', 'weight') as float) > 30 then 'a'
when cast(data(id, 'transfer item field', 'weight') as float) > 10
and cast(data(id, 'transfer item field', 'weight') as float) <= 30 then 'b'
when cast(data(id, 'transfer item field', 'weight') as float) >= 5
and cast(data(id, 'transfer item field', 'weight') as float) <= 10 then 'c'
else 'd' end

else null end

Columns would be

case param('grouper') when 'a' then '> 30 kg'
when 'b' then '10-30kg'
else '' end

Second column

count(*)

• Patient Mileage

cast(data(id, 'request patient mileage') as float)/10

Related Articles:

Advance Report Expressions (Row Grouper Examples)

Advanced Report Expressions (Pickup/Drop off SQL Times)

Advanced Report Expressions (Examples for Columns)