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 a an nVISAGE report by adding a calculated column field.
...
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' |
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') |
| Note |
|---|
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 |
| Info |
|---|
Calculated columns can cannot reference other calculated columns but only those to the left(yet) |
| Info |
|---|
Calculations are always performed prior to any filters or aggregations. This is most often desired, but can be inconvenient in some situations. However more complex calculations (e.g. that also include the filtering) can often work for these situations. |