With version 5.0 of Helical Insight we have added a lot of advanced functionality in terms of filter usage.
One of those functions is called “Display” and “Value” which we would be explaining how to use.
When a filter is added, you can click on these 3 lines as shown in the image and click on “Advanced”
We can see two subcategories in “Advanced”, first is Display and second is Value.
Display: The value of filters is available as a dropdown, with this Display option we can customize how these should get displayed. For example let us say we can want to display the values in uppercase and in descending order, then we can use these kind of options.
Value: In some cases we might want to display something for user selection but we might want to pass some other value, and in those cases we might choose Value. For example: in the dropdown we want to show EmployeeName but we want to pass EmployeeId, so in those cases we can use Value.
Display & Value Configurations: –
1st option in both Display and Value menu allows you to apply any sort of DBfunctions like UPPER/LOWER/TRIM/WHEN etc before the data gets populated as a dropdown for user selection. While applying please make sure that the dbfunction you apply should match with the datatype which is present in the filter. Like if it is varchar then you can use functions like upper, lower, trim etc. If the datatype is date functions like month week etc can be used.
2nd option can allow you to apply Aggregate function before the value is shown. Refer below image.
3rd option of will allow to use specify and select metadata column. By default it will have the columnname on which you have applied filter (and in most of the cases there might not be any need to change this all together) but you can always select any other column also based on the requirement.
Example: Here we have specified and are now using “UPPER” case, hence the list of values are appearing as upper case.
And you can also select the filter value as Descending order and Ascending order and apply to see the changes as shown in the below images.
Now let us say we want to “Display value” as “Destination” and in “Value” should be the original filter value mentioned here as “destination_id”. Click on apply button here you can see the “destination_id” is now behaving like “Destination”.
Now select the filter value as AGRA & AMBALA now you can see the final output here to achieve these things.
NOTE: Whenever you make such usage and would like to verify if your operations are correct or not you can click on the SQL Viewer and you can verify if the SQL is getting generated correctly.