Reports - Custom Column Expressions

Purpose: An advanced feature that allows entering SQL expressions into a report column to do display the results of calculations, date subtractions and additions, and much more. You cannot update data with this feature.

Cost: None.

LegalServer staff must enable this feature, which will then expose a "Report SQL Formula Editing" user role permission that administrators can give to the desired user roles.

Requesting the Feature

File a ticket from your site, and copy and paste the following required text into it:

We solemnly acknowledge:

This is an advanced feature. There is no hand holding, no friendly messages, no guide to writing SQL.
You do not have an ERD, handy "field guide", etc., and we will not ask for one.
LegalServer staff may offer to help with an expression, or may refuse, or tell us there will be a charge to help with an expression.
If an expression breaks a report, that is on us.
If an expression produces an incorrect result because our logic is wrong, that's on us.
If an expression causes a report to be horribly slow, that's on us.

Optionally include in your ticket how you say "SQL". We probably won't refuse to enable it if you get it wrong.

(Ref: LS-135402)

Notes on Using Expressions

Table Names

You can use %tableAlias% to reference the current table. Most of the report code automatically aliases the tables as matter_5 or person_22 depending on how they are added to the report. This alias will allow you to reference the current table the field is created on instead of needing to know the name of the table directly.

Parentheses

We recommend wrapping whatever expression you create in a set of parentheses to make sure it is evaluated on its own.

Filtering

Some Expressions will now allow you to filter on them. Others may allow you to filter on them. If the formula involves any aggregation, you may want to use the Advanced -> Apply after Aggregate option on the filter.

SQL Language

LegalServer uses Postgres 14.