Intellicus Enterprise Reporting and Business Insights 19.1

SQL Designer

5 views June 28, 2020 0

You can  design reports using SQL Designer to retrieve data from the connected database using a query object and then use this retrieved data to design a report.

Using this designer, you can select different database objects, create queries and validate the created queries and save them for later use, and create joins.

The SQL Designer is divided into:

  • Diagram pane on which you can drag and drop the database objects.
  • Designer pane on which you can perform actions on the database objects such as creating joins, deleting tables.
  • Parser pane on which you can design SQL statements.

SQL Designer
Figure 1: SQL Designer

Note: You can resize the individual panes for a better view by clicking the border between two panes and dragging it vertically or horizontally.

Warning: If you do not define an SQL query, you will not be able to design a report.

You can perform the following tasks using the SQL Designer:

Selecting Database Objects

You can select the radio buttons to choose the database (schema) objects such as procedure, synonym, table, and view in the SQL Designer. On selecting the required (schema) objects, the procedure, table, synonym, and view names appear in the list pane adjacent to it. You can select these names and drag it to the diagram pane.

Selecting Database Objects
Figure 2: Selecting Database Objects

You can perform multiple selections using the following methods:

  • Use <ctrl> <click> to select random values
  • Use <shift> <click> for a continuous range of values, or,
  • Simply click and drag individual values.

Important: Before you use a procedure, make sure that it does not perform any unwanted data manipulation in the connected data source.

Note: For OLAP type connections Design tab, Filters tab, Sort tab, Stored Proc. tab in SQL Parser pane will not be available. You can use Edit tab to write the query.

Removing Tables from SQL Designer

To remove a table from the SQL Designer, right-click the table and select Delete Table. If you have defined a join on the tables, you need to first delete the join.

Delete Table
Figure 3: Delete Table

Creating Join Conditions

An SQL join clause – corresponding to a join operation in relational algebra – combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A Join is a means for combining columns from one (self-join) or more tables by using values common to each.

They can be used to unite some or all of the data from two or more tables into one comprehensive structure. You can perform joins using the diagram pane. To do this:

  1. Click the value(s) from a database Field List.
  2. Drag it to the required field.
  3. When the cursor takes join icon shape, release the click.

The join will be indicated as an arrow symbol between the selected fields.

Perform Joins
Figure 4: Performing Joins

Note: When you select two tables, Intellicus may automatically create join between the two. This would happen because a Primary Key – Foreign Key relation was already created in the database and Enable Autolink Foreign Key check box (Options > Custom Design) is selected. In this case, respective condition will also be added in WHERE clause of the query.

If Enable Autolink Foreign Key check box is clear (not selected), Joins are not automatically created.

Removing a Join

To remove a join condition, select a join condition by clicking arrow icon under the where clause and press <delete> on the keyboard.

remove a join
Figure 5: Removing a Join

The join will be removed.

Using SQL Parser

The SQL parser has the following tabs:

SQL Parser
Figure 6: SQL Parser

  • Design – to design SQL statement
  • Stored Procedure – to use for data coming from stored procedures
  • Edit – to edit the SQL statement
  • Result – to display the result based on the SQL statement
  • Filters – to set filters in order to provide filter values at report run time
  • Sort – to specify levels of sort at run time
  • OK – to continue with the changes made
  • Cancel – to cancel the changes made

The formatted SQL statement is provided at the bottom of the SQL Designer dialog box. The values in this formatted SQL statements are populated as per the selections performed in the pane(s) given above it. This facilitates quicker generation of SQL statements and allows editing the same as per requirement.

Designing an SQL Statement

The Design tab contains a complete SQL statement divided into its clauses, according to the objects chosen from the diagram pane.

The SQL clauses available in this tab are:

Select Clause

In the Select clause, the columns selected from the diagram pane are displayed. This allows quick selection and removal of the columns from the select clause. You can also edit this clause by directly writing into that box. Addition of a column using the SQL clause box will reflect on the selections performed in the diagram pane. But removal of a column might not be reflected in the diagram pane.

Tip: You can press <ctrl> <spacebar> to seek context sensitive list of values in all clauses.

Where Clause

In the Where clause, every condition is a row, and the following options are available:

Option Description
Open This option helps in grouping the conditions by using opening braces.
Operand 1 This is a combo box with all available column names. This will also display the parameters (report, system) defined in the report.
Operator This is a combo box, which contains the comparison operators that you may use to compare operand 1 with operand 2.
Operand 2 This is a combo box with all available column names and parameters. It can be used for comparison with the operand 1 chosen earlier.
Close This option helps in grouping the conditions by using closing braces.
Relation You can relate the current condition with the next condition using ‘AND’ or ‘OR’ logical operators.
Group by Clause

In the Group By clause you can provide grouping criteria for the SQL statement.

Having Clause

In the having clause you can define conditions, similar to those defined in the Where clause.

Order by Clause

In the Order By clause you can provide sorting (ascending/ descending) criteria for the SQL statement.

Important: 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 designer.

Writing Power SQL

When you switch from the Design tab to the Edit tab, the SQL in the Design tab is constructed and displayed as a complete SQL statement in the later. Using the Edit tab, you can view and write complex SQL statements that cannot be defined using the Design tab.

During Query writing, you can make use of Context Help. It assists you in correct code (syntax) formation. As you type the code, you can view the context help to select the options. A window pops up for you to choose the right option and disappears upon choosing the option.

editing SQL Statement
Figure 7: Editing SQL Statement

To prevent a complex query (defined in the Edit tab) from being overwritten, when you switch over to the Design tab, make some changes, and get back to Edit tab; you will be prompted with a dialog.

SQL Overwrite Prompt
Figure 8: SQL Overwrite Prompt

If you select Yes your changes in the Edit tab will be overwritten, because the SQL in the Design tab will be reconstructed. If you select ‘No’ then the SQL in the Edit tab will remain intact and shall be used as the final SQL.

Note: The SQL statement in the Edit tab will be used as the final SQL for compilation.

Important: If SQL is manually specified in Edit tab (not constructed using design tab), its field details will not be available in Filter tab.

Dynamic SQL Queries using parameters

To include a parameter in the SQL Query, specify a parameter enclosed within <% and %>.

Example: To dynamically decide the where clause:

SELECT * FROM EmpTbl WHERE <%PrmWhrCls%>

Dynamic SQL 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 does not, return table name as “emp” else (if it exists) value of the parameter must 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;
%@>

Find Button

The Edit tab has a find button. For long queries (refer to the figure on previous page), you can search the query for a search string to reach the location quickly.

Stored Procedures

The SQL Stored Procedure (SP) is a collection of SQL statements and SQL command logic, which is compiled and stored on the database and used to create SQL queries to be stored and executed on the server. Stored procedures can also be cached and reused. We can use and execute these stored procedures to design studio reports. Using stored procedures improves the performance of reports and data rendering.

Selecting Procedure radio button from the Object list on top left of the SQL Designer enables the Stored Procedure tab and disables the Design tab automatically.

select stored procedure
Figure 9: Selecting a Stored Procedure

Drag the selected Stored Procedure into the SQL parser. It will populate the Stored Procedure Parameters. You need to map either value or user defined parameters under UDFParams column.

Note: While designing query objects, use of reserved keywords in SQL Query (for column names/aliases) is not allowed. This is applicable for both, SQL queries as well as stored procedures.

Compiling SQL

When you select the Result tab, the defined SQL statement will be compiled. Record-set is displayed in case of successful completion. If there are errors, they are displayed in the same tab. This would help you in finding the exact location of error(s) and rectify them before using the SQL results in the Layout Designer.

SQL Compilation
Figure 10: SQL Compilation

If the SQL has used a parameter, you will be prompted to provide the values of the parameter.

Viewing SQL Results

The Result tab will display the result based on the SQL statement in the Edit tab. If the SQL statement has used a parameter, you will be prompted to provide the values while viewing the results.

SQL Results
Figure 11: SQL Results

Applying Filters

At report run time, you may wish to receive the data that makes sense at that time. On this tab, you can set filters so that you can provide filter values at report run time.

Applying filters
Figure 12: Applying filters on SQL

To add filters

Select a field by clicking it (Press and hold Ctrl key and click fields to select multiple fields) 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 of the selected fields as dropdown.

Use >> to move all the fields to Selected Fields list.

To deselect 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 do not 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 check box to get only one record if record has multiple records having completely duplicate values.
To set up default value for filter
  1. Select the field name from the table.
  2. 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 Adhoc 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. For more information on Report Parameters, refer to Desktop Studio – Report Parameters document.
  • FetchData: Select this check box for the field if filter type is Adhoc 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 check box if it is mandatory for user to provide value for this field at runtime.
Sorting at Run Time

This tab is used to specify levels of sort at run time.

SQL Results
Figure 13: Options for sorting at runtime

  • 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 user to be able to provide three sort choices at run time (e.g. by Country, State and county), select 3.
  • Qualifier: When SQL is typed in Edit tab, fields may not be prefixed888888888888888 with table name and so, Available Fields list also do not have table names prefixed. Select a field and specify table name to associate it with a table. This is especially useful when you have same field name from two tables and wish to differentiate one from another.
  • 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 check box 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.

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.

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 ‘x’ button to delete that row. Pressing enter key in last row will append a row at the bottom. Select a row and Use Up or Down arrow button to modify the sequence of field-appearance.

Click Ok to continue with the changes.

Close the report. The message to save the designed changes to the report appears.

Click Yes to open the report with the specific design. Now, you can do additional changes to the saved report by using different controls present on the Intellicus Desktop Studio toolbar and then publish the report on the Intellicus Portal.

Click Cancel to cancel all the changes and exit.