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.

NOTE: From version 7.1.10.375 RC1 of Helical Insight certain more options are added which are explained below.
a. Configure a Custom Date Format in Display
When we add a date/date time filter, in many cases we may want a user to select a date field in a very custom format (like Month – Year) etc. Even though we provide multiple prebuilt display options (via FX) which can be used with Dates, in such cases wherein a very custom option is required this new functionality is helpful.
Now the Display section supports many date-format configuration. You can select one of the available formats or enter a custom format and add it. This helps present date values in the format required by the report or its users, without changing the underlying date value used by the filter.

As you can see there is a whole list of formats (you can see more details as soon as you hover over any format). Click and select it. Then click on Close button.

As shown in the above image, you can also create your own custom format, click on Add, then it will get added to the list. Then select that from the drop down and close.
Now for an end user the dates will display in this custom format.
b. Apply Operations to Display and Value Fields
The field menu provides additional operations that can be applied to the selected field in the Display or Value section. Depending on the field and the required report logic, the available options include:
- Aggregate: Apply an aggregation operation where supported.
- Functions: Apply any available function to the selected field.
- Group by: Group the field values.
- Order By: Sort the values in ascending or descending order.

c. Drag and Drop Fields from Metadata
You can also drag fields directly from the Metadata panel and drop them into the Display or Value section. This provides flexibility to use different fields for the user-facing display and the underlying filter value, rather than relying only on the field originally added to the filter.

Thank You,
Helical Insight
