Expressions/T-SQL Quick Reference Guide
This page contains the following sections:
String Functions
LEN()
<expression name="user_name_length">
LEN(user_name)
</expression>Output: 8 (if user_name = 'jsmith01')
LEFT()
<expression name="dept_prefix">
LEFT(department_name, 3)
</expression>Output: "Ops" (if department_name = 'Operations')
RIGHT()
<expression name="last4_phone">
RIGHT(phone_number, 4)
</expression>Output: "1234" (if phone_number = '555-867-1234')
SUBSTRING()
<expression name="short_comment">
SUBSTRING(comments, 1, 20)
</expression>Output: "Initial damage asses" (if comments = 'Initial damage assessment indicates no damage.')
REPLACE()
<expression name="status_replace">
REPLACE(status, 'Open', 'Active')
</expression>Output: "Active" (if status = 'Open')
UPPER()
<expression name="assigned_upper">
UPPER(assigned_to)
</expression>Output: "CAPTAIN ROGERS" (if assigned_to = 'Captain Rogers')
LOWER()
<expression name="assigned_lower">
LOWER(assigned_to)
</expression>Output: "captain rogers" (if assigned_to = 'Captain Rogers')
CONCAT()
<expression name="full_name">
CONCAT(first_name, ' ', last_name)
</expression>Output: "Jane Doe" (if first_name = 'Jane' and last_name = 'Doe')
Date & Time Functions
GETDATE()
<expression name="current_timestamp">
GETDATE()
</expression>Output: "2025-08-26 14:35:12.000"
DATEADD()
<expression name="review_due_date">
DATEADD(day, 7, report_date)
</expression>Output: "2025-09-02" (if report_date = '2025-08-26')
DATEDIFF()
<expression name="hours_since_incident_start">
DATEDIFF(hour, incident_start, GETDATE())
</expression>Output: 12 (if incident started 12 hours ago)
DATENAME()
<expression name="incident_weekday">
DATENAME(weekday, incident_date)
</expression>Output: "Tuesday" (if incident_date = '2025-08-26')
EOMONTH()
<expression name="end_of_month">
EOMONTH(report_date)
</expression>Output: "2025-08-31" (if report_date = '2025-08-26')
Math & Numeric Functions
ABS()
<expression name="abs_difference">
ABS(value_difference)
</expression>Output: 25 (if value_difference = -25)
ROUND()
<expression name="rounded_lat">
ROUND(latitude, 2)
</expression>Output: 34.72 (if latitude = '34.71839281')
POWER()
<expression name="risk_score_squared">
POWER(risk_score, 2)
</expression>Output: 49 (if risk_score = 7)
SQRT()
<expression name="square_root_area">
SQRT(area)
</expression>Output: 10 (if area = 100)
Conversion Functions
CAST()
<expression name="Record_id_text">
CAST(record_id AS varchar(10))
</expression>Output: "10234" (if record_id = 10234)
CONVERT()
<expression name="report_date_formatted">
CONVERT(varchar, report_date, 101)
</expression>Output: "08/26/2025" (if report_date = '2025-08-26')
Conditional / Logical Functions
ISNULL()
<expression name="assigned_or_default">
ISNULL(assigned_to, 'Unassigned')
</expression>Output: "Unassigned" (if assigned_to IS NULL)
CASE()
<expression name="priority_label">
CASE
WHEN priority = 1 THEN 'Critical'
WHEN priority = 2 THEN 'High'
ELSE 'Normal'
END
</expression>Output: "Critical" (if priority = 1)
IIF()
<expression name="status_label">
IIF(status = 'Open', 'Active', 'Closed')
</expression>Output: "Active" (if status = Open)
Aggregate Functions
These are used outside the <eocrepeatallrecords> tag to output aggregate calculations based on all records.
SUM()
<expression name="total_supplies">
SUM(total_supplies)
</expression>Output: 157
AVG()
<expression name="avg_response_time">
AVG(response_time)
</expression>Output: 42.5
COUNT()
<expression name="record_count">
COUNT(*)
</expression>Output: 12
CASE Inside Aggregate (Counts per Category)
Count Open Incidents
<expression name="open_incident_count">
SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 5
Count by Priority
<expression name="critical_count">
SUM(CASE WHEN priority = 1 THEN 1 ELSE 0 END)
</expression><expression name="high_count">
SUM(CASE WHEN priority = 2 THEN 1 ELSE 0 END)
</expression><expression name="medium_count">
SUM(CASE WHEN priority = 3 THEN 1 ELSE 0 END)
</expression>Output: critical_count = 2, high_count = 7, medium_count = 3
Combining Functions
Many of these functions can be nested together to handle edge cases.
For example, you can combine logical functions (CASE) with null-handling functions (ISNULL) to make your board expressions more resilient.
ISNULL + CASE + SUM
<expression name="open_incident_count_safe">
SUM(CASE WHEN ISNULL(status, '') = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 5 (same as before, but safely ignores NULL values instead of breaking logic)
TRIM + UPPER + CASE
Normalize text before comparison to avoid whitespace/case issues.
<expression name="normalized_status_label">
CASE
WHEN UPPER(TRIM(status)) = 'OPEN' THEN 'Active'
WHEN UPPER(TRIM(status)) = 'CLOSED' THEN 'Closed'
ELSE 'Unknown'
END
</expression>Output: "Active" (for ' open ', 'Open', 'OPEN', etc.)
ISNULL + FORMAT/CONVERT (Date Text)
Show a friendly date or “N/A” if missing.
Normalize text before comparison to avoid whitespace/case issues.
<expression name="report_date_mmddyyyy_or_na">
ISNULL(CONVERT(varchar, report_date, 101), 'N/A')
</expression>Output: "08/26/2025"; "N/A" if report_date IS NULL
COALESCE + REPLACE + CONCAT (Clean + Build)
Remove dashes from phone, fill missing, and label it.
<expression name="clean_phone_label">
CONCAT('Phone: ', REPLACE(COALESCE(phone_number, 'N/A'), '-', ''))
</expression>Output: "Phone: 5558671234"; "Phone: N/A" if empty
Attributes
-
useboardlevelfilters default = false
-
usegrouplevelfilters default = true
-
useuserlevelfilters default = true
-
omitalias default = false
-
filter has no default (only does something when you include it)
Attribute Examples
Respect the board-level view filter (non-default: set to true)
If the board view has a default view filter, opt-in so the expression honors it.
<expression name="open_incident_count_board_scope"
useboardlevelfilters="true">
SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 3 (counts only records in the board’s default view filter; for example, region = “North”)
Ignore the group-level filter (non-default: set to false)
By default, group filters are applied. Turn them off to ignore view filters that are applied to a group when assigning out a board.
<expression name="supply_total_all_groups"
usegrouplevelfilters="false">
SUM(total_supplies)
</expression>Output: 420 (totals across all groups, even if the board is assigned to a group with a view filter applied)
Ignore user-level filters (non-default: set to false)
By default, whatever the user has filtered in the UI is applied. Turn it off to ignore user’s ad-hoc filters (for example, <search>, <filterbutton>, <filterlistdropdown> etc).
<expression name="record_count_ignore_user_filters"
useuserlevelfilters="false">
COUNT(*)
</expression>Output: 128 (counts all records despite the user’s current filter selections)
Combine scope controls: board filter ON, user & group filters OFF
Useful for a board-level summary that should reflect the board’s view but not per-user/group scoping.
<expression name="open_incidents_board_only"
useboardlevelfilters="true"
usegrouplevelfilters="false"
useuserlevelfilters="false">
SUM(CASE WHEN status = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 9 (counts within the board’s default view only, ignoring group & user filters)
Use a filter (WHERE clause) for targeted aggregates
The filter narrows the records considered by the expression (especially handy with aggregates).
<expression name="critical_incident_count"
filter="priority = 1">
COUNT(*)
</expression>Output: 4 (counts only priority = 1)
Another example:
<expression name="recent_open_incidents_7d"
filter="status = 'Open' AND incident_start > DATEADD(day, -7, GETDATE())">
COUNT(*)
</expression>Output: 2 (open incidents started in the last 7 days)
filter + NULL-safety with ISNULL
<expression name="open_incident_count_safe_filtered"
filter="priority IS NOT NULL">
SUM(CASE WHEN ISNULL(status, '') = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 5 (counts open incidents after excluding NULLs via filter, and still null-safe inside the CASE)
Full combo: board scope ON, ignore group/user filters, targeted WHERE, null-safe counting
<expression name="board_scoped_open_last_24h_safe"
useboardlevelfilters="true"
usegrouplevelfilters="false"
useuserlevelfilters="false"
filter="incident_start >= DATEADD(hour, -24, GETDATE())">
SUM(CASE WHEN ISNULL(status, '') = 'Open' THEN 1 ELSE 0 END)
</expression>Output: 6 (open incidents in last 24h within the board’s default view only, ignoring group & user filters; null-safe)
omitalias (non-default: set to true)
Prevents WebEOC from aliasing the SQL result set column to the name value.
<expression name="incident_age_hours_no_alias"
omitalias="true">
DATEDIFF(hour, incident_start, GETDATE())
</expression>Output: 12 (hours since start; underlying SQL column is not forced to alias to incident_age_hours_no_alias)
Why omitalias="true" here?
When your <expression> is just a thin wrapper around one <relatedexpression>, WebEOC would normally alias the outer expression as the outer name. Setting omitalias="true" tells WebEOC not to add that extra alias so the inner <relatedexpression name="…"> becomes the column name. This is especially helpful in repeated child rows to avoid double aliasing or ambiguous names.
Example — Repeating child rows and rolling up a related table total
Scenario: In an incident detail view, you repeat deployments; for each deployment you want the roll-up total\_cost from a related deployment\_items table.
<eocrepeatallrecords>
<!-- Other repeated fields … -->
<!-- Use omitalias so the inner relatedexpression alias 'total_cost' is kept -->
<expression name="total_cost_passthrough" omitalias="true">
<relatedexpression name="total_cost"
table="Deployment Items"
where="deployment_item_id = deployment_id">
ISNULL(SUM(ISNULL(cost,0) * ISNULL(quantity,0)), 0)
</relatedexpression>
</eocrepeatallrecords>
</expression>Sample output (per repeated row):
-
Deployment DEP-101 → total_cost = 1,540.00
-
Deployment DEP-102 → total_cost = 0.00
-
Deployment DEP-103 → total_cost = 325.50
Because omitalias="true", the column in the result set is total_cost (the inner name), not total_cost_passthrough.
Tips
-
Use omitalias="true" only when the outer <expression> simply returns one <relatedexpression> and you want the inner name to be the column name.
-
If you add math around multiple <relatedexpression>'s (for example, sum1 + sum2), you’ll typically want the outer alias; don’t set omitalias="true" there, or you may end up with “no column name” in the SQL result.