Add a Calculated Column to a Report

NOTE: Version 2024.2 has significantly improved the way this capability works. Data field Tags are now visible by hovering over a column name or in the Data Field selection lists. And the user can now select fields from a drop down (instead of typing them in) and this will inject the column tag instead.

Advanced operations can be applied to data returned in an nVISAGE report by adding a calculated column field.

Steps:

Open a report and click the hamburger navigation in the upper right corner
Select Add Calculated Column
For the Name field, enter what you would like the new column name to be on the report
Select the data type (this is important for sorting or criteria based on data type, e.g. > ‘2020-01-01’)
Enter the formula to be performed on the data being returned to the report

E.g. CONCAT(LEFT(`permit.permit number`, 5), ‘_KSO’)

Calculated columns do not currently work with Custom data sources.

Important! Use backticks ( ` ) around datasource.field, and single quotes ( ' ) around text.

The data source name is optional, if the field name is unique you can just reference that, e.g., `Tank Capacity gals`. You can also abbreviate data source and /or field names, e.g., `Tank Capacity` or `Entity.Site Name`. If you abbreviate and there are more than one matching column, it will pick the left most column in the calculation.

If you make an error in your syntax, the application will inform you where the error is occurring:

Right click on the column and select Edit Column to make corrections. Once complete and accurate, the new column will appear on the report.

This new calculated field can also be used in advanced filtering…

Pretty much any SQL function can be used in the calculated column, case statements, max/min, numerical calculations, etc.

Some examples:

Number of days since receivable created (NOTE: getdate() is today)	datediff(day,`receivable.receivable date`, getdate())
Permit Sequence NOTE: using a sequence, one can then filter on this column where it = 1, which means you return the the most recent permit for each combination of Site and Permit Category.	row_number() over (partition by `regulated entity.number`, `permit.category` order by `permit.version` DESC)
Site Permit Seq excl Draft NOTE: as above, but excluding any draft permits	row_number() over (partition by `regulated entity.number`, `permit.category` order by IIF(`permit.status` = 'Draft',1,0), `permit.version` DESC)
Type + Stat	CASE WHEN `PERMIT.Type` = 'Stormwater Construction' AND `PERMIT.status` IN ('Closed', 'Expired', 'Terminated') THEN 'SWC Inactive' WHEN `PERMIT.Type` = 'Stormwater Industrial' AND `PERMIT.Status` IN ('Closed', 'Expired', 'Terminated') THEN 'SWInd Inactive' WHEN `PERMIT.Type` = 'Ready Mix' THEN 'WW ReadyMix' WHEN `PERMIT.Category` = 'BOW Individual Wastewater Permit' AND `PERMIT.Type` <> 'Industrial Pretreatment' THEN 'WW CMIF' ELSE '' END
Permit process speed	DATEDIFF(d,`submission.received date`,`permit.issue date`)
Inspection Priority	case when `entity.county` IN ('Rice', 'Sedgwick') then 'High' when `entity.county` = 'lyon' and `permit.effective date` < getdate() - 365 THEN 'Medium' ELSE 'Low' END
Days in effect	iif(`permit.status` = 'in effect', datediff(day,`permit.effective date`, getdate()), null)
Permit number	CONCAT(LEFT(`permit.permit number`, 3), 'DLB')
Days from submit until permit	DATEDIFF(d,`submission.received date`,`permit.issue date`)
Tank Seq	row_number() over (partition by `entity.number`, `Tanks.BER Tank Number` order by `permit.effective date` desc)
Eval Seq NOTE: When column = 1, returns the most recent evaluation for each permit.	row_number() OVER (PARTITION BY `permit.permit number` ORDER BY `evaluation.start date` DESC)
Receivable + 30 days	dateadd(d,30,`receivable.receivable date`)
Construction Permit?	IIF(`permit.category` like '%construction%', 'Yes', 'No')
Paid too early?	DATEDIFF(d,`payable.received date`, `payment.payment date`)
Permit Issue Year	YEAR(`Issue Date`)
Format Date as String (Useful for when client wants a different name for a column in a nVISAGE report than the system field name)	FORMAT(`Next Application Due`, 'd')

A formula typically references other columns (i.e. data fields) by name (not tag, which would be preferred, but is currently hidden form the user). If such a name is changed (by curating the Data Hub Catalog to improve the default data field names), then these formula may break. There are system reports available that will identify each report that may need to be reviewed when making such name changes. NOTE: This will be resolved in v2024.2 where the Tags will be stored instead of then column name, thus making it immune to column name changes

Calculated columns can reference other calculated columns but only those to the left.