FormData Expressions in Dynamic Query View

In this blog, we have given details of all the formData expressions that can be used in the dynamic query view.

Dynamic query essentially implies that the query is generated at runtime depending on the user inputs.This can be achieved by adding a dynamic logic/groovy script using which end user can use the filters, formdata expressions and groovy user session variables on runtime.

Adhoc report can be created using metadata with dynamic view where in report inputs will be taken from either metadata or from report or from both.

A report with dynamic view gives the better query performance in report along with runtime sql query execution.

formData contains adhoc report formdata with all details of report, metadata, tables, colums, filters info etc. used in adhoc report.

formdata encoding

formdata decoding

Expression formData
Description formData returns adhoc report formdata with all details of report ,metadata,tables.colums,filters info etc. used in adhoc report.

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

For Ex. return formData

In adhoc report ,based on the created report it will return the respective formdata.

Output: 

{
	"location": "1592388157915",

	"metadataFileName": "fa1cb41d-7c8a-43d2-a0a9-8f40b44ad213.metadata",
	"metadataName": "TravelMetadata",

	"metadataDir": "Role Viewer",
	"columns": [
		{
			"column": "HIUSER.dynamic View.destination",
			"alias": "dynamic View_destination" 

		}],
	"functions": {
		"groupBy": []},

	"filters": [{
			"values": [	"Agent"],  
			"mode": "auto",
			"dataType": "java.lang.String",
			"orderBy": "", 

			"isFilterEditable": false,       
			"encloseInQuotes": false,
			"dateTimeToggle": false,
			"label": "dynamic View_booking_platform",
			"column": "HIUSER.dynamic View.booking_platform",
			"rangeValuesType": "",
			"id": 0,
			"condition": "EQUALS",
			"valuesRange": {},
			"rangeSelectionToggole": true
		}],
	"customFilterExpression": "${0}", 

	"limitBy": 1000,
	"prependTableNameToAlias": true,

	"requestId": "HJeBlEqT8",
	"metadataFileJson": {
		"type": "db.generic",
		"databaseType": "Apache Derby 10.12.1.1 - (1704137)",
		"fileName": "TravelMetadata",     
		"connectionType": "global.jdbc",

		"visible": "true",
		"connectionDetails": {
			"connectionType": "global.jdbc",
			"dialect": "org.hibernate.dialect.DerbyTenSevenDialect",
			"connectionId": "1",
			"driverClass": { 

				"reference": "derby",
				"driverClass": "org.apache.derby.jdbc.AutoloadedDriver"
			},
			"subType": "dynamicDataSource"},
		"database": {
			"name": "HIUSER",
			"catalog": "", 

			"schema": "HIUSER",
			"tables": {
				"tableList": [
					{"id": "8a28627d07d04ef096d9935f12e0c7e9",

						"name": "travel_details",
						"aliasName": "travel_details",
						"columns": {
							"column": [{
									"name": "travel_id",
								"id": "1667e095-daee-4b6a-886c-c54f28ff5658",
									"aliasName": "travel_id",
									"type": "java.lang.Integer",
								"defaultFunction": "db.generic.aggregate.sum"
								}]}}]},
			"relationships": {}, 

			"views": {
				"viewList": [
					{	"id": "8b08f2fa-7c72-426c-91ac-3654f96f1838",
						"name": "dynamic View",
						"alias": "dynamic View",
						"hasStoredProcedure": false,
				"query": {"query": "if( hasFilters())\n{\nreturn formData\n}\nelse\n{\n\nreturn \"select * from \\\"travel_details\\\"\"\n} ",
							"type": "groovy"}}]}},
		"security": {
			"createdBy": "1",
			"organization": ""
		}},
	"duplicateTableMap": {}, 
	"duplicateOriginalMap": {},

	"classifier": "db.generic",
	"queryGeneratorImplementation": "com.helicalinsight.adhoc.genericsql.GenericSqlQueryGenerator",
	"componentJson": { 

		"@class": "com.helicalinsight.adhoc.DatabaseQueryGenerator",
		"@classifier": "db.generic, db.calcite, db.workflow,  db.noSql"
	},
	"selectedColumnsMap": {
		"dynamic View_booking_platform": "dynamic View.booking_platform",
		"dynamic View_destination": "dynamic View.destination"
	}}

It returns formdata data in json format.

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

Example : return formData.location
Output :

1463377807724/1463377836985/1591703058466

This way end user can utilize required formdata information.

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

Written by

Shailaja

Related Posts