Intellicus Enterprise Reporting and Business Insights 18.1

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

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.

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

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:

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

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:

1. Select Filter Criteria
2. 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:

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:

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:

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.