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):

1. Select Administration in the top menu and then choose Remote Queries.

2. The “Remote Queries” window will open. This window contains all remote queries.

3. Right click anywhere in the remote query list to open a context menu.

4. 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.

5. If options “Add” or “Edit” were selected, then the “Remote Query Setup” window will open.

6. Set up remote query. Refer to Remote Query Setup for more information.

7. 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):

1. Create a new feature or open an existing feature for editing from “Features” pane.

2. The “Feature Definition” window will open.

3. 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.

4. The “Remote Query Setup” window will open.

5. Set up remote query. Refer to Remote Query Setup for more information.

6. Click “Save” button.

7. At this point the new remote query was created or existing remote query was updated and one can continue setting up the feature.

Open

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

Open

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.

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.

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):

1. Open remote connection select box and choose a blank field or existing remote connection.

2. 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.

3. “Remote Connection Setup” window will open.

4. 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.

5. 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

6. 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.

Create Remote Connection from the Administration Top Menu

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

1. Select Administration in the top menu and then choose Remote Connections.

2. The “Remote Connections” window will open. This window contains all remote connections.

3. Right click anywhere in the remote connections list to open a context menu.

4. 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.

5. If options “Add” or “Edit” were selected, then the “Remote Connection Setup” window will open.

6. Set up remote connection.

7. 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.

There are several types of parameters:

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:

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.

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 &amp; instead.

• Less then (<) character is not allowed. Use &lt; instead.

• Greater then (>) character is not allowed. Use &gt; 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.

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.

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.

Remote Parameters

All parameters for remote are configured in different places (e.g. Select Statement, Setup XML, etc.) and some parameters may overlap. The relations are complex and also depends on various conditions. There are priorities defined, which are used to decide where from parameter must be taken in case they overlap. It also depends how parameters are updated based on in which mode remote is executed (e.g. Test, Default, Custom). The setup process is complex because different type remotes are configured based on context. Perfion is a flexible platform, which allows complex relations and some of configuration options are left for backwards compatibility with previous versions of Perfion.

In general, all parameters can be categorized:

• Headers only breaker. A special parameter used to control query when Perfion needs to retrieve only the headers of remote data.

• Key feature. Selection of key feature ID.

• Keys. Key values allows to select only the data which is relevant in current context. Keys are key feature parameter values.

• Variants. Values for Cluster type remotes.

• Custom parameters. Parameters with special data formatting.

• Replace parameters. Parameters without special data formatting.

• Remote type. Defining of remote type, e.g. Single or Cluster.

• Languages. Usage of system default languages.

• Localizable. Setup for localizable remote feature.

• Fields. Defines remote data columns needed for retrieval.

Remote Parameters – Headers Only Breaker

Headers only breaker parameter is a special parameter, which is used to control remote query in such way, that in Test (headers only) mode Perfion could read only remote data headers instead of full data set. For example, in Test (headers only) mode Perfion needs to know which headers (column names) remote data have, but it does not need the data. The Test (headers only) mode is executed in “Feature Definition” window and it may become slow if all data must be retrieved from the remote source when Perfion should only use the headers. This parameter is optional.

Practically it is not a requirement that remote query executed in Test (headers only) mode will return only headers, e.g. without any data. It is also OK to return some data, but it is important to make sure that data amount is limited, because it may become a performance issue when one wants to edit remote feature.

Headers only breaker parameters differ based on data source where from the data is retrieved:

• Database. Database type remote has a special headers only breaker parameter #@#. Database can also use @REPLACE parameter to control the amount of data returned for Test and Default modes.

Using #@# headers only breaker is the fastest way to get headers only remote data for Database remote. It must be used immediately after the last WHERE clause in SQL query. If SQL query does not have any WHERE clause, then it has to be added, e.g. like this: WHERE #@#.

Another way to write SQL query in order to get good performance in Test (headers only) mode is to use

@REPLACE parameter. For example, one can use “Top 1” value in Test mode and “” (empty string) in the Default mode. In this way test data will have only one line of data and default data will not be limited in any way.

Comparing #@# to @REPLACE, the first one is quicker, because it allows to retrieve only headers information from remote database, while with @REPLACE parameter remote database will return some rows of data (one in this case), which will be slower, but still comparable in performance to the other method.

• Web Service. Web Service headers only request is performed by defining Group parameter to use “None” value in Setup XML, but this is optional. Perfion does this automatically for Test (headers only) mode regardless of the Group parameter values set by user.

• OData. OData can use @REPLACE type parameter to control data amount returned from remote data source in Test mode. OData requires minimum one row of data to be retrieved from OData service in order to get header data. For example, one can use “$Top=1” value in OData query in Test mode and “” (empty string) in the Default mode. In this way test data will have only one line of data and data in Default mode will not be limited in any way. Note: in order to control parameter values in Test and Default modes one will need to specify it in Setup XML and the actual parameter value will be dependent on where in OData query it is used. E.g. it has to be set to either “?$Top=1” if the Top parameter is the first parameter in OData query or “&amp;$Top=1” if it is not.

If headers only breaker is not defined, then Perfion will use the Test (Setup) mode to execute the remote query. Perfion will select the first found key feature value defined for Test data in order to limit the data quantity returned by the remote query.

Examples of headers only breaker usage with different type remotes:

*#@REPLACE# parameter with “TOP 1” will allow returning only one data row in Test mode. This is not a requirement and one can return more data rows, but we recommend to optimize the query for performance.

**For Web Service the Test (headers only) mode will automatically use the correct Group parameter value.

Remote Parameters – Key Feature

Key feature is used to relate incoming remote data to the data in Perfion. For example, if Perfion has a product data and remote system has a product data, then the key feature will be used to relate both products. If the products are identified by product number, then key feature have to be set to a feature in Perfion, which stores product numbers. The remote data should be prepared so, that product number data will come in a data column specified as “KEYVALUE”.

Key feature can be defined by specifying the key feature ID, which is greater or equal to zero (>=0), or if defined in Setup XML then one can use the same ID value, but can also use key feature name, e.g. “ItemNumber”.

If key feature is defined using ID, then there are several ID intervals which will affect how the key feature is used.

In Perfion all feature IDs from 1 to 99 (both included) are reserved for internal use. Features created by users will get automatically assigned feature IDs, which are greater than 99. Features with IDs in range from 1 to 99 hold base values for other features. For example, a String type feature will have its value saved in another special feature with ID equal to 2, the Number type feature will use a special feature with ID equal to 1, etc.

Key feature can be configured in Select Statement and in Setup XML. In Setup XML key feature is defined as a root element attribute, e.g. “keyFeature=’ItemNumber’” or “keyFeature=’101’” (refer to Figure 10).

In Select Statement key feature is defined by using special parameter names and there are several reserved for key feature:

• #<key feature ID>#, e.g. #101#, where 101 is key feature ID. Note that one cannot use key feature name here. Only a number greater than or equal to zero (>=0) can be used here as key feature ID value.

• #@ID#. Key feature ID will be set to zero (0).

• #@KEYFEATURE#. Key feature will be defined from Setup XML.

There can be only one key feature specified in Select Statement. For example, if #@ID# was specified and also #@KEYFEATURE#, then the error will be shown.

Key feature can be defined several times in Select Statement, but then it has to be the same in all instances. For example, if #@ID# was defined once, and then again as #@ID#, then it is not an error and both will be used.

If the same parameter is used several times in Select Statement, but with different values, then the same values will always be used with all parameters. The values will be taken from the 1^st found parameter.

In Select Statement key feature definition has two purposes:

• To define key feature ID in order to know which key feature values or Item IDs have to be used in Select Statement.

• To show where key values have to be inserted into query defined in Select Statement. The key feature parameter placeholders will be replaced by the list of actual key feature values or Item IDs when Select Statement is evaluated.

Web Service type remotes do not use Select Statement, so key feature will be always taken from the Setup XML.

The table below shows various situations how key feature ID will be determined.

There are several ways to define the key feature in Select Statement because Perfion must be backwards compatible with older versions. We recommend to always use #@KEYFEATURE# parameter in Database and OData remote queries and then specify key feature ID value in Setup XML. We also recommend not to use key feature values (keys) directly in Select Statement, but instead to define them in Setup XML using key parameters.

Remote Parameters – Keys

Keys are key feature values, which are used to control remote data based on the context where from it was requested. Key values will depend on key feature ID. If key feature ID is specified as greater than zero (>0), then keys will be key feature values. If key feature ID is specified as zero (0), then Perfion will use item IDs as key values for remote and local data mapping. Item ID values depends on context. For example, if data is currently shown in the grid, then item IDs will be base feature item IDs. If the product feature is selected, then item IDs will be product feature item IDs. In Item Editor it will be ID of the item which is loaded in the editor.

Keys can be defined in Select Statement and in Setup XML. If Setup XML is used, then keys will always be used from Setup XML and key values defined in Select Statement will be ignored. Moreover, in Default and Custom modes Perfion will overwrite keys for remote feature based on where from remote feature is used.

In order to use keys for Database and OData type remotes, the Select Statement must have key feature parameter. It is used as a placeholder for key values.

The table below shows various situations and how key values will be determined. 

Remote Parameters – Variants

Variant parameters are used to configure Cluster type remotes.

For Database and OData remotes, variants can be defined in Select Statement and in Setup XML. Web Service type remotes will use only Setup XML. If Setup XML is used, then variant values will always be used from the Setup XML and values defined in Select Statement will be ignored.

There can be multiple variant parameter values defined in Select Statement and also in Setup XML. Variant values will be used in the same way in Test mode and in Default mode, but variant values can be overwritten by Perfion in Custom mode.

When using remote of Cluster type, then remote data must have a column with name “VARIANT”. Values provided in “VARIANT” data column should match values defined in remote query setup. OData remotes, for example, will filter “VARIANT” data column values based on variant values provided in remote configuration.

In case remote feature is of Single type, then variant values will be used as simple parameters.

The table below shows various situations and how variant values will be determined. 

Remote Parameters – Custom Parameters

Custom parameters are any parameters, which are defined without using one of reserved keywords used for special parameters. User can choose their own custom parameter name.

Custom parameters for OData and Database remotes can be defined in Select Statement and in Setup XML, and for Web Service remotes – only in Setup XML.

The custom parameters in Select Statement can be configured to use multiple values, but they cannot differentiate between Test and Default modes. Setup XML allows to specify custom parameters only using a single value, but it allows to differentiate between Test and Default modes and also allows preprocessing of values using their data types as provided in configuration.

If Select Statement and Setup XML are both used, then custom parameter values will be prioritized as provided in Setup XML. If Setup XML has some custom parameters, but OData and Database type remotes do not have the same parameter defined in Select Statement, then custom parameter will not be used. The similar situation is also with Web Service remotes. Even though the parameter is defined in Setup XML, it does not mean that it will be used in data retrieval. The parameter will be sent to remote web service, but it is up to remote web service implementation to decide if it has to be used or not.

The custom parameter with the same name can be reused several times in Select Statement. The values in this case will be chosen from Setup XML if it is defined. If Setup XML is not used, then values will be defined from the first found custom parameter with that name.

The table below shows various situations and how custom parameter (with name “xxx”) values will be determined.

Remote Parameters – Replace Parameters

Replace type parameters are special parameters, which may have only a single value and the name of parameter must always start with REPLACE word (@REPLACE for Select Statement and REPLACE for Setup XML). The parameter names are case insensitive, so there is no difference in between “REPLACE” and “replace”. The replace parameter is practically the same as a custom parameter. The differences:

• Replace parameter name must start with word “Replace”.

• Replace parameter is defined as string type parameter in Setup XML or without using any type (defaults to string).

• Replace parameter from Setup XML is not pre-processed using data type like custom parameter values. It uses value exactly as it was defined.

• Replace parameter is evaluated without checking if parameter value is of numeric or any other type. The value will be replaced with exactly the same values as it was defined by user.

Replace parameters are not fixed to the name “Replace”. All parameters which start using “Replace” word will be replace type parameters. Some valid replace parameter values: Replace, Replace_where_breaker, Replace_top_5, Replace1, Replace2, etc.

The replace parameter with the same name can be reused several times in Select Statement. The values in this case will be chosen from Setup XML if it is used. If Setup XML is not used, then values will be defined from the first found Replace parameter with that name.

The table below shows various situations and how replace parameter values will be determined.

Remote Parameters – Remote Type

Remote type parameter defines the type of remote data: Single or Cluster. The value of remote type is defined using “Remote Query Type” select box, however, the remote type value will also be written to Setup XML document if it is used. In any case the value in Setup XML will be always overwritten by Perfion to match the value selected in the “Remote Query Type” select box.

Remote Parameters – Languages

Languages type parameter is a special parameter, which is used to evaluate language related values. The parameter can be used to adapt OData or Database queries so that they can produce localizable type of remote data, which then can be used to create localizable remote features. Languages parameter is also used with Web Service remotes.

Languages parameter always starts with LANGUAGES word (@LANGUAGES for Select Statement and LANGUAGES for Setup XML). The parameter names are case insensitive, so there is no difference in between “LANGUAGES” and “languages”.

There are two types of Languages parameters:

• Languages. It defines only language codes as values just like any other parameter. For example it can be defined in Select Statement or in Setup XML if it is used.

• Languages(). This parameter is similar to the Languages parameter, but its purpose is to create localizable names using Languages parameter values. This parameter is used only by Database remotes and can be defined only in Select Statement.

The format of this parameter:

Where “Name” is a word, which will be localized using language code values. After Languages() parameter is evaluated, it will be replaced with comma separated list of localizable “Names”. Note: it will not use single quotes like other parameters usually do if data is not numeric. The “<Name>” part will be evaluated to “<Name>EN,<Name>DE” if Languages parameter will have two values: “EN” and “DE”, e.g. #@LANGUAGES:EN,DE#.

In order to use Languages() parameter, the parameter Languages should be also defined (in Select Statement or in Setup XML). The Languages parameter will be used to define the language code values for the Languages() parameter.

Languages() parameter if used must have at least one value defined by Languages parameter.

The primary use of Languages() parameter is to create remote data column names dynamically in SQL queries. Localizable remote data column names have format: “<ColumnName>_<LanguageCode>”.

The languages parameter with the same name can be reused several times in Select Statement. The values in this case will be chosen from the Setup XML if it is defined. If Setup XML is not used, then values will be defined from the first found Languages parameter.

The table below shows various situations and how Languages and Languages() parameter values will be determined.

Remote Parameters – Localizable

Localizable parameter is meant to tell web service that remote data will be used with localizable remote feature.

Localizable parameter is used only in Setup XML and only with Web Service remotes. It is defined in Setup XML root element attribute “localizable” and have values “true” or “false”.

Remote query in general can be used with multiple remote features and some of those features can be localizable and some may be non-localizable. The general rule is, however, that localizable features are more complex, because instead of holding only one column of data as its value, it will have one data column for each user language defined in Perfion. This is not a requirement and rather an ideal case, e.g. data columns for all languages are optional.

In order to use localizable remote feature, the remote data must fulfill some requirements. The data column names should include suffixes with language codes used in Perfion.

Where:

• ColumnName is the original column name as it is used with non-localizable remote feature.

• LanguageCode should match language code specified for user in Perfion.

The non-localizable remote feature has no such requirements and it can also be used directly with localizable type remote data. For example, there is no difference for non-localizable remote feature what is data column name. One can select “Name” (from non-localizable remote data) or “Name_EN” (from localizable remote data) and there will be no difference in how that data is handled by Perfion. However, the localizable remote feature will fail to read data properly if it will not find data column matching user defined languages in Perfion.

By specifying “localizable” parameter in Setup XML using “true” value, one will tell to remote web service that it is expected to deliver the data as specified for localizable remote data. In order to make non-localizable remote data as localizable, one has to convert all data column names into localizable equivalents and also use data in each column, which will be language dependent.

Note that the “KEYVALUE” and “VARIANT” columns are not localizable and should not be used with language code suffix like other data column names. This also means that localizable remote feature cannot use “KEYVALUE” or “VARIANT” column as their remote data field (defined from “Feature Definition” window). If “KEYVALUE” will be selected as remote data field, Perfion will show an error when the data will be used, e.g. in the grid. If “VARIANT” will be selected as remote data field, then Perfion will not show an error, but the data may be used incorrectly in Perfion application.

The same localization procedure can also be done with OData and Database type remotes, but since their queries are defined in Select Statement, so they do not need to use special parameters for this. The only requirement is that user will write the query in a way, which will result in remote data format, which match the format required to be used with localizable remote feature.

Note that OData query is rather complex and limited in what user can do with it in order to format data in desired format like it is required for localizable remote feature. It does not support data projection like it is possible with database SQL query and thus it is practically impossible to structure remote query for OData so that it can be used with localizable remote feature. The only option is to expect that remote data coming from OData service will match exactly the same format as required by Perfion, which is highly unlikely.

Remote Parameters – Fields

Field parameters defines mandatory remote data columns. 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, e.g. Perfion does not demand any particular data column (fields) to be present in the data which is returned from remote web service. If fields are defined, then web service is required to return remote data with at least data columns defined by field parameters.

Field parameters can be defined only in Setup XML and if at least one field parameter is defined, Perfion will automatically add Key and/or Variant fields (if they are not defined) based on which type of data (Single or Cluster) is used for remote. For example, if remote have Single data type, then the minimum amount of data columns required from the web service is one and it is the “KEYVALUE” column. However, if one creates a remote feature and selects another data column as remote data field, then to use such remote feature (e.g. in the grid) the minimum amount of data required will be at least two data columns: “KEYVALUE” column and the column defined by remote feature as remote data field. By using field parameters Perfion can inform the remote web service to send only the required data instead of sending everything it has.

Field parameters in Default mode will be modified by Perfion. If parameters are not defined in Setup XML (e.g. in Default section) they will be automatically added, and if field parameters are defined by user, then Perfion will add extra parameters if they are necessary.

The purpose of field parameters is to allow better performance when using remote web service. It allows web service to dynamically handle remotes and exclude the data Perfion does not need. There is no reason to send 20 data fields each time when Perfion needs only two or three data fields. It is, however, up to web service implementation to support field parameters or not. Perfion will report about which data it requires and then the web service can send all data, but it also can send only required data if performance is important.

Perfion uses field parameters when, for example, several remote features are used in Default mode, e.g. in the grid. If those remote features are defined using the same remote query, then Perfion will send only one request to remote web service to get data for all remote features at once instead of sending one data request for each remote feature. Each remote feature will use “KEYVALUE” column for key values and in some cases one extra data column as specified by selected remote data field (in “Feature Definition” window). Perfion in this case will send “KEYVALUE” as a key for all remote features and then will combine remote data field values from each remote feature as extra field parameters.

Example: let’s say the two remote features described above have following data:

• Remote feature “A” will use “KEYVALUE” data column and “DataA” data column

• Remote feature “B” will use “KEYVALUE” data column and “DataB” data column

Then Perfion will send one data request to remote web service and will automatically add these field parameters: “KEYVALUE”, “DataA”, “DataB”.

Examples how field parameters are used are shown in the table below.

Remote Feature

Remote feature is very similar to all other features in Perfion, but remote feature retrieves data from remote data source in real time instead of reading it from local database. Remote feature differences from normal feature:

• Remote feature data is read live from remote data source every time the remote feature is used.

• Remote feature data has read only attributes, e.g. its values cannot be modified.

• Remote feature can be configured using parameters, e.g. from Report Designer.

• Remote feature must be setup to use remote query and remote data field (in “Feature Definition” window).

• Remote feature data will be shown in the grid and Item Editor in light blue background to indicate that it is data from remote feature.

• Remote feature icon will be shown with a globe indicator in “Features” pane, e.g. .

• Remote feature support only some of feature data properties as shown in the table below.

Create a New Remote Feature

A new remote feature can be created from “Features” pane by opening an existing feature for editing or by creating a new one. The procedure (refer to Figure 12):

1. Create a new feature or open existing feature for editing. It can be done from “Features” pane.

2. The “Feature Definition” window will open.

3. Add or modify any feature properties like you would normally do for any feature.

4. Add or modify feature data properties. Note: remote feature can be only specified with limited number of data properties (refer to Remote Feature).

5. Select existing or create a new remote query.

6. After selecting remote query, it will be executed to get all available remote data fields, which then will be presented in “Data Field” select box.

7. Select “Data Field” value. Even though the remote query allows retrieving multiple data fields at the same time, the remote feature can use only one data field. If you need to use other data fields from the same query, then you can create more remote features where each will reuse the same remote query, but will use different data fields.

8. Click “OK” button.

When opening “Feature Definition” window for remote feature, the remote query will be executed in Test (headers only) mode to get remote data column names (data headers), which can be selected by user in remote “Data Field” select box. If “Feature Definition” window for remote feature takes a lot of time to open, then it could be that remote query takes a lot of time retrieving remote data. We recommend to set up remote query so, that in Test mode it will be tuned for performance.

How to Use Remote Feature

Remote feature can be used almost in the same way as any other feature in Perfion. One can add it to the grid, use with Report Designer or via base feature configuration add it to another feature and then show data in the Item Editor.