SAMPLE DISCOVERER REPORT
=========================================================
Discover is a reporting Tool
It is ment for End users / Business Users
Discoverer is a wizard based tool.
Two Editions
1) Discoverer Administration Edition
2) Discoverer User Edition
Using User edition we can generate reports. This can be done by setting up admin part.
so Technical person will set up admin part. Business Areas, Tables all will created.
User will open user edition and do his job.
1) Discover 4.1 (Desktop) -> works in client server (2 tier)
2) Discover 4.i -> (3 tier architecture) applicable for Oracle Apps
3) Discover 4i Viewer This is used to run the reports and see data. It cannot be developed tool.
Adiministration Edition
=======================
Navigation
Oracle Discoverer 4 -> Adimistration Addition
connect To the Database
Components
Oracle----------EUL-------DISCOVERER
DB USER
EDITION
EUL-> Write the queries and generate the data for the user.
EUL->It called as Meta Data
EUL->It is nothing but set of tables which stores Business Area
When you are connecting to discoverer we are connecting to EUL
It is series of table which select with EUL. these are tables which store meta data related to business areas.
By default it has an EUL
Ater Connecting => EUL_US
Tools EUL Manager
Press Create New EUL
Click select an existing User
Then create acccess to public (Everybody will have access to EUL)
If EUL is created for Oralce Apps then check the last check box for Applications
Select user name CSE (i.e EUL owned by CSE)
This will create meta data tables and EUL tables CSI schema.
EUL Tables and views are created this is common whether you connect to any database like (scott, Apps etc)
Tables like EUL1, EUL2...Which will hold information of about business area.
Then Message EUL has been created sucessfully.
Connect as CSI and connect .
It will connect to EUL created.
Select Options => Default EUL-> CSI
EUL_PAYROLL EUL_HR
1) BUSINESS_AREA1 1)BUSINESS AREA3
2) BUSINESS_AREA2 2)BUSINESS AREA4
Owned by SCOTT Schema Owned by Apps
All these are public
If Scott grants access to other users then we can login through that user and create business area in EUL_PAYROLL
EUL information stored in EUL Tables.
So Business Area_hr can contain
1) HR Tables1
2) HR Tables2
In discoverer the terminology used is Business Area is collection of folders.
We can create one or more business area with payroll tables.
we can grant access on businessarea1 to HR Users.
we can grant Access on Businessarea2(Payroll) to finance users.
Creating Business Area
======================
A wizard appers for creating business area.
click on line dictionary.
selet a database link
click scott
(i.e creating business area for scott schema)
It will list all tables from scott
select emp, dept
click next
create joins
in oracle apps there is no foreign key created. so we have to create explicitly.
click next
name EMP_DEPT
Finish.
This has to tables (i.e two folders)
one folder EMP And DEPT
====================
Folders
It is a collection of items. (i.e columns in tables)
3 types of folders
1) Folder from Database
2) Custom folders -> It is not based on particular table. It is refered using select query.
3) Complex folders-> It will have data from two or more folers. The requirement is a join.
There is property sheet for all business folders and items
Click EMP_DEPT (BUSINESS AREA)
Click new folder to create complex folder
so we can drag DEPTNO from DEPT folder to complex folder
then wen we try to drag ename from EMP to complex folder it will tell no join found.
so we have to create join for complex folder.
To create join-> Insert -> Join
then drag ename from EMP folder to complex folder
It will be sucessful. Since join is available.
Custom folders :- Based on SQL Query like
select e.emp_no,e.name,e.deptno,d.dname
from emp e,
dept d
where e.deptno=d.deptno
Validate the query
custom folder is usefull where (Where Clause is compled in SQL Query)
Custom folders can write a sql query on all tables which are have access to that user
Item are nothing but columns.
if click on folder and have derieved items also
items will have properties.
we can use fuctions to create new columns in folders
function has to be registered.
Tools->register PL/SQL function
create function at back end
then new opton is selected in
Tools->Register PL/SQL function to register the function
Arguments can be entered
A condition can be written like similar to where clause in forms
For Example : Dept folder should be restricted to deptno 10 then condition can be set deptno=10
Type Mandatory or Optional
Mandatory it is applicable for all reports at end users
Optional it is optional it can be overwritten
PRIVILAGES
==========
Tools->Priveleges->select User like XYZ
1) Administration
2) User Edition
If adminstration is not required double click it.
Then apply.
Again you can select another user and grant privileges
Tools->USER/ROLE
it is seeing the same information as above.
Query Governor
-------------
It warns user about the required if warn user option is clicked.
Scheduled workbooks reports : user will be able to schedule workbooks provided schedule workbooks is clicked in privileges.
Securities
----------
Insert -> Security
Grant access of business area to users
User->Business Area
It is same as above
Tools-> Manage schedule workbooks
To see the schedule reportswhether it is completed or end up in error. to see error press view error.
Item Classes Tab ->
================
1) Lov
2) Alternative Sort
3) Drill to Detail
List of Value : It can used for parameters.
-------------
Select the item from the folder to get list of values.
Alternative sort
----------------
In alternative sort we can give list of values and we can provide sequence also.
It is for static values.
New item class
Create custom folder
Query
Select 'Monday' wk, 1 seq from dual
union
Select 'Tuesday' wk, 2 seq from dual
union
Select 'Wednesday' wk, 3 seq from dual
union
Select 'Thursday' wk, 4 seq from dual
union
Select 'Friday' wk, 5 seq from dual
union
Select 'Saturday' wk, 6 seq from dual
union
Select 'Sunday' wk, 7 seq from dual
Validate and ok
New item class
check Alternate Clause
then select WK
then Seelct seq
New Item Class
(Drill down and drill Up)
Year
Quarterly
Monthly
Weekly
Daily
These will be created in Hierarcies
Drill to details
Dept
Emp
Suppose we want to find out all employees relatd to 10
Drill to detail
If dept nos 10,20,30 then 10 is clicked it will take to all employees who belong to dept 10
so the set up is click drill to details
then select deptno from dept and then select deptno from emp.
Hierarchy
=========
Drill Down and Drill up Reports
(In table Locations)
Country
State
Place
Hierarchy is based on same table
Click Hierarchy
Create a new folder AR Schema HZ_Locations
next finish.
details are loaded into EUL
Hierarchy -> Create new -> Item Hierarchy
Click HZ_Locations
1) select country
2) select state
3) select city
based on data can be created.
Summaries
Materialized view has a physical location. It stores data compared to view. View does not store data.
It is important for analytical reports
sum(sales), avg(sales) group by country.
we create a tables
and country wise sales is kept by query.
then instead of running from query tables, the query can be run once to sumary table and report can be run from summary tables(Materialized view).
Materialized views refreshed automatically.
If data is committed then automatically it is refreshed in summary table (Materialized view)
performance improves very much.
Creating summeries
click summeries
Tools-> Summary
Wizard Appears
Options
1) Have Discoverer recommand and create the best summaries.
2) i want to specify the summaries myself
choose 2
click next
3 options appears
click -> from items
next
select deptno from dept
select sal from emp
next
in properties we can set refresh
-> on commit
Refresh -> Full or incremental
Whether the query is refreshed full or as and when it changes.
give the name of summary table
the summary table is created in end user.
Connecting to oracle application
=================================
When log through apps then it will be connected to responsibility
Use discoverer 4i to connect to oracle apps.
connection
----------
connect to application EULS
applsyspub
applsyspub
use application id. it will connect to responsibility.
================
DESKTOP EDITION
================
If aplication is configured
username
password
connect
oracle application user check box
if you don't want to logon as oracle application user then type end_us/end_us@hfcu9
if logon as oracle application user
it will list of responsibilities
Application Developer
Oracle Payable
.
.
.
.
choose on of the responsibility
work book -> similar to report
click create a new workbook
Types of workbook
1) Table
2) Crosstab
3) Page-Detail Table
4) Page-Detail Cross Tab
click table type.
In list box it asks which business area.
For example select EMP_DEPT
here all business area are listed which are given privileges
given on admin part to user
If business area is selected list of folders under will appear.
Select what ever items you want
if it is selected from emp items from dept is also allowed because there is relation ship between
EMP AND DEPT. Otherwise folder get disabled since there is no relationship between EMP AND DEPT.
Click Next
Option Hide Duplicate rows
if we want to suppress duplicate records (like distinct)
Click Next
if any conditions is to be added on any item we can add here
AND, OR, NOT Conditions can be specified.
Click Next
you can sort on what column you want.
Direction High to Low or Low To High.
Group Group Sort.
Click Next
Calculations can be added
even user defined functions can be used which are registered or any oracle pre defined function can be used.
Click Next
Finish.
The Output of the report appears.
We can see small arrow on Hiredate column since sort condition is set on Hiredate.
The output is Result set.
After developing the workbook if you want to change anything.
Sheet->Edit Sheet
once the report is being generated we can save the file
File-> Save
1) My Computer
2) Database
If it is save in the database it is accessiable to that user login only.
Open workbooks from either from desktop or from database
once we open the workbook it will ask whether to run the query then if yest it will run the report.
Opening
1) My Computer
2) Database
3) Scheduled Manger
If we select scheduled manager then it will show all the scheduled reports we can make any changes if we want.
if it is already running then we can use the job in admin and make the changes in user edition.
The definition of workbook is saved in EUL (if it is saved in database).
if it is stored on desktop it is not stored as part of EUL.
View-> SQL Inspector will help to know the exact query of the report.
Query as well as explain plan can be viewed.
If you want to save this query we can export to .sql file.
In all queries it will be 1=1 in where condition.
if it default and any other where conditions will add to it.
we can export, change the condition if required.
then file -> Import Sql.
By default it will import in tabular format.
=========================================================================
select distinct a.po_header_id,c.item_id,c.item_description,a.quantity QTY_REJECTED,b.quantity QTY_RETURNED,
a.transaction_date REJECTED_DATE,b.transaction_date RETURNED_DATE,
b.transaction_date-a.transaction_date Days,e.vendor_name
from
(select po_header_id,quantity,transaction_type,transaction_date from po.rcv_transactions where
transaction_type in ('REJECT')) a,
(select po_header_id,quantity,transaction_type,transaction_date from po.rcv_transactions where
transaction_type in ('RETURN TO VENDOR','ACCEPT','RECEIVE')) b,
po.po_lines_all c,
po.po_headers_all d,
po.po_vendors e
where-- a.po_header_id=b.po_header_id and
a.po_header_id=c.po_header_id and
a.po_header_id=d.po_header_id and
d.vendor_id=e.vendor_id
========================
Grouping the Records:
=====================
If we would like to apply any group by clause then we will select Column name
=>Right Click => Group Sort which will group the records based on the column
Attach the Title
================
goto the Toole menu Options field =Select the Tab called Table check the
check box called Title => Double click on that andeter the Title.
Select Insert button to insert the Data , Time and as well as Parameter Values.
Attach the parameters:
=====================
Select Tools Menu Parameters option
select the new button select the column name for comparision select the relation
and specifuy the condition.
To Display the parameter value in the Title then goto title Select Insert button
insert the Parameter name .
Item Classes :
==============
Item class is nothing but list of values which will be attach to the parameter to
select single value or multiple values.
Goto Admin Edition select Item classes tab =>Right Click select Item classes
select the Folder . and select the column name
so that we can use that LOV for that folder and as well as for the selected column.
Go To DeskTop Edition attach the parameter
Check the check box called Allow users to enter multiple values.
so that user can select multiple values at a time otherwise single value.
======================
Attach the Parameters and Conditions:
===================== ================
1)Goto Toole Menu =>Parameters=>New
Select the Column Name and Define the Parameter
Use Parameter in the Condition:
================================
1)Goto Tools Meneu=>Conditions=>New
Selct Condition , Item And Value
2)If we would like to provide multiple conditions by using either AND (or) OR
we will use the Add Button
3)Double click on the AND it will be changed to OR
Display Total:
==============
1)Goto Tools=>Total option => New button
select group function and Column Name
select either Grand Total or Sub Total (If it subtotal At what level)
2)Enter the Prompt as per that value will be displayed
Disply Formula Columns:
=======================
1)Toolsmenu=>Calculations options=>New
select the Function an pass the Aruments select column in the Workbook
Layout
Register UserDefined Functions:
===============================
1)Goto SQl Prompt create Function with return value compile succesfully.
2)Goto Admin Edition => Toole=>Register PL/SQL Function=>
Enter the Function Name (If package function enter package name also)
3)Goto Arguments tab enter the Parameter details also.
4)Select Validiate button and Ok button.
5)goto Desktop Edition Tools=>Calculations=>Allfunction
where we can find the function whatever we have registered.
6)Select the function pass the parameter we can get the result.
===============================================
Sort Options :
=============
1)Right Click on the Workbook column name => Item Properties =>to change the Column Name
=> Group Sort =>To make grouping
=> Sort Low to High =>Assending order
=> Sort high to Low =>Desending order
=> Foramt Data => To format the Data
=> Format Heading => Format the Heading
=> Format Exception =>To give the Format Conditions
Add Title to the Workbook:
=======================
1)Goto Tools Menu=> Options=> Table Tab=>Check the Title Check Box
Double click on the Title Text enter the Title
2)Select the Button called Insert button to Inser Date , Time, Parameter valued and so on
Exporting data into EXCEL SHEET
============================
File->Export->change the path and For Edit purpose File->Edit;
Join and Outerjoin
==============
po_vendors--->Right click--->join
Option------>if outer join is Required
Modification==========>Right click modifications
New custom Folder====>Admin ===>Right click====>New custom Folder
Back ground logo
==============>
Take *.bmp===>format===>sheet===>set back ground;
clear back ground===>right click on that select clear back ground
Sharing to User
=============f=>
File===>filemanage==========>sharing option==========>drag and drop
Date format and extra
==================
Tools===>options====>Format
Drill down
==========>Related to more that one item
work book===>right click==>drill down
previlieges
===========>
Tools===>previlieges
How to move one instance to anothe instance
===================================>
File===>export==>give the instance path
Hierarchy(Date)
==========>
Admin Edition =====>Hierarchy Wizard
Import Excel SheeT Data
====================>
File==>Import
=========================================================================
|
Report Name
|
XX Current
Employees
|
||
|
Requestor
|
Rajesh Pandita
|
Developer
|
Rajesh Pandita
|
|
Functional Area
|
HRMS
|
||
|
Report Description
|
To List all
the Current Employees as on Date
|
||
|
Business Need
/ Case
|
To List all
the Current Employees as on Date.
|
||
|
Number of Users
|
|
||
|
Assumptions(if
any)
|
List assumptions (if any) made regarding the report or the business
environment.
|
||
|
Report
Frequency
|
Weekly / Daily / Monthly
|
||
Report
Layout (Attach Report layout below)
|
Emp. No.
|
Employee Name
|
Date of Joining
|
Date of Birth
|
Buiness Unit
|
Location
|
Function
|
Sub Function
|
Sub Sub Function
|
Band
|
Level / Designation
|
Title
|
|
Previous Experience
|
Total Experience
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Worksheet
1 –Current Employees
|
Workbook Filename
|
BTVL_CURRENT_EMPLOYEE.DIS
|
|
Workbook Name
|
BTVL_CURRENT_EMPLOYEE
|
|
Worksheet Name
|
BTVL
Current Employees
|
|
Developer
|
Rajesh Pandita
|
Report Title |
Current Employee Report
|
|||
Data Elements/Fields Needed (Position in Report - list in order from left to right) |
EUL
Folder. EUL Element
|
Algorithm,
Additional Comments, etc.
|
||
|
papf.PERSON_ID
papf.Employee_Number
pay.Payroll_Name
papf.LAST_NAME
papf.FIRST_NAME
papf.MIDDLE_NAMES
papf.TITLE
papf.FULL_NAME
papf.START_DATE
papf.EFFECTIVE_START_ papf.EFFECTIVE_END_DATE ppt.user_person_type
papf.DATE_OF_BIRTH
papf.TOWN_OF_BIRTH
papf.REGION_OF_BIRTH papf.COUNTRY_OF_BIRTH papf.EMAIL_ADDRESS
papf.MARITAL_STATUS
papf.EMAIL_ADDRESS
papf.NATIONALITY
papf.SEX
papf.blood_type
papf.attribute2
papf.attribute4
papf.registered_disabled_flag
|
All the items
belongs to Custom folder
BTVL_CURRENT_EMPLOYEES
|
|||
Calculated Columns |
|
|
||
|
|
|
|||
|
|
|
|||
|
|
|
|||
|
|
|
|||
Parameters
(User selectable)
|
The parameters are mentioned in the screen
shot.
|
|
||
|
|
|
|||
Parameters |
 |
|||
Conditions |
 |
|||
|
|
|
|||
|
|
|
|||
|
|
||||
Sort Order |
See in the
screen shot
 |
|
||
|
|
|
|||
|
|
||||
|
|
|
|||
Totals, Subtotals |
|
|||
|
|
||||
Sample Report output |
 |
|||
Worksheet 1 – EUL Customization
|
Database Instance
|
PROD
|
||
|
Workbook Name
|
BTVL_CURRENT_EMPLOYEE
|
||
|
Workbook File Name
|
BTVL_CURRENT_EMPLOYEE
|
||
|
Developer/Int. Lead
|
Rajesh
Pandita
|
Phone Number
|
|
Revisions
(mark revisions in different color)
|
Revision No.
|
Date
|
Notes
|
|
#
|
25-11-2005
|
Initial Creation
|
|
#
|
25-11-2005
|
Added new column in the layout
|
|
|
|
|
|
|
||
|
|
|
|
|
|
|
|
For multiple
worksheets (if applicable), please copy this section and fill out for each
worksheet.
|
Worksheet Name
|
XX_CURRENT_EMPLOYEE
|
Custom Folder
and Database Functions (attach SQL on the following page)
|
Business Area
|
Custom Folder Name
|
Special Instructions
|
|
Oracle Human Resources
|
XX_ CURRENT_EMPLOYEES
|
|
|
|
|
|
Tables Being
used by Custom folder.
|
Business Area
|
Database Folder Name
|
Schema Name
|
Table Name
|
|
Oracle Human Resources
|
XX_ CURRENT_EMPLOYEES
|
|
per_all_people_f papf,
|
|
|
|
|
per_person_types ppt,
|
|
|
|
|
PER_ALL_ASSIGNMENTS_F PA
|
|
|
|
|
PAY_ALL_PAYROLLS_F PAY,
|
|
|
|
|
per_person_analyses ana,
|
|
|
|
|
per_analysis_criteria anc,
|
|
|
|
|
PER_SPECIAL_INFO_TYPES_V PSI
|
|
|
|
|
per_all_people_f papf,
|
|
|
|
|
per_person_types ppt,
|
|
|
|
|
PER_ALL_ASSIGNMENTS_F PA ,
|
|
|
|
|
PAY_ALL_PAYROLLS_F PAY,
|
|
per_person_analyses ana
|
|||
|
|
|||
|
|
|||
|
|
List of Values
(LOVs)
|
Business Area
|
Folder Name
|
Item using LOVs
|
Item generating the LOVs
|
|
Oracle Human Resources
|
XX_ Payroll
|
Payroll Name
|
Select
Distinct Payroll_name from
PAY_ALL_PAYROLLS_F
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Business Area Name
|
Folder Name
|
Item Name(s) to be visible via EUL
|
|
Bharti HRMS
|
XX_
CURRENT_EMPLOYEES
|
papf.PERSON_ID
papf.Employee_Number
pay.Payroll_Name
papf.LAST_NAME
papf.FIRST_NAME
papf.MIDDLE_NAMES
papf.TITLE
papf.FULL_NAME
papf.START_DATE
papf.EFFECTIVE_START_ papf.EFFECTIVE_END_DATE ppt.user_person_type
papf.DATE_OF_BIRTH
papf.TOWN_OF_BIRTH
papf.REGION_OF_BIRTH papf.COUNTRY_OF_BIRTH papf.EMAIL_ADDRESS
papf.MARITAL_STATUS
papf.EMAIL_ADDRESS
papf.NATIONALITY
papf.SEX
papf.blood_type
papf.attribute2
papf.attribute4
papf.registered_disabled_flag
|
|
|
|
|
SQL Code:
papf.PERSON_ID ,
papf.Employee_Number,
pay.Payroll_Name,
papf.LAST_NAME ,
papf.FIRST_NAME ,
papf.MIDDLE_NAMES,
papf.TITLE,
papf.FULL_NAME,
papf.START_DATE "Hire Date"
,
papf.EFFECTIVE_START_DATE "Effective From",
papf.EFFECTIVE_END_DATE "Effective To" ,
ppt.user_person_type "Type",
papf.DATE_OF_BIRTH ,
papf.TOWN_OF_BIRTH "Place of Birth",
papf.REGION_OF_BIRTH "State of Birth",
papf.COUNTRY_OF_BIRTH "Country of Birth",
papf.EMAIL_ADDRESS
,
papf.MARITAL_STATUS ,
papf.EMAIL_ADDRESS "E-Mail",
papf.NATIONALITY ,
papf.SEX "Gender",
papf.blood_type "Blood Type",
papf.attribute2 "Pre-Migration Emp Num",
papf.attribute4 "Date of Marriage",
papf.registered_disabled_flag "Registered Disabled",
anc.segment1 "Type of Statutory",
anc.segment4 "Statutory Num",
papf.attribute1 "Driver license No",
Mt(papf.person_id) "Mother Tongue"
--NULL "Mother Tongue"
FROM
per_all_people_f papf,
per_person_types ppt,
PER_ALL_ASSIGNMENTS_F PA ,
PAY_ALL_PAYROLLS_F PAY,
per_person_analyses ana,
per_analysis_criteria anc,
PER_SPECIAL_INFO_TYPES_V PSI
WHERE
papf.person_type_id = ppt.PERSON_TYPE_ID (+) AND
PA.PAYROLL_ID = PAY.PAYROLL_ID (+) AND
PA.person_id (+) = papf.person_id AND
ana.person_id (+) = papf.PERSON_ID AND
ana.analysis_criteria_id = anc.analysis_criteria_id (+)
AND
psi.id_flex_num (+) = anc.id_flex_num AND
psi.NAME = 'Statutory Details'
AND
AND papf.current_employee_flag = 'Y'
UNION
SELECT DISTINCT
papf.PERSON_ID ,
papf.Employee_Number,
pay.Payroll_Name,
papf.LAST_NAME ,
papf.FIRST_NAME ,
papf.MIDDLE_NAMES,
papf.TITLE,
papf.FULL_NAME,
papf.START_DATE "Hire Date"
,
papf.EFFECTIVE_START_DATE "Effective From",
papf.EFFECTIVE_END_DATE "Effective To" ,
ppt.user_person_type "Type",
papf.DATE_OF_BIRTH ,
papf.TOWN_OF_BIRTH "Place of Birth",
papf.REGION_OF_BIRTH "State of Birth",
papf.COUNTRY_OF_BIRTH "Country of Birth",
papf.EMAIL_ADDRESS
,
papf.MARITAL_STATUS ,
papf.EMAIL_ADDRESS "E-Mail",
papf.NATIONALITY ,
papf.SEX "Gender",
papf.blood_type "Blood Type",
papf.attribute2 "Pre-Migration Emp Num",
papf.attribute4 "Date of Marriage",
papf.registered_disabled_flag "Registered Disabled",
NULL "Type of Statutory",
NULL "Statutory Num",
papf.attribute1 "Driver license No",
NULL "Mother Tongue"
FROM
per_all_people_f papf,
per_person_types ppt,
PER_ALL_ASSIGNMENTS_F PA ,
PAY_ALL_PAYROLLS_F PAY
WHERE
papf.person_type_id = ppt.PERSON_TYPE_ID AND
PA.PAYROLL_ID = PAY.PAYROLL_ID (+) AND
PA.person_id (+) = papf.person_id AND
TRUNC(SYSDATE)
BETWEEN pa.EFFECTIVE_START_DATE AND pa.EFFECTIVE_END_DATE
AND NOT EXISTS
(
SELECT
1
FROM
per_person_analyses ana,
per_analysis_criteria anc,
PER_SPECIAL_INFO_TYPES_V PSI
WHERE
ana.person_id (+) = papf.PERSON_ID AND
ana.analysis_criteria_id = anc.analysis_criteria_id (+)
AND
psi.id_flex_num (+) = anc.id_flex_num AND
psi.NAME IN('Statutory Details','Additional Employee Details' ))
Coding Standards for Custom SQL
·
Custom folder naming conventions: XX_
CURRENT_EMPLOYEES (XX Can be Business decided like
for Bharti BTVL_
CURRENT_EMPLOYEES)
·
Enclose comments using /*……*/.
·
Must specify alias names for
calculated columns (e.g., SELECT
a.columna || b.columnb SOME_ALIAS_NAME).
·
Ensure alias names in select
statements have unique names (no duplicates).
·
Use custom folders instead of
creating database views.
·
Avoid hard-coding values in the
SQL--these should be added in the workbook.
·
Ensure filters/conditions/parameters
are added via the workbook to limit the number of rows retrieved.
·
Include as many columns as necessary
in the SELECT in order to provide multiple solutions (i.e., multiple worksheets
/workbooks), if possible.
·
Performance considerations (e.g.,
use appropriate joins, key/index fields, etc.)
Discover is a reporting Tool
It is ment for End users / Business Users
Discoverer is a wizard based tool.
Two Editions
1) Discoverer Administration Edition
2) Discoverer User Edition
Using User edition we can generate reports. This can be done by setting up admin part.
so Technical person will set up admin part. Business Areas, Tables all will created.
User will open user edition and do his job.
1) Discover 4.1 (Desktop) -> works in client server (2 tier)
2) Discover 4.i -> (3 tier architecture) applicable for Oracle Apps
3) Discover 4i Viewer This is used to run the reports and see data. It cannot be developed tool.
Adiministration Edition
=======================
Navigation
Oracle Discoverer 4 -> Adimistration Addition
connect To the Database
Components
Oracle----------EUL-------DISCOVERER
DB USER
EDITION
EUL-> Write the queries and generate the data for the user.
EUL->It called as Meta Data
EUL->It is nothing but set of tables which stores Business Area
When you are connecting to discoverer we are connecting to EUL
It is series of table which select with EUL. these are tables which store meta data related to business areas.
By default it has an EUL
Ater Connecting => EUL_US
Tools EUL Manager
Press Create New EUL
Click select an existing User
Then create acccess to public (Everybody will have access to EUL)
If EUL is created for Oralce Apps then check the last check box for Applications
Select user name CSE (i.e EUL owned by CSE)
This will create meta data tables and EUL tables CSI schema.
EUL Tables and views are created this is common whether you connect to any database like (scott, Apps etc)
Tables like EUL1, EUL2...Which will hold information of about business area.
Then Message EUL has been created sucessfully.
Connect as CSI and connect .
It will connect to EUL created.
Select Options => Default EUL-> CSI
EUL_PAYROLL EUL_HR
1) BUSINESS_AREA1 1)BUSINESS AREA3
2) BUSINESS_AREA2 2)BUSINESS AREA4
Owned by SCOTT Schema Owned by Apps
All these are public
If Scott grants access to other users then we can login through that user and create business area in EUL_PAYROLL
EUL information stored in EUL Tables.
So Business Area_hr can contain
1) HR Tables1
2) HR Tables2
In discoverer the terminology used is Business Area is collection of folders.
We can create one or more business area with payroll tables.
we can grant access on businessarea1 to HR Users.
we can grant Access on Businessarea2(Payroll) to finance users.
Creating Business Area
======================
A wizard appers for creating business area.
click on line dictionary.
selet a database link
click scott
(i.e creating business area for scott schema)
It will list all tables from scott
select emp, dept
click next
create joins
in oracle apps there is no foreign key created. so we have to create explicitly.
click next
name EMP_DEPT
Finish.
This has to tables (i.e two folders)
one folder EMP And DEPT
====================
Folders
It is a collection of items. (i.e columns in tables)
3 types of folders
1) Folder from Database
2) Custom folders -> It is not based on particular table. It is refered using select query.
3) Complex folders-> It will have data from two or more folers. The requirement is a join.
There is property sheet for all business folders and items
Click EMP_DEPT (BUSINESS AREA)
Click new folder to create complex folder
so we can drag DEPTNO from DEPT folder to complex folder
then wen we try to drag ename from EMP to complex folder it will tell no join found.
so we have to create join for complex folder.
To create join-> Insert -> Join
then drag ename from EMP folder to complex folder
It will be sucessful. Since join is available.
Custom folders :- Based on SQL Query like
select e.emp_no,e.name,e.deptno,d.dname
from emp e,
dept d
where e.deptno=d.deptno
Validate the query
custom folder is usefull where (Where Clause is compled in SQL Query)
Custom folders can write a sql query on all tables which are have access to that user
Item are nothing but columns.
if click on folder and have derieved items also
items will have properties.
we can use fuctions to create new columns in folders
function has to be registered.
Tools->register PL/SQL function
create function at back end
then new opton is selected in
Tools->Register PL/SQL function to register the function
Arguments can be entered
A condition can be written like similar to where clause in forms
For Example : Dept folder should be restricted to deptno 10 then condition can be set deptno=10
Type Mandatory or Optional
Mandatory it is applicable for all reports at end users
Optional it is optional it can be overwritten
PRIVILAGES
==========
Tools->Priveleges->select User like XYZ
1) Administration
2) User Edition
If adminstration is not required double click it.
Then apply.
Again you can select another user and grant privileges
Tools->USER/ROLE
it is seeing the same information as above.
Query Governor
-------------
It warns user about the required if warn user option is clicked.
Scheduled workbooks reports : user will be able to schedule workbooks provided schedule workbooks is clicked in privileges.
Securities
----------
Insert -> Security
Grant access of business area to users
User->Business Area
It is same as above
Tools-> Manage schedule workbooks
To see the schedule reportswhether it is completed or end up in error. to see error press view error.
Item Classes Tab ->
================
1) Lov
2) Alternative Sort
3) Drill to Detail
List of Value : It can used for parameters.
-------------
Select the item from the folder to get list of values.
Alternative sort
----------------
In alternative sort we can give list of values and we can provide sequence also.
It is for static values.
New item class
Create custom folder
Query
Select 'Monday' wk, 1 seq from dual
union
Select 'Tuesday' wk, 2 seq from dual
union
Select 'Wednesday' wk, 3 seq from dual
union
Select 'Thursday' wk, 4 seq from dual
union
Select 'Friday' wk, 5 seq from dual
union
Select 'Saturday' wk, 6 seq from dual
union
Select 'Sunday' wk, 7 seq from dual
Validate and ok
New item class
check Alternate Clause
then select WK
then Seelct seq
New Item Class
(Drill down and drill Up)
Year
Quarterly
Monthly
Weekly
Daily
These will be created in Hierarcies
Drill to details
Dept
Emp
Suppose we want to find out all employees relatd to 10
Drill to detail
If dept nos 10,20,30 then 10 is clicked it will take to all employees who belong to dept 10
so the set up is click drill to details
then select deptno from dept and then select deptno from emp.
Hierarchy
=========
Drill Down and Drill up Reports
(In table Locations)
Country
State
Place
Hierarchy is based on same table
Click Hierarchy
Create a new folder AR Schema HZ_Locations
next finish.
details are loaded into EUL
Hierarchy -> Create new -> Item Hierarchy
Click HZ_Locations
1) select country
2) select state
3) select city
based on data can be created.
Summaries
Materialized view has a physical location. It stores data compared to view. View does not store data.
It is important for analytical reports
sum(sales), avg(sales) group by country.
we create a tables
and country wise sales is kept by query.
then instead of running from query tables, the query can be run once to sumary table and report can be run from summary tables(Materialized view).
Materialized views refreshed automatically.
If data is committed then automatically it is refreshed in summary table (Materialized view)
performance improves very much.
Creating summeries
click summeries
Tools-> Summary
Wizard Appears
Options
1) Have Discoverer recommand and create the best summaries.
2) i want to specify the summaries myself
choose 2
click next
3 options appears
click -> from items
next
select deptno from dept
select sal from emp
next
in properties we can set refresh
-> on commit
Refresh -> Full or incremental
Whether the query is refreshed full or as and when it changes.
give the name of summary table
the summary table is created in end user.
Connecting to oracle application
=================================
When log through apps then it will be connected to responsibility
Use discoverer 4i to connect to oracle apps.
connection
----------
connect to application EULS
applsyspub
applsyspub
use application id. it will connect to responsibility.
================
DESKTOP EDITION
================
If aplication is configured
username
password
connect
oracle application user check box
if you don't want to logon as oracle application user then type end_us/end_us@hfcu9
if logon as oracle application user
it will list of responsibilities
Application Developer
Oracle Payable
.
.
.
.
choose on of the responsibility
work book -> similar to report
click create a new workbook
Types of workbook
1) Table
2) Crosstab
3) Page-Detail Table
4) Page-Detail Cross Tab
click table type.
In list box it asks which business area.
For example select EMP_DEPT
here all business area are listed which are given privileges
given on admin part to user
If business area is selected list of folders under will appear.
Select what ever items you want
if it is selected from emp items from dept is also allowed because there is relation ship between
EMP AND DEPT. Otherwise folder get disabled since there is no relationship between EMP AND DEPT.
Click Next
Option Hide Duplicate rows
if we want to suppress duplicate records (like distinct)
Click Next
if any conditions is to be added on any item we can add here
AND, OR, NOT Conditions can be specified.
Click Next
you can sort on what column you want.
Direction High to Low or Low To High.
Group Group Sort.
Click Next
Calculations can be added
even user defined functions can be used which are registered or any oracle pre defined function can be used.
Click Next
Finish.
The Output of the report appears.
We can see small arrow on Hiredate column since sort condition is set on Hiredate.
The output is Result set.
After developing the workbook if you want to change anything.
Sheet->Edit Sheet
once the report is being generated we can save the file
File-> Save
1) My Computer
2) Database
If it is save in the database it is accessiable to that user login only.
Open workbooks from either from desktop or from database
once we open the workbook it will ask whether to run the query then if yest it will run the report.
Opening
1) My Computer
2) Database
3) Scheduled Manger
If we select scheduled manager then it will show all the scheduled reports we can make any changes if we want.
if it is already running then we can use the job in admin and make the changes in user edition.
The definition of workbook is saved in EUL (if it is saved in database).
if it is stored on desktop it is not stored as part of EUL.
View-> SQL Inspector will help to know the exact query of the report.
Query as well as explain plan can be viewed.
If you want to save this query we can export to .sql file.
In all queries it will be 1=1 in where condition.
if it default and any other where conditions will add to it.
we can export, change the condition if required.
then file -> Import Sql.
By default it will import in tabular format.
=========================================================================
select distinct a.po_header_id,c.item_id,c.item_description,a.quantity QTY_REJECTED,b.quantity QTY_RETURNED,
a.transaction_date REJECTED_DATE,b.transaction_date RETURNED_DATE,
b.transaction_date-a.transaction_date Days,e.vendor_name
from
(select po_header_id,quantity,transaction_type,transaction_date from po.rcv_transactions where
transaction_type in ('REJECT')) a,
(select po_header_id,quantity,transaction_type,transaction_date from po.rcv_transactions where
transaction_type in ('RETURN TO VENDOR','ACCEPT','RECEIVE')) b,
po.po_lines_all c,
po.po_headers_all d,
po.po_vendors e
where-- a.po_header_id=b.po_header_id and
a.po_header_id=c.po_header_id and
a.po_header_id=d.po_header_id and
d.vendor_id=e.vendor_id
========================
Grouping the Records:
=====================
If we would like to apply any group by clause then we will select Column name
=>Right Click => Group Sort which will group the records based on the column
Attach the Title
================
goto the Toole menu Options field =Select the Tab called Table check the
check box called Title => Double click on that andeter the Title.
Select Insert button to insert the Data , Time and as well as Parameter Values.
Attach the parameters:
=====================
Select Tools Menu Parameters option
select the new button select the column name for comparision select the relation
and specifuy the condition.
To Display the parameter value in the Title then goto title Select Insert button
insert the Parameter name .
Item Classes :
==============
Item class is nothing but list of values which will be attach to the parameter to
select single value or multiple values.
Goto Admin Edition select Item classes tab =>Right Click select Item classes
select the Folder . and select the column name
so that we can use that LOV for that folder and as well as for the selected column.
Go To DeskTop Edition attach the parameter
Check the check box called Allow users to enter multiple values.
so that user can select multiple values at a time otherwise single value.
======================
Attach the Parameters and Conditions:
===================== ================
1)Goto Toole Menu =>Parameters=>New
Select the Column Name and Define the Parameter
Use Parameter in the Condition:
================================
1)Goto Tools Meneu=>Conditions=>New
Selct Condition , Item And Value
2)If we would like to provide multiple conditions by using either AND (or) OR
we will use the Add Button
3)Double click on the AND it will be changed to OR
Display Total:
==============
1)Goto Tools=>Total option => New button
select group function and Column Name
select either Grand Total or Sub Total (If it subtotal At what level)
2)Enter the Prompt as per that value will be displayed
Disply Formula Columns:
=======================
1)Toolsmenu=>Calculations options=>New
select the Function an pass the Aruments select column in the Workbook
Layout
Register UserDefined Functions:
===============================
1)Goto SQl Prompt create Function with return value compile succesfully.
2)Goto Admin Edition => Toole=>Register PL/SQL Function=>
Enter the Function Name (If package function enter package name also)
3)Goto Arguments tab enter the Parameter details also.
4)Select Validiate button and Ok button.
5)goto Desktop Edition Tools=>Calculations=>Allfunction
where we can find the function whatever we have registered.
6)Select the function pass the parameter we can get the result.
===============================================
Sort Options :
=============
1)Right Click on the Workbook column name => Item Properties =>to change the Column Name
=> Group Sort =>To make grouping
=> Sort Low to High =>Assending order
=> Sort high to Low =>Desending order
=> Foramt Data => To format the Data
=> Format Heading => Format the Heading
=> Format Exception =>To give the Format Conditions
Add Title to the Workbook:
=======================
1)Goto Tools Menu=> Options=> Table Tab=>Check the Title Check Box
Double click on the Title Text enter the Title
2)Select the Button called Insert button to Inser Date , Time, Parameter valued and so on
Exporting data into EXCEL SHEET
============================
File->Export->change the path and For Edit purpose File->Edit;
Join and Outerjoin
==============
po_vendors--->Right click--->join
Option------>if outer join is Required
Modification==========>Right click modifications
New custom Folder====>Admin ===>Right click====>New custom Folder
Back ground logo
==============>
Take *.bmp===>format===>sheet===>set back ground;
clear back ground===>right click on that select clear back ground
Sharing to User
=============f=>
File===>filemanage==========>sharing option==========>drag and drop
Date format and extra
==================
Tools===>options====>Format
Drill down
==========>Related to more that one item
work book===>right click==>drill down
previlieges
===========>
Tools===>previlieges
How to move one instance to anothe instance
===================================>
File===>export==>give the instance path
Hierarchy(Date)
==========>
Admin Edition =====>Hierarchy Wizard
Import Excel SheeT Data
====================>
File==>Import
=========================================================================
No comments:
Post a Comment