Creating Reports


This section describes creation and maintenance of the created reports. Create vibrant reports with a simple user interface on consistent basis. 


Creating a Report


To create a report: 

  1. Click the  icon on the left side of the screen. 
  2. From the available domains list, select the data source on which the report will be based. Alternatively, select the SQL option and mention the Table Name, Database Connector, Domain and Query.


NOTE: You can pass the dependent report/chart filter name to the query. Please suffix the report or chart filter name with the respective data type value. 

Ex: “:DateParam” /*DATE*/ . It is mandatory to provide a “DATE” suffix for the date data type. Similarly, “NUMBER“ for number data type & “TEXT” for varchar but both of these are optional.

Limitation: Self LOV option for List of Values source will not work for the dependent reports on this table. Instead, use query or LOV Names based List of Values source for the report/chart filters.



You can pass the dependent report/chart filter name to the query. Please suffix the report or chart filter name with the respective data type value. 

Ex: “:DateParam” /*DATE*/ . It is mandatory to provide a “DATE” suffix for the date data type and can optionally provide “NUMBER” for number data type & “TEXT” for varchar. 

These options are can be viewed in the report creation and SQL based tables in the domain section. 



3.  In the Configure Report screen, select the specific domain that hosts the desired data.


Domains List: Select the organization domain to pull portions of data to populate the Report. Once the domain has been selected, the configuration screen will be re-populated with the available tables that reside in that domain. Start the drag and drop operation of the desired columns into the report columns window.


  1. Tables Window: Displays tables containing data in the selected domain.
  2. Report Columns: Listing of selected columns being installed in the report. 
  3. Column Properties: Customize the look and feel of each individual column by changing properties such as type formats, styles, alignment, color etc.
  4. Progress Icons: These icons show each portion of Creating & Editing Charts that can be customized. Complete a section and click the Next or Previous icon to move forward or backward through each section. The icon that is a darker shade than the other icons indicate the active edited section. To proceed to the next step in creating a report, continue reading.



Circular Join

Circular join is a type of join that comes into picture when columns from more than 2 tables are used to create a report. An alert will be displayed when columns are dragged and a choice to choose between the available joins is displayed.

For illustrative purposes, three tables are used here. As observed in the below image, the third table (Order Details) is joined both ways with the first (Customers) and second table (Employees). Also, observe that the Customers table is in a one way join to the Employees table.



During the create report process, by dragging and dropping a column from a table, you will be able to view all the tables that are joined to the initial table. Tables that are not joined to the initial table will not be displayed. 


The third table (Order Details) here forms a circular join resulting in the formation of unintended extra joins that will narrow down the input data in the final report output. To avoid this situation, you will be informed about the presence of a circular join and given a choice to choose the required table join to resolve the issue. Choose a table and click OK. 

 

Switch off the circular join alert from the report settings and admin user settings page. 

At the Report Level


First driving table Should Be Expanded


After editing the report, always first table in the Data source screen among multiple tables should be expanded by default.





By default, the option selected at the report level is Off. The admin can turn it to On. By selecting Default the user is reflecting the option chosen at the Admin level. 

Overrided Report Process count, status, and process log with a setting

Provided a setting in the report settings to override the values returned either through trigger or any other means of the process. If this setting is not empty, then consider override. Query the table provided in the XML tag for first three XML columns by reports process_id

This setting is to override trigger processes generated rowcount, status, summary log to the SplashBI process so that these can be viewed in the SplashBI Process Status window. 

If Override Process Detail Type is XML ,Follow below syntax 

<process-details><rows-column>num_rows</rows-column><status-column>status</status-column><summary-log>log</summary-log><table-name>sbia_proceses</table-name><where_clause>process_id = $system_process_id</where_clause></process-details>

If Override Process Detail Type is Query 

Provide Query with mandated aliases as 'row_count', 'status', 'summary_log'.

Setting Name - Override Process Details Override Process Detail Type

Description - Set this to override report process status, row count, and process log

Setting Name - Override Process Detail Type

Description - Set this for selecting type of process detail override.

This setting is used to select type of process detail override.

If user select Default the value is fetched from user settings

Setting for Fnd_sessions insertion


We have profile option Oracle EBS: Added FND_SESSIONS setting to Initialize at Site level and at report level option for Oracle EBS Report

If this setting is on, then in EBS init block we will call this statement. 

     INSERT INTO fnd_sessions (session_id,effective_date)

                   (SELECT USERENV('SESSIONID'), TRUNC(SYSDATE)  FROM dual

                    WHERE NOT EXISTS (SELECT 'c'

                    FROM fnd_sessions fs2

                    WHERE USERENV('SESSIONID') = fs2.session_id)

                   );

Added below settings 

Setting Name: Insert fnd session

This setting is displayed only for Oracle EBS reports in report settings.

SQL Hint 

User can provide hints so that database can perceive them and execute the report SQLS faster as per database optimizer standards.


Optimizer hints can be used with SQL statements to alter execution plans. Hints can be on single tables and multiple tables as well.


User need to provide a new setting at report settings level and table settings level to enter SQL hint.


The hints in the report setting will override the table(s) level settings and report SQL will have HINTS after SELECT clause.


If there are multiple tables for the report with hints then all hints need to be brought to the SELECT clause.



Report Icons



  • Report Columns: To learn more on this topic, click here.

  • Filters: To learn more on this topic, click here.


  • Filters Criteria: To learn more on this topic, click here.

  • Pivots: To learn more on this topic, click here.

  • Sorts: To learn more on this topic, click here.

  • Aggregation: To learn more on this topic, click here.

  • Triggers: To learn more on this topic, click here.

  • Distribution: To learn more on this topic, click here.

  • Advanced: Contains Drilldown, Data Source, Template, Portals and Totals.