Intellicus Enterprise Reporting and Business Insights 18.1

Creating Query Objects using Simple Steps

0 views June 28, 2020 0

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:

  1. RDBMS source using SQL or stored procedure
  2. File source
  3. Web service
  4. Another Query Object
  5. 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.

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

  1. Select any of the objects among Table, View, Procedure and Synonym.
  2. 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:

  1. Select Table, Procedure, View or Synonym. List of entities of selected item appears.
  2. 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 insert icon in the leftmost cell of column header. This inserts a row below column header.

To get a row below current row:

Click insert icon 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 cancel icon 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 cancel icon 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 insert icon in the leftmost cell of column header. This inserts a row below the column header.

To get a row below current row:

Click insert icon 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 cancel icon 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.

File as a Data Source
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.

Web Service as a Data Source
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

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

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

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

Hyperlink dialog
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 ellipses button 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.

data format

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.

data format-currency

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.

data format-date

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.

data format-time

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

data format-scientific

Decimal places = Decides number of decimal precision places in the output

Text

Process the text data according to the format string.

data format-text

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.

data format-network ID

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:

  1. Select Filter Criteria
  2. Fields properties

Filter Step
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

Note: Lookup Values is enabled for char and date type fields

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.

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

Formula Fields Step
Figure 12: Formula Fields Step

Action Button Comments
Add add button To add a new formula field
Delete delete button 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