Custom column feature allows user to create a separate derived column which could be required for creating adhoc reports. This is useful when the data is required in different format as that of present in the database. Some use cases of Custom column are mentioned below:
- We have ‘time of call’ in the database. Let us say, we want to know for a particular time range, how many calls were received. Then a custom column can be used. In the custom column, we could write formulae to derive the time from time-stamp, then group it and count it.
- In a sales data, if a cost of item column is present. We could create a custom average column which would show the average cost of the item.
Following are the steps to create a custom column:
Step 1: Double click on the custom column button as shown in the snapshot above
Step 2: A window will appear in which user can give a name to the column and write the SQL query to get intended output.
From version 4.0 GA onwards, we have implemented security upgrade and the default rule for custom column is set to “strict-metadata”. You must use fully qualified queries in order to execute the custom column query without any error. There are other options also which can be set like default, strict-metadata, stripped etc. In order to know more about the various security rules, refer to this blog
Step 3: Click on save and then ‘Generate report’
Note: You can also put static values here. You can basically write anything here which will result in a single column of values. Directly write your db function/arithmetic function etc. There is no need to write select, alias, where, having etc clauses here. Also you might be referring to various columns from the db in the custom column, the only precondition is that atleast one of the columns of the tables to which these columns are part of should be present in the dragged selection.For More Info, Contact us at firstname.lastname@example.org