The Reporting Framework is intended to generate reports that are helpful for users to analyze the data more efficiently. The OvalEdge application automatically generates and presents the reports with the Users LoggedIn information, Schemas, Tags, Tables, etc. These reports are generated using Detail SQL, Filter SQL, and Chart SQL queries.
The OvalEdge application provides an ability for users to customize the reports within the Reporting Framework using SQL Queries and allows the users to view the desired reports with a specific chart name or with the contents specific to the report group.
The process to create custom reports
To customize the report within the Reporting Framework, we need to follow four steps:
- Develop Reporting Framework SQL
- Execute the Developed SQL
- Execute the Advanced Job
- View the Reports
Develop Reporting Framework SQL
The OvalEdge application provides an ability for users to customize the reports within the Reporting Framework using SQL Parameters and Values.
Example: Users License Type Report
The Users License Type is a sample report developed using various parameters and SQL queries. This report provides information about the number of users and their license types in the OvalEdge Application.
To develop the Users License Type report, the Detail SQL, Chart SQL, and Filter SQL are required. Here the Detail, Chart, and Filter SQL are dependent on each other and without the Detail SQL, the Chart and Filter SQL queries cannot be generated because the Filter and Chart related data is retrieved from the Detail SQL.
- Detail SQL: It provides the summary of the report in the tabular format, here the data existing in the OvalEdge database is fetched with respect to the Detail SQL and displayed in the OvalEdge Reports.
Example: In the case of the Users License Type report with the help of Detail SQL, all the users with their license types existing in the OvalEdge database are retrieved with Username and Licence Types details and displayed in the OvalEdge Reports in Tabular format. - Chart SQL: It provides the total count of the data associated with the Detail and Filter SQL.
Example: In the case of the Users License Type report with the help of Chart SQL, the total count of users existing in the OvalEdge database with the specific License Types are displayed. - Filter SQL: It provides the information in a detailed format and allows the user to view the specific data. Here the parameter from the Chart SQL is passed to the Filter SQL to get a specific set of data selected by the user in the tabular format.
Example: In the case of the Users License Type report with the help of Filter SQL, the information existing in the detail report is displayed and allows the user to view the data specific to the license type.
Users License Type Report SQL Query
The following is a simple select query that will fetch the license types of a user within the selected OvalEdge Application.
INSERT INTO stg_oereportext(
chartname,
reportgroup,
connectionid,
detailsql,
filtersql,
chartsql,
businessdescription,
processed_flag,
error_msg )
VALUES
('Users and License Types','OvalEdge Reports',-1,'select user.userid as Username,(case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end)licensetype
from user',
'select * from (select user.userid as Username,FNAME as "First Name",LNAME as "Last Name",
EMAIL as "Email",GENDER as "Gender",TITLE as "Title",user.status as "Status",
(case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end) licensetype from user) tmp where licensetype=?',
'select (case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end) licensetype,
count(userid) as "#Users"
from user group by(case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end)',
'Users and their Licensetype',
0,
'Invalid input'
);
The following are the parameters and the values mentioned in the above query.
Parameter Name: chartname
Description: Name of the Report
Value: Users License Types
Parameter Name: reportgroup
Description: Type of the Connection
Value: OvalEdge Reports
Parameter Name: connectionid
Description: The ID for the connection created in the OvalEdge
Value: -1
Parameter Name: DetailSQL
Description: This SQL query is used to select the columns from the OvalEdge tables and list the values required for the report.
Value:
select `user`.userid as Username,(case when licensetype=1 then "Business User"
when licensetype=3 then "Author & Analytical" end) licensetype from user
In the above Detail SQL Value, the “user” is the table used and with the help of this query, the “userid” and “licensetype” details are retrieved.
Note: In the Detail SQL, the “case” statement is used to check the conditions and return a value and if there are multiple conditions then the first condition is met and if that condition is true, it will stop reading and return the result.
On the successful execution of the Detail SQL, all the users with their license types existing in the OvalEdge database are retrieved with Username and Licence Types details and displayed in the OvalEdge Reports in Tabular format.
Parameter Name: FilterSQL
Description: This SQL Query is used to act as an action filter to display the values of the columns selected in the Detail SQL.
Value:
select * from (select `user`.userid as Username,FNAME as "First Name",LNAME as "Last Name",
EMAIL as "Email",GENDER as "Gender",TITLE as "Title",`user`.status as "Status",
(case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end)
licensetype from user) tmp where licensetype=?
In the above Filter SQL Value, the “user” is the table used and with the help of the nested select statement the “userid”, “FNAME”, “LNAME”, “EMAIL”, “GENDER”, “TITLE”, “status”, and “licensetype” details are retrieved.
On the successful execution of the Filter SQL, the system allows the user to view the specific filtered data. Here when the user clicks on the specific license type, the users associated with the selected license type are displayed in the Tabular format.
In above the screenshot, the highlighted region specifies the filter data. Here when the user clicks on the “Author & Analytical License Type” Bar, the User details associated with the selected License Type are displayed with the USERNAME, FIRST NAME, LAST NAME, EMAIL, GENDER, TITLE, STATUS, and LICENSE TYPE details.
Parameter Name: ChartSQL
Description: This SQL Query is used to select the columns that are required to create a bar chart. Here the X-axis is categorical data and the Y-Axis is measurable data.
Value:
select (case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end)
licensetype,count(userid) as "#Users" from user group by
(case when licensetype=1 then "Business User" when licensetype=3 then "Author & Analytical" end)
In the above Chart SQL Value, the “user” is the table used and with the help of this query the “licensetype” and “userid” as a number of users(#Users), details are retrieved.
Note:
- The “case” statement is used to check the conditions and return a value and if there are multiple conditions then the first condition is met and if that condition is true, it will stop reading and return the result.
- The GROUP BY clause within the select statement in SQL is used to arrange identical data into groups with the help of some functions. i.e. if a particular column has the same values in different rows then it will arrange these rows in a group.
On the successful execution of the Chart SQL, the total count of users existing in the OvalEdge database with the specific License Types are displayed.
Parameter Name: businessdescription
Description: The description is helpful for users to understand the data objects.
Value: This report provides information about the number of users and their license types in the OvalEdge Application.
Parameter Name: processed_flag
Description: Displays whether the advanced job used for the reporting framework SQL is successful or not. Here the if the advance job is successfully completed then the processed flag value is represented as 1 and if the advance job is unsuccessful then the processed flag value is represented as 0
Value: 0 or 1
Parameter Name: error_msg
Description: Displays the error message when the execution fails
Value: Invalid input
Once after developing the Reporting Framework SQL, execute the developed SQL in the MySQL Command-Line or any third-party tools which are available in your machine.
Example: Navicat
Execute the Developed SQL
To execute the developed Reporting Framework SQL using Navicat Tool, complete the following steps:
- Open the Navicat application.
- Select the Connection Type as MySQL.
- Select the appropriate Database in which the reports need to be stored.
- In the page header section, click on the Query tab and select the New Query option.
- When you click on the New Query option, the Query Editor page is displayed.
- Copy the developed Reporting Framework SQL and paste it in the Query Editor and click on the Run button. The Query will be executed and the successful message is been displayed.
Execute the Advanced Job
To execute the advanced job associated with the Reporting Framework SQL, complete the following steps:
-
Login to the OvalEdge Application.
-
Click on the
Administration module from the left menu. The sub-modules associated with the Administration are displayed. -
Click on the
Advanced Jobs. The Advanced Jobs main page with all the advanced jobs existing in the system is displayed.
-
In the Advanced Jobs data grid under the Name column, click on the search icon and search for the “Loadoereportext table for reporting framework” job used for the reporting framework.
-
Select the Job and click on the Run Advance Job button, the selected job is executed and the message “Advance Job successfully submitted with Job Id” is displayed.
-
To validate whether the Job is run successfully or not, click on the
Jobs module from the left menu. The Jobs main page with all the advanced jobs existing in the system is displayed.
-
In the Jobs data grid, verify the Job Status with the reference to the Job ID and Job Name columns.
-
Once the Job Status column is updated with SUCCESS, navigate to the Data Catalog module and view the Reports.
Note: While the advanced job is running, the new connection with Crawler ID “-1” is automatically created and displays in the Crawler Information module.
View the Reports
To view the reports built with the Reporting Framework SQL:
-
Click on the
Data Catalog module from the left menu. The Data Objects (Databases/Tables/Table Columns/Files/File Columns/Reports/Reports Columns/Queries) existing in the system are displayed. -
Click on the Reports Tab, the entire reports existing in the OvalEdge application are displayed in the Reports data grid.
-
In the Report Column, click on the search icon and enter the User License Types report name. The User License Types report name is displayed in the Reports data grid.
- In the Report Column, click on the User License Types report name. The Users License Types report is displayed.
