Introduction
While I hope that most of us by now are already aware of how to create a simple BI Publisher Report from Data Model using SQL query, but in case you are not aware you may feel free to refer this article here and it would get you started.
If one has some basic idea of SQL then its fine but if you are a newbie or an individual working primarily with OTBI reports (also referred to as a drag and drop reports) one may face challenges. In such scenarios one may make use of “Query Builder” feature. This feature allows one to simply pick choose fields from the list of available fields and prepares a basic SQL query out of it. One may then simply add conditional clauses and the complete query is ready.
We will illustrate the same in the worked example here.
Worked Out Example
In this example we will try to get few details of person’s employee and assignment tables. At this point we know that we will be using “Standard SQL” and Data Source is “ApplicationDB_HCM”.
Once we click on the Query Builder a new page will open where we have to choose the Schema Name (“FUSION” for this example) and also the table names from which we want to fetch the column details.
Once we select the tables we would need to select the columns ( using checkbox) Remember one may even use the “Check All” option (but this will only work if the total number of columns in less than 60). The tables we are using here have more than 60 columns and hence we will have to manually choose. The application screen (post selection) would appear as below:
And the moment we do this a basic SQL will be ready which can be seen in the “SQL” tab
The SQL query which gets created can be fetched from the table below:
SQL Query Created from Query Builder |
select "PER_ALL_PEOPLE_F"."PERSON_ID" as "PERSON_ID", "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE", "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE", "PER_ALL_PEOPLE_F"."BUSINESS_GROUP_ID" as "BUSINESS_GROUP_ID", "PER_ALL_PEOPLE_F"."START_DATE" as "START_DATE", "PER_ALL_PEOPLE_F"."APPLICANT_NUMBER" as "APPLICANT_NUMBER", "PER_ALL_PEOPLE_F"."PERSON_NUMBER" as "PERSON_NUMBER", "PER_ALL_PEOPLE_F"."WAIVE_DATA_PROTECT" as "WAIVE_DATA_PROTECT", "PER_ALL_ASSIGNMENTS_F"."ACTION_CODE" as "ACTION_CODE", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_ID" as "ASSIGNMENT_ID", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_NAME" as "ASSIGNMENT_NAME", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_NUMBER" as "ASSIGNMENT_NUMBER", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_SEQUENCE" as "ASSIGNMENT_SEQUENCE", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_STATUS_TYPE" as "ASSIGNMENT_STATUS_TYPE", "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_TYPE" as "ASSIGNMENT_TYPE", "PER_ALL_ASSIGNMENTS_F"."BARGAINING_UNIT_CODE" as "BARGAINING_UNIT_CODE", "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_START_DATE" as "EFFECTIVE_START_DATE_1", "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_END_DATE" as "EFFECTIVE_END_DATE_1", "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_SEQUENCE" as "EFFECTIVE_SEQUENCE", "PER_ALL_ASSIGNMENTS_F"."EMPLOYEE_CATEGORY" as "EMPLOYEE_CATEGORY", "PER_ALL_ASSIGNMENTS_F"."EMPLOYMENT_CATEGORY" as "EMPLOYMENT_CATEGORY", "PER_ALL_ASSIGNMENTS_F"."NORMAL_HOURS" as "NORMAL_HOURS", "PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD" as "NOTICE_PERIOD", "PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD_UOM" as "NOTICE_PERIOD_UOM", "PER_ALL_ASSIGNMENTS_F"."PERIOD_OF_SERVICE_ID" as "PERIOD_OF_SERVICE_ID", "PER_ALL_ASSIGNMENTS_F"."PERSON_ID" as "PERSON_ID_1" from"FUSION"."PER_ALL_ASSIGNMENTS_F" "PER_ALL_ASSIGNMENTS_F", "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F" |
And one may add additional where clause as required. For this example we will use the following where clause
Where Clause |
WHERE "PER_ALL_PEOPLE_F"."PERSON_ID" = "PER_ALL_ASSIGNMENTS_F"."PERSON_ID" AND TRUNC(SYSDATE) BETWEEN "PER_ALL_PEOPLE_F"."EFFECTIVE_START_DATE" AND "PER_ALL_PEOPLE_F"."EFFECTIVE_END_DATE" AND TRUNC(SYSDATE) BETWEEN "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_START_DATE" AND "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_END_DATE" AND "PER_ALL_PEOPLE_F"."PERSON_NUMBER" = '481' |
And once done we can see that the Data Model is created
Finally, if we click on the “Data” tab we will get the results.
Summary
So this is how one can make use of query builder feature and prepare simple SQL query based data model in Oracle ERP Cloud Application. This might serve as a good starting point for all non-techie guys to build a very basic BI Report.
Hope this is useful and you guys will be able to utilize this for self-learning or for your project deliverables.
That’s all from my side. Thanks for your time and have a nice day!
Comments
what can I say… I hesitate a lot and don't manage to get anything done.
my page ... Glamour Models: https://blinkmodelmanagement.com/atmosphere-models-services-las-vegas/private-lingeries/
RSS feed for comments to this post