Remote Query

Remote query defines how data will be retrieved from remote data source and how the remote will be used in Perfion. All remotes must have remote query.

Create New or Edit Existing Remote Query

There are two ways to create new or edit existing remote query: from the Administration top menu or from the “Feature Definition” window.

Create Remote Query from the Administration Top Menu

This is a standard way to create or edit any type of remote query. The procedure (refer to Figure 6):

Select Administration in the top menu and then choose Remote Queries.
The “Remote Queries” window will open. This window contains all remote queries.
Right click anywhere in the remote query list to open a context menu.
From the context menu select to create a new remote query or edit the selected remote query. In order to edit existing remote query one can also double click on selected remote query.
If options “Add” or “Edit” were selected, then the “Remote Query Setup” window will open.
Set up remote query. Refer to Remote Query Setup for more information.
Click “Save” and then close the “Remote Queries” window.

Create Remote Query from the “Feature Definition” Window

Remote query can also be created directly from “Feature Definition” window when assigning remote query to a feature in order to make it a remote feature. The procedure (refer to Figure 7):

Create a new feature or open an existing feature for editing from “Features” pane.
The “Feature Definition” window will open.
In the “Feature Definition” tab “Remote” area “Query” select box shows the selected remote query which is used with this feature. Here one can select an existing remote query and press “Edit” button or select an empty option and then press “New” button.
The “Remote Query Setup” window will open.
Set up remote query. Refer to Remote Query Setup for more information.
Click “Save” button.
At this point the new remote query was created or existing remote query was updated and one can continue setting up the feature.

Figure 6: Create or edit remote query from the Administration top menu

Figure 7: Create or edit remote query from “Feature Definition” window

Test, Default and Custom Modes

The remote can be used in four different modes which control where and how in Perfion they will be used.

Mode	Usage	Description
Test (setup)	“Remote Query Setup” window	Test mode is used in order to test remote if it works using test parameters. It is recommended to set test mode so, that it will return only small amounts of data from remote source. This mode is activated when the “Test” button is pressed in “Remote Query Setup” window.
Test (Headers only)	Remote feature data field selection (in “Feature Definition” window)	It uses test data like in Test (setup) mode, but the purpose of it is to retrieve only headers information from remote data. It is used from “Feature Definition” window when selecting remote data field.
Default	Grid, “Item Editor” window Report Designer, etc.	Default mode is used everywhere in Perfion except two cases as described in Test (setup) and Test (headers only) modes.
Custom	Report Designer etc.	This mode is the same as Default mode, but with custom parameters. In some cases in Perfion where remotes are used (e.g. Report Designer) it is possible to overwrite some of parameters for remote with custom parameter values. In this case the parameters specified in Select Statement and/or Setup XML will be overwritten with those custom parameter values coming e.g. from the Report Designer. For more information how to use such parameter refer to the manual “Perfion Parameters”.

Remote Query Setup

All remote query configurations are done from “Remote Query Setup” window. Overview of parameters (refer to Figure 8):

Name. Remote query name.
Remote Connection. Connection which is used to get access to remote data. Remote connection specifies all parameters needed in order to connect to remote data source. It will determine which type of remote data source will be used (Database, OData or Web Service) which will also affect the layout of Remote Query Setup window. Note that Web Service type remotes do not have Select Statement window and requires Setup XML data, while remotes which have other data sources (Database or OData) use Select Statement and Setup XML is optional.
Select Statement. In this window one can specify SQL query (for Database remotes) or OData query (for OData remotes). Web Service remotes do not use this window.
Remote Query Type. Remote query type selector which allows to define if remote query will be of Single or Cluster type.
Setup XML. XML document, which allows specifying various parameters for remote query.
Test data preview window. This window is used to show preview of remote data returned for remote query when using test mode. It is hidden at first and is shown only after executing data retrieval test which can be done by pressing the “Test” button or F5 button on the keyboard.
Setup XML template. This is a button which when pressed will insert the template of fully configured Setup XML document with all parameter types.

Figure 8: Remote Query Setup

Query Name

Remote query name is unique name to identify the remote query. We recommend to specify a name, which would explain what type of remote query it is, e.g. what type of remote data it will retrieve.

Remote Connection

Remote connection will specify all parameters needed in order to connect to remote data source. One can select one of available remote connections or create a new one.

To create a new or to edit existing remote connection (refer to Figure 8):

Open remote connection select box and choose a blank field or existing remote connection.
If the existing remote connection is chosen, then press “Edit” button to modify and if the blank field was selected, then press the “New” button to create a new remote connection.
“Remote Connection Setup” window will open.
Edit or define a new remote connection name. The name should be unique and we recommend to use names, which describe the connection. Note that the same connection can be use not just for remote queries, but also in other places in Perfion, e.g. Actions and in such case the remote connection name will be used as identifier for remote connection.
Specify the remote connection information in Connection Info text box. The remote connection type will be determined based on how the data was specified. Refer to Remote Connection Type.
1. If it is an URL, then the remote connection will be of Web Service type.
2. If the data starts with “Provider="OData"”, then the remote connection will be of OData type.
3. If none of the above were detected, then remote connection will be of Database type.
4. If no data is provided
Click “Save”.

The type of remote connection will be defined based on remote connection type. If, for example, one uses web service URL as connection, then remote will become web service type remote. Connections for different data sources are specified using different specifications and Perfion will detect which one is used to define the remote type. Refer to remote query setups for different data sources to see remote connection specifications.

Remote Connection Type

Remote connection type will be determined based on what data is provided in “Connection Info” field in Remote Connection Setup window.

If it is an URL, then the remote connection will be of Web Service type.
If the text starts with “Provider="OData"”, then the remote connection will be of OData type.
If none of the above were detected, then remote connection will be of Database type.
If the “Connection Info” field is left empty, then it will be a Database connection to Perfion database.

Remote Connection Templates

To make it easier to define the remote connection one can click the right mouse button inside the Connection Info field to activate the context menu from which one can select various connection templates. The templates are predefined to reflect various supported types of configurations which could be used in Perfion. The template after it is inserted has to be updated by replacing placeholder fields (which start with “<” and end with “>”) with actual data. Refer to Figure 9.

Figure 9: Remote Connection Setup window - templates

Create Remote Connection from the Administration Top Menu

One can create a new remote connection from the Administration top menu. The procedure:

Select Administration in the top menu and then choose Remote Connections.
The “Remote Connections” window will open. This window contains all remote connections.
Right click anywhere in the remote connections list to open a context menu.
From the context menu select to create a new remote connection or to edit the selected remote connection. In order to edit existing remote connection one can also double click on selected remote connection.
If options “Add” or “Edit” were selected, then the “Remote Connection Setup” window will open.
Set up remote connection.
Click “Save” and then close the “Remote Connections” window.

Remote Query Type

The select box controls the type of data Perfion expects from remote data source. If the “Cluster” type is selected, then remote query will be expected to use variant data.

The expected remote type can be also defined in the Setup XML using the attribute “type”. However, the information provided in Setup XML is optional and is only used to support special cases, e.g. when using Web Service type remotes. In any case the select box will have priority and in case the types do not match, Perfion will overwrite the value in Setup XML to have the same value as defined in “Remote Query Type” select box.

Select Statement

Select statement window allows to define the query to be used for data retrieval. For database remotes one can define SQL query in this window, while for OData remote – OData query.

The Select Statement window is not used for Web Service remotes, because all the configurations are made in the Setup XML. The Select Statement window will be automatically hidden after a Web Service type remote connection is chosen.

The query defined in this window in most cases will be a standard query with an extra Perfion controls added to make query usable in different scenarios. For example, in order to use the same query in the grid and in Item Editor, the different amounts of data have to be retrieved using the same query. To control the amount of data, Perfion needs to know which data columns from remote are the key columns, which data columns are the variant columns, etc.

Users can also specify extra parameters, which allows controlling retrieved data more precisely for each scenario where data may be used in Perfion application. For example, users can specify available languages as a custom parameter, so data can be customized to retrieve data based on e.g. languages to create localizable remote feature, which returns data in several languages per each item.

Select Statement – Parameters

Select statement parameters can be of various types, but the rules are almost the same for all type of parameters. The differences will be only in how they are used, e.g. which keywords are used to distinguish one from another.

The parameter format

#<parameter>[:<parameter values>]#

There are several types of parameters:

Type	Format	Example	Description
Key feature	#<Key feature ID>#	#101#	This is a special parameter to detect key feature ID. In this case the parameter does not have name, but instead integer number >= 0. In example, key feature ID is equal to 101. This type of parameter is allowed only once in select statement, unless exactly the same parameter (with the same key feature ID value) is repeated multiple times.
Variable	#@<parameter name>#	#@ID#	This is a normal parameter. The parameter name can be chosen by user, but there are also some parameter names, which are reserved by Perfion for special use. Reserved keywords: <no name> - Headers only breaker ID – Key feature KEYFEATURE – Key feature LANGUAGES – System languages LANGUAGES() – System languages (a special case) VARIANT – Variant parameter REPLACE – Replace parameters. All parameters, which starts with “REPLACE” falls into this category. Note that parameter names are case insensitive.
Perfion.In	Perfion.In(<query parameter name>, #<parameter name>#) or Perfion.In(<query parameter name>, <parameter values>)	Perfion.In(ID,#@ID:2,3#) or Perfion.In(ID,1,2,3) or Perfion.In(ID,’1’,’2’,’3’)	The parameter is used only with OData query. Perfion.In is like a function, which takes two parameters as arguments. The first one (query parameter name) is used to identify entity name in OData source data and the second one is Perfion parameter or comma separated list of parameter values. Refer to Perfion.In Block for more information.

Parameter values are optional. If parameter values are defined, then the values will be valid for all modes (Test, Default and Custom), but some values can be automatically overwritten by Perfion or by configuration, e.g. by Report Designer in Custom mode. Moreover, if Setup XML is defined and has parameter with the same name as in Select Statement, then parameter values from Setup XML will be used instead (in most cases, but there are exceptions, e.g. with key feature).

Parameter values can be of several types:

Parameter value type	Parameters	Example	After evaluation (Test mode)	Description
No value	#@#	#@:1,2,3#	1=0 or 1=1	All parameters may be used without values, but headers only breaker parameter is not considered as a real parameter and has no values. The values can be defined, but they will be ignored.
No value	#@LANGUAGES()#	#@LANGUAGES(Name_)#	Name_EN,Name_DE	This parameter values will be ignored if defined. The parameter is special, because it will use multiple values, but these values will come from @LANGUAGES parameter instead.
Single value	#@REPLACE#	#@REPLACE:1,2,3#	1,2,3	Most parameters can be specified with a single value, but only @REPLACE type parameter can be specified with a single value only. Everything defined after the colon (:) will be treated as a single value.
Multiple values	All other	#@ID:1,2,3#	1,2,3	All parameters, which are not as defined by “No value” or “Single value” may have multiple values. Values are comma separated and can be used with single quotes or without. All parameter values after processing will be escaped with single quotes if at least one value is not of numeric type.
		#@ID:1,2,3a#	‘1’,’2’,’3a’
		#@ID:’1’,’2’,’3’#	‘1’,’2’,’3’

Perfion parameters in Select Statement are just placeholders, which will be replaced with actual values when Select Statement is evaluated. All Select Statement parameters with multiple values will become a list of comma separated values after evaluation. The table below shows examples of various cases how parameters are defined and how they will look after evaluation when used in different modes. The values are shown taking into account that Setup XML is not used, e.g. not defined or if defined, then has no matching values.

Mode	Before evaluation	After evaluation	Description
Test	#101# #@ID# #@KEYFEATURE# #@VARIANT# #@LANGUAGES# #@LANGUAGES()# #@REPLACE# #@CustomParam#	<empty string>	Test mode will use only manually defined values regardless of parameter type. In this case there are none. NOTE: @CustomParam is user defined parameter with any name, which is not reserved.
	#102:1,2# #@ID:1,2# #@KEYFEATURE:1,2# #@VARIANT:1,2# #@LANGUAGES:1,2# #@CustomParam:1,2#	1,2	Manually defined values will be added using formatting, e.g. using single quotes around each parameter value if at least one parameter is not of numeric type.
	#@LANGUAGES(Name_):1,2#	Name_EN,Name_D E	Manually defined values are not used and will be ignored. The values will be taken from another parameter - @LANGUAGES instead. In the example it shows evaluated values in case @LANGUAGES parameter is specified to use EN and DE values.
	#@REPLACE:”1,2”#	“1,2”	Manually defined values will be added exactly as they are defined, e.g. with no pre-processing or changing formatting.
Default	#101# #@ID# #@KEYFEATURE#	<Comma separated key values e.g. from the grid>	Default mode for key feature will always overwrite manually defined key values with values coming from Perfion.
	#102:1,2# #@ID:1,2# #@KEYFEATURE:1,2#	<Comma separated key values e.g. from the grid>	Default mode for key feature will always overwrite manually defined key values with values coming from Perfion.
	#@VARIANT:1,2# #@CustomParam:1,2#	1,2	Variant and any other parameter type will always have only manually defined values.
Custom	#@VARIANT:1,2# #@CustomParam:1,2#	1,2 or <Comma separated values e.g. from Report Designer>	The same as in Default mode, but if remote feature is used from some place which has possibility to define custom parameters, then manually defined values can be overwritten. For example, this can be done from the Report Designer.
Default	#@LANGUAGES# #@LANGUAGES:1,2#	‘EN’,‘DE’,‘DAN’	Perfion will replace parameter with user language codes as they are specified in Perfion.
Custom	#@LANGUAGES# #@LANGUAGES:1,2#	‘EN’,‘DE’,‘DAN’ or <Comma separated values e.g. from Report Designer>	The same as above, but if remote feature is used from some place which has possibility to define custom parameters, then manually defined values can be overwritten. For example, this can be done from the Report Designer.
Test	Perfion.In(ID,#@ID:2,3 #)	(ID eq 2 or ID eq 3) (ID eq ‘2’ or ID eq ‘3’)	If the second argument will be used as parameter (e.g. #@ID:2,3#), then it will be evaluated 1^st and only then evaluated values will be used in “Perfion.In” block. “Perfion.In” block will process data values based on their type as defined in OData metadata. For example, the 1^st result will be produced if ID in OData metadata is defined as numeric and the 2^nd result will be produced if it is defined as text/string. Refer to Perfion.In Block – Data Formatting for more information.
Test	Perfion.In(ID,2,3)	(ID eq 2 or ID eq 3) (ID eq ‘2’ or ID eq ‘3’)	As above, but the second argument is defined directly, so it does not need an extra evaluation step like it was required in the previous example.
Test ( Headers only)	#@#	1=0	Headers only breaker parameter has no values and will be always replaced with the same value.
Test (setup) Default Custom	#@#	1=1	Same as above, but will be replaced with another value in all the other modes.

The table shows how parameters are evaluated in absence of Setup XML document. To see how each parameter will behave when Setup XML document is present refer to Remote Parameters and then follow from there based on parameter type.

Note that it is allowed to specify the same parameter multiple times in the same Select Statement, but then, manually defined values will be used as specified in the 1^st found instance of those parameters. For example, if #@ID:1,2# is defined 1^st, and #@ID:3,4# is defined 2^nd, then both will become as the 1^st one, e.g. #@ID:1,2#.

Setup XML

Setup XML allows to configure most of parameters which can be defined in Select Statement and several more. It also allows configuring parameter values based on mode, e.g. Test or Default. Setup XML is also the only way to configure Web Service type remotes, because Select Statement is not used with Web Service remotes.

When creating a new remote query the Setup XML is blank, but one can insert the template of Setup XML document by pressing the button “Setup XML” over Setup XML window. The template has most types of parameters predefined so it would easier to select those which may be required for remote query configuration. After inserting the template one can remote unwanted parameters and update those which will be used for remote query configuration. Note also that the Setup XML document is a standard XML document and one can comment out the unwanted parameters, but one must also use parameters so that XML format is valid, e.g. without specifying any illegal characters. For example, the following characters are not allowed in XML documents and must be escaped:

Ampersand (&) character is not allowed. Use & instead.
Less then (<) character is not allowed. Use < instead.
Greater then (>) character is not allowed. Use > instead.

Setup XML document allows specifying some parameters, which can also be specified directly from Select Statement. In case there are some parameters which are the same in Select Statement and in Setup XML, then Setup XML in most cases will have priority and will overwrite any values defined in Select Statement. Therefore, it is recommended to place parameters in Select Statement without predefining in-line values and to use Setup XML document to define the values for those parameters.

The Setup XML document is mandatory for Web Service type remotes. However, we recommend to use it with all remotes. It has more features and it also simplifies Select Statement configuration.

Note that parameter names in Select Statement and in Setup XML document are case insensitive and will match if parameter name is the same, but in different letter casing.

Moreover, parameters in Select Statement are identified with @ prefix, but Setup XML does not use prefixes and only actual parameter name will be used.

Setup XML – Structure

Setup XML consist of three parts (refer to Figure 10):

Metadata. The root element of setup XML has five attributes:
- “name”. Setup XML document name. It does not have any special meaning in Perfion, but can be used by Web Service to identify remote query.
- “keyFeature”. Key feature ID. Defines key feature for remote.
- “type”. Remote query type. Defines remote query type, e.g. Single or Cluster.
- “localizable”. Localization status. Defines the preferred remote data output from localization perspective. If set to “true” it tells to Web Service that remote query expects data, which will be used with localizable remote feature. Used only with Web Service remotes.
- “timeout”. Timeout in seconds. Only integer values >=0 are accepted. Defines the timeout for remote. If it is defined, then it will overwrite the default value, which is 30 seconds.

Parameter value usage depends on remote data source:

Database. The value will be used as “CommandTimeout” parameter, which controls the wait time before terminating the attempt to execute a command (SQL query). Database connection string configured in Remote Connection Setup window may have another timeout parameter which controls the connection timeout. This one is different and controls the SQL query execution timeout.
OData. This value will be used as request timeout.
Web Service. This value will be used as request timeout.
Test setup. All parameters specified in element “<Test>” will be used for tests of remote, e.g. used in “Test (headers only)” and “Test (setup)” modes.
Default setup. All parameters specified in element “<Default>” will be used in “Default: and/or “Custom” modes.

Figure 10: Setup XML structure

Setup XML – Parameters

One can define many types of parameters in Setup XML and these parameters are defined in the same way in Test and Default modes. Refer to Figure 11 which shows the fully configured Test block. The Default mode can use the same parameters.

In order to define any parameters we recommend inserting the template Setup XML document (Setup XML button above the Select XML window) and then modifying the parameters based on application requirements.

Figure 11: Setup XML - Test mode parameters

The table below shows examples of various cases how parameters are defined, what they mean and how they will be handled in different modes. For examples of how each parameter is defined in Setup XML refer to Figure 11.

Parameter	Location	Description
Group	Test Default	Headers only breaker parameter and it can also be used to select data by some grouping category. It is used only with Web Service remotes. Values: “All” and “None” are reserved by Perfion, but custom values can also be used if Web Service supports it. “None” value means that only headers are requested from remote data source and “All” means that it must return all data. If parameter is not defined, Perfion will use “All” as default value.
Key	Test	Key values for key feature.
Key	Default	Key values for key feature. Perfion will overwrite key values, which will be taken from Perfion depending on the context where from the remote feature is used. For example, if remote feature is shown in the grid, then all key feature values shown in the grid will be used as key values.
Field	Test	Fields defines remote data columns needed for retrieval. These parameters are used only with Web Service remotes and are optional. By default, if they are not defined, then all available remote data should be returned. If fields are defined, then Web Service is supposed to return only remote data columns specified by field parameter values.
Field	Default	Field parameters will be modified by Perfion. It will add required field parameters based on the context if they are not manually defined by user.
Variant	Test Default	Variant parameter values will be used based on what is the type of the remote, e.g. Single or Cluster type. For Cluster type remote Variant parameter will work like custom string type parameters, but it will use multiple values instead of a single value. For Single type remote Variant parameter will not be used by Perfion for special data handling. However, Variant parameter will work like a custom multi-value parameters and can still be used. However, we do not recommend using it in this way and instead use other type of parameters.
Languages	Test	Allows to specify language codes. The primary intention is to allow creating remote queries, which can deliver data for localizable remote feature. Localizable remote features will have multiple data columns instead of one, e.g. one data column for each language. Languages parameter is mandatory if remote is specified as localizable (localizable attribute is set to “true” in Setup XML).
Languages	Default	As above, but here Perfion will overwrite all languages based on the context, where data is used from. If remote feature is used in the grid, then it will overwrite the languages parameter using default user language. If remote feature is used from the Item Editor, then Perfion may use all languages defined for the user.
Custom parameter	Test Default	Custom parameter allows defining a single value of chosen type. One can define custom parameters of five types: Number, String, Bool, Date and GUID. The type names are self-explanatory. Refer to Figure 11 for examples of custom parameters defined with each supported data type. Note that one can skip the type definition if it is a String type parameter. Parameters will be used as any other parameters, but before usage Perfion will try to parse the value and will convert it to the type as it was defined in parameter “datatype” attribute. String type parameters will be used as defined, e.g. without conversion. Note: “Number” and “Date” parameter conversion and usage depends on Windows OS culture settings on computer where Perfion application is running. For example, if “Number” parameter value is defined as “1.5” and Windows OS culture is set to Danish, then “1.5” value will be converted to “1,5” value (e.g. with comma).
Replace parameter	Test Default	The same rules apply as for Custom parameter. The only difference is that this parameter must be defined as String type parameter (or without using any data type definition, e.g. without “datatype” attribute) and its name must start with “Replace”. Setup XML parameters are case insensitive, so letter casing is not important.