Advanced operations can be applied to data returned in a 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' |
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`) |
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.
Calculated columns can reference other calculated columns but only those to the left.
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.