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
Enter the formula to be performed on the data being returned to the report
E.g. CONCAT(LEFT(`permit.permit number`, 5), ‘_KSO’)
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:
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`) |
Calculated columns do not currently work in Custom reports.