Filter Expressions for Dynamic Query View

This blog gives details about the filter expressions that can be used in the dynamic query view on the metadata page of Helical Insight.

Dynamic queries allow the report level filters to be used in the view to generate the query based on those filters.

Filters can be accessed by using the ${filter} notation.

Filter has many properties like filter label,mode,condition,value etc based on which user can create dynamic view based upon the requirement and the logic.

Below mentioned image shows the output of the different expressions:

Filter Expressions

Expression formData
Description Filter returns the filter component including all filters used in adhoc report along with its filter information.

With (.) dot notation end user can fetch required information from filters.

Formdata is : 

"filters": [
		{
			"values": [
				"Agent"
			],
			"mode": "auto",
			"dataType": "java.lang.String",
			"orderBy": "",
			"isFilterEditable": false,
			"encloseInQuotes": false,
			"dateTimeToggle": false,
			"label": "booking_platform",
			"column": "HIUSER.FilterDynamicView.booking_platform",
			"rangeValuesType": "",
			"id": 0,
			"condition": "EQUALS",
			"valuesRange": {},
			"rangeSelectionToggole": true
		},
		{
			"values": [
				"Agra"
			],
			"mode": "auto",
			"dataType": "java.lang.String",
			"orderBy": "",
			"isFilterEditable": false,
			"encloseInQuotes": false,
			"dateTimeToggle": false,
			"label": "destination",
			"column": "HIUSER.FilterDynamicView.destination",
			"rangeValuesType": "",
			"id": 1,
			"condition": "EQUALS",
			"valuesRange": {},
			"rangeSelectionToggole": true
		}
	]

For Ex. return filters

Output:
[{
	fullyQualifiedColumn="FilterDynamicView"."booking_platform",
	column="booking_platform",
	condition==,
	isCustomCondition=false,
	dataType=java.lang.String,
	custom=null,
	value='Agent',
	values=[Agent],
	isCustomValue=false,
	id=0,
	label=booking_platform,
	mode=auto
}

,
{
	fullyQualifiedColumn="FilterDynamicView"."destination",
	column="destination",
	condition==,
	isCustomCondition=false,
	dataType=java.lang.String,
	custom=null,
	value='Agra',
	values=[Agra],
	isCustomValue=false,
	id=1,
	label=destination,
	mode=auto
}]

It returns filters component with all filter information.

Note : filters specific information can be retrieved by (.) dot notation.

Example :return filters.fullyQualifiedColumn

Output: 

["FilterDynamicView"."booking_platform",
"FilterDynamicView"."destination"]

This way end user can utilize required filters information.

FILTER EXPRESSIONS

  1. {filter}.label
    2. Expression ${filter}.label
    Description With this filter expression in dynamic view, runtime will provide comma separated list of all the filter labels present in report.

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return “${filter}.label”

    Output : booking_platform,destination

  2. {filter}.id
    3. Expression ${filter}.id
    Description With this filter expression in dynamic view, runtime will provide comma separated list of all the filter id’s present in report.
    The id starts from the 0 and ends with n-1 filters.

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return “${filter}.id”

    Output :0,1

  3. {filter}.column
    4. Expression ${filter}.column
    Description With this filter expression in dynamic view,runtime will provide comma separated list of all the filter column. This gives only the column name.

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return “${filter}.column”

    Output : “booking_platform”,”destination”

  4. {filter}.fullyQualifiedColumn
    5. Expression ${filter}.fullyQualifiedColumn
    Description With this filter expression in dynamic view,runtime will provide comma separated list of all qualified column. The qualified column has “catalog.schema.table.column” pattern

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return “${filter}.fullyQualifiedColumn”

    Output :”FilterDynamicView”.”booking_platform”,”FilterDynamicView”.”destination”

  5. {filter}.values
    6. Expression ${filter}.values
    Description With this filter expression in dynamic view,runtime will provide comma separated listof all the filter values as an array notation. The values are quoted when the value is text/date and not quoted when value is number.

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return ” ${filter}.values”

    Output : [‘Agent’,’Agra’]

  6. {filter}.value
    7. Expression ${filter}.value
    Description With this filter expression in dynamic view,runtime will provide comma separated list of all the filter values are not in array notation. The values are quoted when the value is text/date and not quoted when number

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return ” ${filter}.value”

    Output : ‘Agent’,’Agra’

  7. {filter}.mode
    8. Expression ${filter}.mode
    Description With this filter expression in dynamic view,runtime will provide comma separated list of all the filter mode. The mode is either auto or custom.

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return ” ${filter}.mode”

    Output : auto,auto

  8. {filter}.condition
    9. Expression ${filter}.condition
    Description With this filter expression in dynamic view,runtime will provide comma separated list of of all the filter condition.
    The condtion may be = (equals), <> (not equals) , in (is one of ),not in( is not one of ) etc

    Note : We need to give filter expression in double quotes with return statement.

    For Ex. return ” ${filter}.condition”

    Output : =,=

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at Helical Forum

Written by

Shailaja

Related Posts