=dynamicvalue(); function

Outline

The =dynamicvalue(); Scripting Engine function retrieves a value from a datatable defined in DataCentral. The value will default be cached in session to increase performance, but the developer can optionally set the "live" boolean property to tru and force live database query every time the expression is executed (performance penalty!)

Attn:
This function should never be used referencing heavy datatables or in rendering loops, for example in datarows, with the "live" property set to true!

This function will only be able to reference exactly one of the first 25 rows retrieved from a datatable definition. Best practice is to use filters which narrows down the resultset to only a few rows.

Aliases

=dynamicvalue();
=dval();

Properties

The dynamicvalue function has complex input

Property Datatype Default value Description
dtid GUID [REQUIRED] The uniqueidentifier of the datatable. This is a GUID-datatype reference to the datatable defined in DataCentral
cname String 0 Column name or ordinal (zero-based) of column you want to retrieve the value from
ridx Integer 0

Row index (zero-based) of the row you want to retrieve the value from. Therefore the first row is "ridx":"0".

ATTN:
New from version 4.2:
By providing a negative row index, the function will return the calculated result based on the existing BA calculations:
-1:  SUM
-2:  MIN
-3:  MAX
-4:  AVG
-5:  COUNT
-6:  COUNT BIG
-7:  COUNT DISTINCT
-8:  STDEV
-9:  STDEVP
-10:  VAR
-11:  VARP
-13:  SUM DISTINCT
live Boolean false Controls if the value should be cached or not. When this property is unset, it defaults to false and the value will be cached in the users session. If set to true, the query will be executed every time the expression is parsed
rfilters List(Of filter) []

List of column/value pairs to filter the query. The filters has to be formatted as a JSON array containing the filters:

rfilters: [
   { "fcol": "filterColA", "fval": "34" },
   { "fcol": "filterColC", "fval": "test" },
]

Examples

=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4");
=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4", "cname": "colA");
=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4", "cname": "colA", "ridx": "2");
=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4", "cname": "colA", "ridx": "2","live":"true");
=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4", "cname": "colA", "ridx": "0","live":"true","rfilters":[{"fcol":"colB","fval":"4"}]);

//Scripting engine expression as argument in an other exprssion:
=dval("dtid": "f2996ada-6d07-493b-b01c-88fe36c288b4", "cname": "colA", "ridx": "0","live":"true","rfilters":[{"fcol":"colB","fval":"=sv(my_system_variable);"}]);
Tags: 
Scripting Engine