BI-Creator

Concept Link: BI Creator

BI Creator Module is the PAT DYNAMIC Report Creator which allows the User to dynamically create reports using SQL Statements, filter fields using (optional and required) parameter, and to preview the data of the input criteria. Dynamic SQL is a programming technique that enables you to build SQL statements dynamically at runtime. You can create more general purpose, flexible applications by using dynamic SQL because the full text of a SQL statement may be unknown at compilation. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.

1 Components

PAT BI consists of two areas:

• Ribbon
• PAT BI Creator (Workarea)
• BI Form (Dialog Filter Area)

BI Creator Tool consists of three workspaces:

• Main: Allows the user to create dynamic SQL statements and add filter options.
• Report Layouts: Allows the user to create reports using Report Designer.
• Settings: Display options for Grid View, Pivot Grid View and Endpoint.

2 Ribbon

• 1) Open Report Designer: Opens the Report Designer,
• 2) Edit Report Parameters: Here you define who may use the report (different roles),
• 3) Add Report Layout: Add a new Report Layout,
• 4) Delete Report Layout: Delete a Report Layout,
• 5) Create Menu: Rebuilds the menu under PAT BI,
• 6) Export Report: Exports the report with all layouts (Report & Dashboard),
• 7) Export All Reports: Exports all reports with all layouts (Report & Dashboard),
• 8) Import Report(s): Imports one or more reports with all layouts (Report & Dashboard). If the report already exists, you have the option to overwrite the existing report or create a new one.
• 9) Create PAT Mobile Access Rights: Creates the access rights for PAT Mobile,
• 10) Create PAT API Access Rights: Creates the access rights for PAT API,
• 11) Add Dashboard Layout: Add a new Dashboard Layout,
• 12) Delete Dashboard Layout: Delete aDashboard Layout.

3 PAT BI Creator

• 1) Report Id: Predefined Id for the created report.
• 2) Report Name: Report Name which will be used in the creation and display area.
• 3) Dynamic SQL Statements: Dynamic SQL statements using SQL standards Like, Equal, NotEqual.
• 4) Filter Area: Adding Filter based criteria (WHERE STATEMENT).
• 5) Preview Area: Displays result-sets from the SELECT statement.
• 6) Field Area: Additional changes for the columns, renaming, type changing.

The BI Tool uses standard SQL including a WHERE clause (Filter Area), field area where columns can be renamed and adjust the Display Text and the Preview Area.

3.1 Dynamic SQL Statements

The PAT Enterprise Dynamic SQL Statements allows the User to insert free-text SELECTs, define optional(define as ?) and required parameters (WHERE CLAUSE) and many more; the result can be displayed in the Preview Area (Data Area).

1) SQL Statements with all fields mandatory:

<syntaxhighlight lang="sql">
SELECT * from B_PRODPLAN
WHERE ARTNR = :ARTNR
AND FORM = :FORM
</syntaxhighlight>

2) SQL Statements with one optional field (?AND FORM = :FORM).Where clause is always mandatory.:

<syntaxhighlight lang="sql">
SELECT * from B_PRODPLAN
WHERE ARTNR = :ARTNR
?AND FORM = :FORM
</syntaxhighlight>

3) SQL Statements with all fields optional:

<syntaxhighlight lang="sql">
SELECT * from B_PRODPLAN
WHERE 1=1
?AND ARTNR = :ARTNR
?AND FORM = :FORM
</syntaxhighlight>

3.2 Dynamic Filter area

The Dynamic Filter area allows the user to add filter values as WHERE clauses which will be included in the SELECT statement.

1) Position: You have the possibility to change the order given by the system by drag and drop.

2) Id: The column name from the database.

3) Display Text: The alias for the column to be used in the SQL statement.

4) Type: The Type determines which control is used. The type can be selected from:

• 'Input', 'Date', 'Lookup' -> a field can be selected,
• 'Lookup(Search)' -> optional search within the lookup,
• 'Multi-Select Lookup' -> CheckedComboBox multi-selection

=> must IN (:Company) or 'Fixed Value' -> There should be the possibility to take fix defined values, these are: Current Company ID, Current Main Location ID, Current Workstation

5) Option:

• For type 'Input', the option can be set as Default, Uppercase, Lowercase, and Numeric;
• for type 'Lookup', 'Lookup(Search)' or 'Multi-Select Lookup' this option will be taken from the model, stored procedure, hardcoded values or SQL.

With the SQL filter you have the possibility to define a selection list by SQL query.

6) Type Detail: In case of 'Lookup', 'Lookup(Search)' and 'Multi-Select' the option can be chosen from 'Model', 'Stored Procedure' (a package including a function) or 'Hardcoded' (verbous free-text).

7) Test Value: The Test Value Area is the Column where required values for the Input, Date, Lookup, Lookup(Search) and Multi-Select Lookup will be added.

8) Mandatory: If you remove the "?" from the sql-statement, an optional entry becomes a mandatory

?AND = :CompanyId => Optional 
AND = :CompanyId => Mandatory

3.2.1 An overview of all available Type Options:

Types	Option	Description
Input	Default	Unrestricted input
	Uppercase	Capital letters only
	Lowercase	Lower case only
	Numeric	Only numbers
	Multiple	Multiple input; only works in combination with the syntax "in", for example: artnr in (:artnr)
Date	Default	Default date of the calendar
Lookup	Model	Under Type Details a model can be selected from the list.
	Stored Procedure	In the background there is a package called PAT_BI_Filters, which is used for selection. Currently replaced by SQL.
	Hardcoded	For entering a list with fixed values. (a,b,c)
	SQL	An SQL statement can be entered, taking into account the company (use as :COMPANY_ID).
Lookup (Search)	Model	Under Type Details a model can be selected from the list.
	Stored Procedure	In the background there is a package called PAT_BI_Filters, which is used for selection. Currently replaced by SQL.
	Hardcoded	For entering a list with fixed values. (a,b,c)
	SQL	An SQL statement can be entered, taking into account the company (use as :COMPANY_ID).
Multi Select Lookup	Model	Under Type Details a model can be selected from the list.
	Stored Procedure	In the background there is a package called PAT_BI_Filters, which is used for selection. Currently replaced by SQL.
	Hardcoded	For entering a list with fixed values. (a,b,c)
	SQL	An SQL statement can be entered, taking into account the company (use as :COMPANY_ID).
Fixed Value	Current Company ID	Refers to the currently selected company.
	Current Workstation	Refers to the current workstation.
	Current Main Location ID	Is defined in the "Workstation Settings".

3.3 Preview Area (Data Area)

The Preview Area presents a data preview of all columns which are defined and included in the SELECT statement.

3.4 Field Area

In the Field Area of the BI Creator, the fields which are defined in the SELECT statement can be renamed and the type of the field can be changed. The columns which are included in the Field Area are:

• POS: You have the possibility to change the order given by the system by drag and drop.
• Database Name: Column name from the database and the SELECT
• Code Name: Name used in the WHERE statement
• Display Name from Resource: The name from Resources: Checkbox if the display name from the resources will be used.
• Display Name: Name which is used in the Dialog and Report Area.
• Display Type: Datatype of the field (e.g. .NET Field types)
• Topsy Turvy: turn everything upside down (upside down).
  • For example: select artnr, artkbz, fl_inaktiv from b_artikel (the InActive becomes an Active and is therefore easier to read)

3.5 Settings

BI Type: There are several possibilities:

• Default: The Report Layout is used for display.
• Dashboard: The Dashboard is used for display.

Show Grid: Shows the data in grid design within the Default

Show Pivot Grid: Shows the data in pivot grid design within the Default

Auto Refreshing: Is a simple extension that allows you to automatically update and reload a page at a certain interval. The interval is specified in seconds.

Auto Refreshing Interval: Defines the interval time in seconds,

Enable Api: It allows data to be called up or viewed from different platforms

4 Enterprise BI Form

4.1 Dialog Filter Area

The Dialog Filter Area is used to display the data created in the BI Creator using Grid, Pivot Grid or a Report (which can be created using the Report Designer).

Preview(Grid PivotGrid)

After creating a BI Form (saving), the next step is to create a Menu item (Create Menu) as Dialog Form, which can be opened in ListBox. The Dialog Filter Area is used to edit filter values (from the WHERE CLAUSE) to display the data as Grid or Pivot Grid.

Create Menu Tab

In the BI Form Dialog, the predefine can be used and the Data can be displayed in View (Preview as Grid or Pivot Grid). As an example, we create a Report based on our Data from the SELECT we used before.

<syntaxhighlight lang="sql">
SELECT * from B_PRODPLAN
WHERE ARTNR = :ARTNR
?AND FORM = :FORM
</syntaxhighlight>

Reporting Area

Overview Enterprise Client Menu

5 Report Designer

Die Enterprise Client Reports Suite bietet einen vollständigen anpassbaren Endbenutzer-Berichts-Designer für das erstellen bzw. bearbeiten von vorhanden Reports. Enterprise Client Reports Administration zeigt eine Übersicht über die vorhandenen Reports und deren Kategorien die vom Entwickler erstellt werden müssen.

Enterprise Client Report Designer

Report Administration

6 PAT Mobile BI

PAT BI

The BI tool is a reporting application which works hand in hand with the PAT Enterprise. In the PAT Enterprise you have a report creator, which is described here.

After adding some reports in the Enterprise, you can see them in the PAT Mobile too.

By clicking on the menu item BI you get an overview of all reports which were created. After clicking on the Open Report button a filter appears. This filter works the same as in the PAT Enterprise. You can even use the saved filters on both programmes. After applying the filter you will receive the report data.

If there is a report layout saved, you will get the option to download this report, either as pdf or as xlsx format. Just select the wanted layout in the combo box and click on the button of the format you want. Then the download will start automatically.

Report Administration

BI Creator

Overview Enterprise Client Menu