Let us look at the various steps that you can add onto the editor. Following are the primary steps with their behavior and properties explained.
Data Source step
The Data Source step brings data into the Query Object.
You can bring data into the Query Object from:
- RDBMS source using SQL or stored procedure
- File source
- Web service
- Another Query Object
- Big Data / Non-relational databases
There can be any number of Data Source steps in a Query Object.
Each Data Source step has the following properties to set.
Properties
Property | Values | Comments |
New Source/
Existing QO |
New Source/
Existing QO |
You choose whether a new SQL or file source is to be created or you can use an existing Query Object |
Connection | Select from available list or Select (Parent) | When selected (Parent), then this Data Source fetches data from the connection specified in the Connections option present at the top left options bar (Refer Figure 2) or falls back to default connection configured for the user.
When a connection name is selected, then this Data Source fetches data only from that connection, irrespective of Query Object connection or connection of other Data Source in this Query Object |
SQL | A Complete SQL statement designed with the help of SQL Wizard | This property is visible when you select a connection that points to an RDBMS.
The SQL Designer window opens for you to design an SQL or procedure. See the below section for SQL Designer details |
File/Stream Source | File name, File Type and parsing details designed with help of File Selector Wizard | This property is visible when you select a connection that points to a file system.
The File Selector window opens to specify file name, file type and parsing details |
Web Service | Web Service Name, Port, Method and Record Pattern details designed with help of Web Service Selector | This property is visible when you select a connection that points to a Web Service.
The Web Service Selector window opens to specify web service, port, method and record pattern |
Existing QO | Select an existing Query Object | The output of the select Query Object becomes input of this Data Source |
Sorted | Check/Uncheck | Check = You specify if this data is already sorted on one or more fields.
This flag helps in optimizing sort dependent steps or processes later in the data flow |
Fields | Lists all fields available in this Data Source | The fields along with Field Properties can be set as discussed later |
SQL Designer
This wizard allows you to design SQLs by dragging and dropping tables on the Design tab or by typing the complete SQL on the Edit tab.
Figure 3: SQL Designer
Note: You can see the schema of database of selected connection for which metadata caching has been done.
The SQL Designer’s left pane lists Entities and attributes in the selected entity. The area on the right side is divided into following tabs:
- Design: Use this tab to design relatively simpler queries using drag and drop method.
- Edit: SQL designed on Design Tab is represented as an SQL statement on this tab. You can also write or paste SQL here.
- Result: Rows received as a result of SQL execution are displayed here.
- Sort: To specify sorting preferences.
- Filters: Set runtime filter criteria on this tab.
- Procedure: This tab is brought in front when procedure is selected. Specify procedure related details on this tab.
Note: Use Sort tab and Filter tab only from Web Studio. For SQL used in a Query Object, users will specify Sort and Filter on Ad hoc Report Designer/ Smart View.
Getting a list of database objects
The top line indicates the database connection that will be used to get database objects list.
- Select any of the objects among Table, View, Procedure and Synonym.
- Entities available on selected connection are listed. At a time, the list displays a pre-set number of entities. If the entity you are looking for is not available in the list, use the drop-down box options to view another set of entities. To get next list, click Next link. To get previous list, click Prev link.
Date and time when metadata of the selected entity was refreshed appears on the top of the SQL Designer.
SQL Designer: Design
Simple SQL Queries can be designed using Design Tab.
To add an entity in Select clause and From clause:
- Select Table, Procedure, View or Synonym. List of entities of selected item appears.
- Drag the entity you want from the Entity list and drop it in design area (blank area on the right).
It appears as a box in design area. Entity name also appears in Select clause and From clause. Repeat the above steps to select multiple entities.
Note: Instead of drag and drop, you can also double-click an entity. You can also use parameters as part of the SQL. To do this, drag [PARAMETERS] to design area. Parameter box appears in different color.
To re-position attribute list of an entity on design area:
Click the title bar of the box and drag to re-position it where you wish on design area.
To join two entities:
Drag the field / attribute from one entity list and drop it on the field / attribute on another entity.
An arrow will appear connecting two attributes of “From” entity and “To” entity. Select, From and Where clause will also change accordingly.
To delete a join:
Right-click the arrow representing that join. The Delete Link option will appear. Click Delete Link option.
Join will be deleted and will be removed from Where clause.
Working with Attributes
- An attribute from any entity on design area can be dragged and dropped at right place in Select clause, Where clause, Group By and Order By clause.
- You can also check or clear the attribute to include it and remove it from Select clause respectively.
Where clause
To get a row at the top:
Click the in the leftmost cell of column header. This inserts a row below column header.
To get a row below current row:
Click in the row below which you want to add a row for condition. A row will be inserted in the row below the respective row.
To remove a condition:
Click button in respective condition row.
To specify a where clause
- Form a condition by selecting operand1, operand2 and operator.
- To join conditions, form two conditions and select a relation in rightmost column of the first condition (of the two being joined).
- To group conditions, specify opening and closing braces.
Note: You can also drag an attribute from design area to be used as operand1 or operand2.
Group By
In the Group By clause you can provide grouping criteria for the SQL statement. To place an entity in Group By, click the entity in the Entity List and drag it in the box below Group By.
Note: Do include appropriate summary function in select clause so that it can be used in Having clause.
Having
The procedure to complete Having clause is the same as that of Where clause.
Note: Do include appropriate summary function in select clause so that it can be used in Having clause.
Order By
In the Order By clause you can provide sorting (ascending/ descending) criteria for the SQL statement. For a report with grouping, the order by clause must have the columns in the same order as of the respective sections in the Layout Editor.
Note: You can also drag an attribute from design area.
SQL Designer: Edit
On Edit tab, you can view and write complex SQL statements that cannot be defined using the Design tab. From Design tab, when you come to Edit tab, the SQL in the Design tab is constructed and displayed as a complete SQL statement.
You can also write dynamic SQL Queries on Edit tab. Refer below section to know how to write dynamic SQL Query.
The SQL statement in the Edit tab will be used as the final SQL for compilation.
From Edit tab, if you switch over to Design tab, make changes and get back to Edit tab, you will be prompted with a dialog. This is to prevent a complex query (defined in the Edit tab) from being over written when from Edit tab you switch over to the Design tab, make some changes, and return to Edit tab.
On that dialog, select OK to reconstruct SQL designed on Design tab and replace existing SQL on Edit tab. Select Cancel to retain the SQL on Edit tab and use it as the final SQL.
SQL Designer: Result
When you select the Result tab, the defined SQL statement will be compiled. Record-set (Default: 50) will be displayed in case of successful completion. If there was any error, same tab will display the error. This would help you in finding the exact location of error(s) and rectify them.
SQL Designer: Sort
- Prompt: Select this check box to get sort dialog box at report-run time. User will be able to provide sorting options on the dialog box.
- Count: Select the number of sort levels to be made available to user. For example, if you want to provide three sort choices at run time (e.g. by Country, State and county), select 3.
- Disable Forced Sorting: If due to any reason, result-set received from data-source is not sorted as per need (like groping), Intellicus server will sort the data. Check this checkbox to stop Intellicus from doing so.
Important: Data sorting performed by Intellicus becomes significantly time-intensive process when a record-set has large volume of records. In that case, we recommend not to Prompt and check Disable Forced Sorting. This will make sure Intellicus uses data as it is received from data-source.
Under ‘Select Sort Fields’, the fields listed in Available Fields are the fields that will come from database. The fields listed in Selected Fields will be available for sorting at run time.
- To move a field from Available Fields list to Selected Fields list, select a field from Available Fields list and click >
- Click >> to move all the fields to Selected Fields list
- To move a field from Selected Fields list to Available Fields list, select a field from Selected Fields list and click <
- Click << to remove all the fields from Selected Fields list
Qualifier: When SQL is typed in Edit tab, fields may not be prefixed with table name and so, Available Fields list also don’t have table names prefixed. Select a field and specify table name to associate it with a table. This is especially useful when you get the same field name from two tables and wish to differentiate one from another.
Based on the number selected in Count check box, that many dropdown boxes will appear on an input form at the run time. You can select fields that should appear by default along with default order (ascending or descending).
In Default Fields select the field, and the sort order. Click button to delete that row.
SQL Designer: Filters
At report run time, a user may wish to receive the data that makes sense to him/her at that time. On this tab, you can set filters so that user can provide filter values at report run time.
To add filters:
Select a field from Available Fields. Click > button to move the fields to Selected Fields list. At report run time, Ad hoc tab of Input Parameter Form will have 3 rows having all the selected fields as dropdown. Use >> to move all the fields to Selected Fields list.
To un-select a field, click the field from Selected Fields list and click < button. Click << button to empty Selected Fields list.
- Qualifier: When SQL is typed in Edit tab, fields may not be prefixed with table name and so, Available Fields list also don’t have table names prefixed. Select a field and specify table name to associate it with a table. This is especially useful when you get the same field name from two tables and wish to differentiate one from another.
- Max Rows: The maximum number of records to be fetched from the database. Number specified here would appear by default on input parameter form at run time. User can change it if required at run time.
- Suppress Duplicates: Check this checkbox to get only one record if record has multiple records having completely duplicate values.
To get a row at the top:
Click the in the leftmost cell of column header. This inserts a row below the column header.
To get a row below current row:
Click in the row below which you want to add a row for condition. A row will be inserted in the row below the respective row.
To remove a condition:
Click button in respective condition row.
Specify / select values the selected field from other columns. Details are provided after these steps.
- FieldName: The field name for which default value(s) is being set.
- Type: Select Ad hoc to use the fieldName for accepting the value. Select UserParameter get value using a pre-set user parameter.
- Data Type: Specify data type of the filter parameter.
- Parameter: If Type is UserParameter, specify the parameter name.
- FetchData: Select this check box for the field if filter type is Ad hoc and you want data should be fetched from the database. In this case value box for this field will appear as a drop-down box.
- Criteria: Select criteria for the field.
- Value1, Value2: Based on selected criteria, specify Value1 or Value1 and Value2. For example, for “Between” as criteria, you need to provide two values.
- Mandatory: Select this checkbox if it is mandatory for user to provide value for this field at runtime.
SQL Designer: Procedure
To set procedure and provide value of parameters if any:
- Click Procedure tab to bring it in front.
- Click Procedure from the box listing database object types. Procedures are listed in Entities list.
- Double-click an entity (procedure) to select it. Procedure Name appears in Procedure Name area. Parameters in that procedure appears in the Parameter list.
- For each entry (parameter), specify a constant value or parameter name.
Dynamic SQL Query
Using parameters:
To include a parameter in the SQL Query, specify a parameter enclosed within <% and %>.
Example: To dynamically decide where clause:
SELECT * FROM EmpTbl WHERE prmManufacturer = ‘<%PrmWhrCls%>’;
Using javascript code block:
Use javascript code block enclosed by <@% and %@> to create a dynamic SQL Query. After execution, the code block should return a string that will be replaced in the SQL query.
You can use following in the code block:
- if else
- parameter value and parameter data type
Syntax of code block:
<@% [Executable block, which returns a string] %@>
Syntax to use parameter in code block:
params(“parameterName”).getAttribute()
Attributes: “Value” to get value of the parameter and “DataType” to get data type of the parameter.
Examples
Example 1. Check if parameter “prmEmpNo” exists. If it does and its value is not blank, return the value, else return nothing.
Select * from emp where 1=1 <@% if(params("prmEmpNo") != null && params("prmEmpNo").getValue() != "") { return " AND empno in <%prmEmpNo%>"; } else { return ""; } %@>
Example 2. Check if parameter “prmSelectTable” exists. If it doesn’t, return table name as “emp” else (if it exists) value of the parameter to be used as table name.
Select * from <@% if(params("prmSelectTable") == null) { return "emp"; else return params("prmSelectTable").getValue(); } %@>
Example 3. Construct WHERE clause dynamically (for LOCATIONTYPEID and REGNID fields).
select LOCTIONID, LOCTIONNAME,LOCTYPEID,REGNID from LOCATIONMASTER where 1=1 <@% var v_Str; v_Str = ''; var v_Str1; var v_Str2; var v_Str1=''; var v_Str2=''; if(params("prmloctype") != null && params("prmloctype").getValue() != "") { v_Str1 = " AND LOCATIONTYPEID in (<%prmloctype%>)"; } else { v_Str1 = ""; } if(params("prmRegion") != null && params("prmRegion").getValue() != "") { v_Str2 = " AND REGNID = <%prmRegion%> "; } else { v_Str2 = ""; } v_Str = v_Str1 +v_Str2; return v_Str; %@>
File as a Data Source
This wizard allows you to select a file and provide parsing properties to fetch records from that file.
Figure 4: File as a Data Source
Web Service as a Data Source
This wizard allows you to select a web service available from the connected Web Service provider and provides method, record pattern details.
By reading the WSDL, it also prompts for parameters required to request the Web Service.
Figure 5: Web Service as a Data Source
Note: Intellicus also supports JSON as a File Type for File and Web Service data sources in a Query Object.
Field Properties
Figure 6: Field Level Properties
The Field Level Properties at Data Source step are listed in the table below.
Property | Values | Comments |
Data Type | CHAR,
NUMBER, DATE, BINARY |
Select the data type of the incoming data |
Data Format | Format String | Specify the format of the incoming data. This is useful only if Date or IP Address type data are incoming in CHAR fields but needs to be converted to Date and Number types for further use |
Database Time Zone | Select Time zone from the list | Specify the time zone in which the incoming date data is stored. This is useful only if date time data needs to be converted to other time zone data based on reporting requirement.
For example, when incoming GMT data need to show EDT (or any requested TZ) value in report, specify that the incoming data is GMT. The output format is generally specified in Format step or in user preference |
Length/Precision | Type yourself | Length of field for Char data type
Precision or length of field for Number data type |
Scale | Type yourself | Scale or number of digits after decimal point |
Locale | Select from the list | Select the language/ country in which the incoming date data is stored |
Sort Priority | Number 0-N | If the data is sorted on multiple fields then specify sort priority number of this field.
Primary sort field should be the lowest number |
Sort Criteria | Ascending/
Descending |
Specify sort as either ascending or descending order |
Qualified Name | Type yourself | This name can be used as the field name to resolve field name ambiguity when same field is fetched from two tables or expressions |
RESTful Web Service as a Data Source
Designers can create Query Objects using RESTful Web Service through the REST Web Service provider. You can use GET/POST methods to fetch data from the REST server. You need to provide the following details:
- Request Path, Record Pattern, Query, Matrix and Header parameter in case of GET method
- Request Path, Record Pattern, Query and Header parameter for Form POST method or
- Request Path, Record Pattern, Text data and Header Parameter for Text POST method
Intellicus supports all values under Text for Content-Type parameter. Below are the supported values under Form:
- application/json
- application/xml
- text/plain
Figure 7: REST as a Data Source
Format step
The Format step primarily lists all fields provided by the selected Query Object.
The Format step generally the last step of the Query Object workflow, specifies caption, group label etc. and acts as business view of the Query Object.
Figure 8: Format Step
For each field on the Format step, the following properties are set:
Property | Values | Comments |
Field | Name of Field
(read only) |
Original name of the field. |
Source | Step Name
(read only) |
In which step did this field originate in the Query Object.
Helps in tracking a field source in a complex Query Object |
Caption | Type yourself | This is name of the field which you can see under the Fields list while designing a report |
Hyperlink | Hyperlink Options:
URL Drill Down to another Report |
Detailed steps of specifying hyperlink is mentioned below in a separate section titled “Hyperlinking a Field” |
Group Label | Type yourself or Select from list | To create a new group, type the new group name.
To put this field into an existing group, select group name from the drop-down list. This helps to categorize fields if the number of fields fetched from SQL is large |
Hidden | Check/Uncheck | Check = makes this field invisible to users for reporting process.
It also hides this field from next Query Object if this Query Object is used as a Data Source |
GIS Enabled | Check/Uncheck | Check = This field has GIS classification data such as country names, state or city names.
GIS enabled field will appear in selection list for grouping option in GIS mapping visualization screens |
Format properties | ||
Width | Number 1-100 | Default width of this field when dragged onto a report |
Output Format | Format String | This field value will be formatted using this format string.
Useful for Date and Number formatting. If you need to decide the format string at run time, then select “Apply Locale Default” property in the Format String selector dialog |
Align | Left,
Right, Center |
This field, when put on a report, its values will be aligned to the selected side by default |
Input Format | Format String | This format string decides the prompting format for the value for this field on Ad hoc Filter screen.
This is useful in prompting date values in desired format. This is also used to input IP Address format for number values. For details on Output/Input Format, refer the below section titled “Data Format Dialog” |
User Time Zone | Select Time zone | Specify the time zone in which the date data is to be converted and displayed.
The Report Server calculates the difference between Database Time Zone and User Time Zone and does time conversion. For deciding User Time Zone at run time, select SYS_USER_TZ value. |
Render As | Select from list:
Blank Image |
Select Render As Image in case the string field has reference to an image path |
Width | Type yourself | Width of the rendered image |
Height | Type yourself | Height of the rendered image |
Source Type | Select from list:
Image Path URL |
The path of the image to be rendered can be a relative system path (example: ../images/intellicus_logo.gif) or an external URL (example: http://www.intellicus.com/Images/Intellicus_Logo.png) |
Hyperlinking a Field
A field can be made hyperlink to visit a web page or drill down to another report (parameterized report) at run time. For example,
- For a summary report having sales by country, you can hyperlink the country-name so that user can open respective country’s detailed sales report by clicking the country-name.
- From within a report, select and open web sites or documents dynamically, based on values used in the URL.
Figure 9: Hyperlink Options dialog box
Hyperlink Options
URL
You can hyperlink the field to open a specific URL.
The URL is automatically constructed by the “Drill Down to another Report” settings.
You can over write the URL on your own.
For external links such as web links you need to write your own URL text.
Drill Down to another Report
This option enables to hyperlink the field to open a specific report.
Hyperlink Dialog Properties
Property | Values | Comments |
Select | Select Report using Report Selector | Report to drill down to |
Target | New Window(_blank)
Same Frame(_self)
Parent Frame(_parent)
Parent Window(_top) |
Opens the drill-down report in new browser window
Replaces the parent report and opens the drill-down report in the same portal tab
Opens the drill-down report in new Intellicus portal tab
Replaces the Intellicus portal and open the drill-down report in the same browser window. |
Report Parameter | Parameter of the drill-down report | Report Parameter is the parameter in the report being set as hyperlink. |
Report Parameter Value Field | Select field from this report | Select the field whose value is to be passed to drill-down report as the selected parameter value |
System Parameter | Select from list | Select if you need to pass system parameter from this report to drill-down report |
System Parameter Value Field | Select value | For each system parameter you choose to pass, relevant values for that system parameter populates in the list automatically. You shall choose the desired value. Refer Appendix A to know about the System Parameter(s) and their value(s) |
Data Format Dialog
Data Format dialog is used to set the format of the field upon clicking icon. As an alternate, you can also type-in the format string in the Output/ Input Format text box.
Data Format dialog properties:
Format | General
Number Currency Date Time Percentage Scientific Text Network Id |
Specifies various formats of data for input or output.
|
General | No specific format to apply to the data. | |
Number | Treat the data with number validations as given below.
Apply Locale Default = Instead of providing a fixed format here, pick the format from Localeconfigurations.xls for user’s current selected locale or the default settings Decimal Places = maximum number of decimals allowed in input padding or rounding up to these many decimals in output Use 1000 Separator = Yes = apply thousand separators in output Negative Numbers = whether to enclose in braces or prefix with minus |
|
Currency
|
Treat the data with currency validations as given below.
Apply Locale Default = It picks the format from Localeconfigurations.xls for user’s current selected locale or the default settings Decimal Places = maximum number of decimals allowed in input padding or rounding up to these many decimals in output Check Use 1000 Separator to apply thousand separators in output Negative Numbers = whether to enclose in braces or prefix with minus Currency = Select currency symbol/chars to prefix |
|
Date | Treat the data with date validations as given below.
Apply Locale Default = Instead of providing a fixed format here, pick the date format from localeconfigurations.xls for user’s currently selected locale or the default settings Append Time = Append the time format from the localeconfigurations.xls and create date + time format string for user’s currently selected locale Format = Select a format string from the fixed formats’ list (Some formats may not be supported by the calendar control that helps end user to fill the date value) In case you need a fixed format date + time input or output formats, select the format strings that contain both date and time parts
|
|
Time | Treat the data as time part only.
Apply Locale default = Instead of providing a fixed format here, pick the time format from localeconfigurations.xls for user’s currently selected locale or the default settings Format = Select a format string from the fixed formats’ list (Some formats may not be supported by the calendar control that helps end user to fill the date value) |
|
Percentage | ![]() |
Treat the data as percent division so multiply by 100.
Decimal places = Decides number of decimal places in the output
Scientific
Treat the data as a big number that needs conversion into scientific format.
Decimal places = Decides number of decimal precision places in the output
Text
Process the text data according to the format string.
Formats = Select format types as:
Lower Case = Convert text into all lower case
Upper Case = Convert text into all upper case
Network ID
This format is applicable on number fields. This format treats the number 32bit IP number and converts to respective IP display formats.
Formats: Select format types as:
IP Address (IPV4) = Converts user entered dotted quad notation IP address into 32-bit IP number in input fields.
Converts 32-bit IP number into quad dotted notation of IP address in output fields
MAC Address = Converts user entered MAC address string into its EUI-48 number in input fields.
Converts EUI-48 number into its MAC address string in output fields
Note: The path for locale configuration file is:
Folder: <Install Dir>\ReportEngine\Config\i18n
File: localeconfigurations.xls
You may need to consult with your system administrator, in case you don’t have access to this file.
Filter Step
Filter Step can be used to apply filtering conditions on specific fields of the selected Query Object.
Filter step can take an input from any step (except Load and Format steps) and provide output to any step (except Data Source step) in the flow of Query Object.
The properties tab shows two sections for a Filter step:
- Select Filter Criteria
- Fields properties
Figure 10: Filter Step
Select Filter Criteria
You need to select available Field name, Criteria and Value to apply ad hoc filters for this step.
Only the data that matches the filtering criteria can pass to the output from this step.
Fields properties
For each selected field of the Query Object, the following properties can be set:
Property | Values | Comments |
Field | Select from list | Select the field on which you want to apply filter |
Criteria | Select from list | Select the operator to be used in the filter. These are comparison operators based on the data type of the selected field (different for character, numeric or date).
The between operator prompts for two values |
Value | Type yourself or select from list | Based on the configuration of this field in the meta layer, the value list appears |
Relation | AND
OR |
AND = The next condition is applied with combined conjunction of this condition
OR = The next condition is applied in alternate conjunction of this condition |
Open/Close | (
(( ((( (((( ((((( ) )) ))) )))) ))))) |
Braces to group a set of conditions for applying appropriate AND/OR combination |
Use Parameter | Check/Uncheck | Check = When Use Parameter is checked, Value gets populated with parameter values for comparison |
Additional Mandatory Filters | Specify a number | This specifies the number of filters in addition to existing filters that are mandatory for user to choose |
Lookup Values
|
Check/Uncheck | Check = Whether this field provides a list of lookup values to apply filter on. Lookup values can be retrieved from a value list or from another table or query
|
Mandatory | Check/Uncheck | Check = Will mandate reports using this Query Object to apply filter on this field |
Hide | Check/Uncheck | Check = Will remove this field for filter options. End user will not see this field in filter-on field list |
Lookup Details | ||
Key Value Field | Select a field | When we have Lookup values with display and value columns, the value should apply to filter on key field, instead of display applying filter on this field. This is a SQL optimization option.If you create a lookup with customerID and customer name and your table is indexed on customerID, then for the customer name field, set customerID as Key Value Field |
Dynamic | Check/Uncheck | Check = You can set a source (SQL or another Query Object) for the Lookup values |
Static | Check/Uncheck | Check = You can type in the lookup values |
Restrict to list | Check/Uncheck | Check = The list shown to the end user for selecting values for filtering should not allow typing in new value other than list |
Fetch | Now,
On Every use, Lazy, By Search |
Now = Fetch the values only upon saving this Query Object
On Every Use= Fetch the values every time end user screen loads for prompting filters Lazy = Fetch the values when user selects this field for filtering and clicks the combo for value selection By Search = Fetch matching values when user starts typing values in the filter |
Min. Key Length | Number 0-4 | By search fetching of data starts only after these many characters are typed by the user |
User Defined | Check/Uncheck | Check = To provide a user defined SQL or data source for lookup values
Uncheck = Automatically generates distinct based query to get lookup values |
New Source/
Existing QO |
New Source/
Existing QO |
Whether user defined is new SQL or an existing Query Object |
Display Column | Select from List | From lookup value result set, select the field to display on the user interface |
Value Column | Select from List | From lookup value result set, select the field to pass the value under filters |
Link Lookup | Check/Uncheck | Check = Specifies that this is a nested lookup |
Link Lookup To Parent Field | Check/Uncheck | Check = Select the parent field to which this field will be nested.
For example, Set Country as the parent field for this property of State field Note that you must use the parent value in the where clause of lookup SQL |
Link Lookup
Mandatory |
Check/Uncheck | Check = Before applying a filter on Parent field, the nested field lookup values will not be listed
Uncheck = Before applying a filter on Parent field, ALL values will be listed for nested field. When a filter is applied on parent field, NESTED values will be listed |
Sort Step
Data that passes through a Sort step gets sorted based on the properties like Sort By Field, Criteria, etc. as specified in the step.
Figure 11: Sort Step
The properties to set are:
Property | Values | Comments |
Sort by Field | Select field from list | You can add multiple fields using Sort by and Then by lines based on priority of sorting in the order they are applied |
Criteria | Ascending/Descending | Sorting criteria as either ascending or descending order |
Case Insensitive | Check/Uncheck | Check = ABC is at same level as ABc
XYZ is smaller than abc Uncheck = ABC is smaller than ABc XYZ is bigger than abc |
Formula Fields Step
Formula Fields step allows you to add calculated fields that are populated at run time. These calculated fields are generally based on existing fields. In the below figure, interest amount is calculated applying a formula on the fields of the selected data source.
Figure 12: Formula Fields Step
Action Button | Comments |
Add ![]() |
To add a new formula field |
Delete ![]() |
To delete the selected formula field |
For each added formula field, following properties are set:
Property | Values | Comments |
Name | Type Yourself | Name of the field as visible to the end user |
Return Type | Number
Char Date |
Determines the data type of the formula field |
Length/Precision | Type yourself | Length of field for Char data type.
Precision or length of field for Number data type |
Scale | Type yourself | Scale or number of digits after decimal point |
Formula | Formula String | Java script syntax formula
|
Formula String Syntax
Follow JavaScript Syntax to create a formula. To create a formula, you can use field names and define variables. A formula may have ‘if’ construct as well as ‘nested if’ construct. You can use logical operators too. If want to add more than one statements in formula, use semicolon ‘;’ as separator between two statements.
Example:
For a formula field named TotalAmount,
var total ;
if (unitprice < 10 )
{total = unitprice*quantity;}
else
{total = unitprice;}
TotalAmount = total;
Specifying Actions from the Button Palette
The various menu actions that can be performed on the Query object are described in the below table.
Item | Action |
Add New | Closes currently opened Query object and provides a new screen for designing an Query object |
Open | Opens a selector dialog to select and open a Query Object for editing. To open a Query Object, expand the folder and either double-click or click the Query Object name and click Open.
List View shows the list of Query Objects Detailed View shows the detailed view of Query Objects list Refresh List refreshes the shown list of Query Objects fetched from the repository |
Save | Saves any editing work done on the Query Object |
Save As | Saves currently edited Query object with a new ID (auto-generated) and Name in repository. You can choose a different folder location to save.
If you are saving an existing Query Object with a new name, check “Copy Access Rights” under Options to copy the same rights to this new Query Object. You can also add Description to the Query object |
Connections | Helps to specify a Parent connection |
Advanced | Please refer the Advanced Properties section in this document |
Revert | Reverts to the last saved instance of the Query Object |
Delete | After a confirmation prompt dialog, deletes currently opened Query Object from repository. This action is not reversible |