Wednesday, February 4, 2015

Oracle RDF Reports Overview with Examples.


Report Builder is one of the Oracle GUI Tool.

By using this we can develop simple and Complex reports

There are two ways we can develop the report
1)  Wizard base
2)  Manual

While developing Report we have two stages.
1) Data Model
2) Layout model

Data Model :This section shows the logical structure of the report.
It contains below.
System Parameters.
User Parameters.
Queries.
Groups.
Formula Columns.
Summary Columns.
Place Holder Columns.

Layout Model Objects:

1)Text : Will be used to display the standard text information
         like titles,prompts,headings,Address……………

2)Frame : Will be used to display the layout objects only one time
           like totals,titles,Headings and so on

3)Repeating Frame: Will be used to display the objects multiple times
           like database columns,Sub Totals,Page Totals…….

4)Field  : Will be used to display the Data base columns,variables and            so on.

                First page :  Employee Information Report

                Second Page :  Empno Ename  Sal  hiredate   Comm

                 Last page  : End of the report

Summary Column used for calculating summary information like sum, average etc like Average,count,min,max and so on.

1)Group level   :               Executed for every record in the group
2)Report Level  :             Executed only once for entire report.

This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.

Ex:
Total    Emp:
Total    Sal:
Min Salary  :
Max Salary  :

In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.


Formula Columns : When we want implement some logic and return the  value then we will use formula columns.
It is a PL/SQL Function. where we can write PL/SQL code we can return only one value.
1)Group Level
2)Report Level


Formula columns are generally preceded by CF_ to distinguish from other columns. Column names or parameters with a prefix ‘:’  in formula column are considers as input to a formula column.

Examples:

  1. Comparison of data in two different columns/variables and perform some action.
  2. When you need to use any If-else block.
  3. To execute some sql query to find out email-address of a party using party_id
Place Holder Columns:

A datatype which stores some value and we can use this across the  report.
It will work like Global variable in the reports.If we want to return more than one value from the Formula Columns then it will be used.

We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.

Triggers:
Format Trigger
Validation Trigger
Action Trigger
Report Triggers

Format Trigger : To Hide or display the layout objects dynamically it will be used.
layout objects means frame, repeating frame, field,…………

Action Triggers: If we want to develop drill down reports then we will use Action Triggers.
Place the Button in the layout when user press the button we can  execute some PL/SQL code.

Report Triggers: 5 Types which will fired automatically when we run the report.

ü  Before Parameter Form
ü  after Parameter Form
ü  Before report
ü  Between pages
ü  After Report


Before Parameter Form : Will Execute before the parameter form is going to  open.
To assign any default values for the parameter.

After Parameter Form  : After the Parameters are entered in the form and submit it will be executed.To change the Parameter values dynamically And To populate the Lexical parameter values.

Before Report : It will be executed before report is executing and select data from database.

Between Pages : When cursor moves from one page to another page it will be executed .
                It will be executed (n-1) times.

After Report : after completion of the report. once the output is reach the  destination (Printer , file, fax , e-mail) then it will be executed.

Parameters :

ü  System Parameters
ü  User Defined Parameters                         
ü  Bind
ü  Lexical

User Parameters:
Bind parameters will used to pass the the value into the query in the WHERE Clause

Lexical parameters will be used to replace the string in side of the query in any clause.

SELECT * FROM EMP &P_ORDER_BY                        

Ex: 
After Parameter Form Trigger

If user pass the deptno then select between that otherwise select all the dept employees data

SELECT *
FROM EMP
WHERE DEPTNO BETWEEN P_FROM AND P _TO

If P_from is entered then it should retrieve all dept from the specified dept

if p_to is entered then it would retrieve up to specified deptno.


WHERE DEPTNO>=:P_FROM

WHERE DEPTNO<=:P_TO



BindLexical
ü  To pass the Values into the WHERE Clause.
ü  We will use the symbol “:”
ü  Can be used only in the WHERE Clause.
ü  Any Data Type.
ü  Values will be passed in the Parameter Form.
ü  To Replace the String in the Query.
ü  We will use the symbol “&”
ü  In any Clause we can use this.
ü  Must be Character.
ü  Values will be assigned dynamically form                                     in the After Parameter Form Trigger.


System Parameters: 

DESFORMAT     :              Html, pdf…..
DESNAME          :               Name of the Printer, E-mail,Fax,File
DESTYPE            :              File, Screen, Print, Fax, E-mail
MODE                  :               Bitmap, Character


Confine Mode: Lock mode if it is on then we can not take the child object out of the Parent Object. If it is  off then we can take out of the parent.

Flex Mode:       If Flex mode is on . If we Incr/Decr the child object automatically parent object also incr/decr.  If it is off then Only child object will be decr/Incr.

Program Unit : 
  Is a PL/SQL objects(Procedures, Functions, Packages) which will be stored in the Report we can use only in the Current Report.


Program unitsPL/SQL Objects
ü  Will be stored in the file.
ü  Can be used only in the Current Report.
ü  Improve the Performance.          
ü  Stored in the Database
ü  We can use any report.
ü  It takes more time


Libraries : Group of Program units and Libraries We can attach the Attach the Library to another report and use the Program units.

We can not attach the Program units to another reports directley that’s why we will PL/SQL Library to attach.

.pll  –      Source Code –                    Program Link Library
.plx  –     Executable Code –           Program Link Executable

Report Registration Steps:

1) Develop the report (.rdf) as per client requirement by using Reports  Builder

Step – 1:              select “Built a new Report manually” option and click OK button.

Step -2:  Now you are in Data Model, select SQL query tool and write required query.

Step – 3: Click on OK and connect to apps/apps@VISR12

Step- 4 : As our query is having parameters its creating two bind parameters

Step – 5:  Go to Paper layout and design Frame, then keep Labels and then design Repeating Frame with in Frame and keep  Fields with in Repeating Frames.
Go to Frame Properties                                set Vertical Elasticity as Expand
Go to Repeating Frame Properties           set like following

Go to Field Properties set like below.

Step – 6: Run the form and enter details like below.




2) Move the report from the local machine in to the server by using WINSCP
A. CUS_TOP\11.5.0\reports\US .rdf (or)
Reports Deployment 
Note:  Plz notice that as we don’t have CUST_TOP in our server, we are feeling PO_TOP as  CUST_TOP
for our practice. But in real time we will use only CUST_TOP which is named with Client Name.
Step – 1: open Putty and type below commands to know path of Custom top

Step – 2: Open WinSCP to reach the Custom Path through GUI

Step – 3: Deploy the test.rdf into Custom top Location.
3) Select System Administrator
a. Create Executable
b. Executable name
c. Application Name
d. Execution Method
e. Report (.rdf) file name

Step – 4: Create Executable by linking to test.rdf

Executable File name should be Report name which is available in server only.
4) Create Concurrent Program and attach :
concurrent program is Instance of executable file along with input parameters and incompatible programs.  Development of sample report and Move  to oracle apps and execution.
a. Executable
b. Parameters
c. Incompatibilities
Step – 5: Create Concurrent Program, while creating provide Executable name to this.

Step – 6:  Click on Parameters Tab and go to screen to provide details like below screen. Token values should be like parameter from Report Builder.

5) Create Request group and attach Concurrent Programe

Step – 7: Go to Request Group screen , create  group, or open existing group to attach our Concurrent Program Name.

6) Create Responsibility
a. Request Group – Collection of Concurrent Programs
b. Data Group – Collection of User IDs
c. Menu – Collection of forms
Step – 7: Go to Responsibility Screen and create one responsibility and attach our Request Group to this Responsibility.

7) Create user attach Responsibility to the user
Step – 9: Go to User Screen and attach our responsibility to existing Business User.
Run the Report through SRS window.

  1. Go to File —-à switch Responsibility —–à select our responsibility
  2. Go to View—à Request —à find then we can fine below screen.
  3.  
 Click on Submit New Request  button.

 Click on ok button to submit single request. Then call our report to run SRS window.

 Provide the Values for Parameters  before click on OK button.

 Click on No button for not to submit another request.
 Click on Refresh Button to make our report into complete Normal Stage.

  Types of executables : These are programs

Oracle Application supports 12 types of languages
1)      Oracle Reports                  à   .rdf
2)      Sql * Plus                             à   .sql
3)      PL/SQL Procedures         à   .proc
4)      Sql * Loader                       à   .ctl
5)      Unix shell script                à   .sh
6)      C – Language                     à   .C
7)      Pro – C                                   à   .prc
8)      Java Programs                   à   .java
9)      Java Serve Pages (OAF) à   .jsp
10)   PERL      scripting               à   .perl
11)   Java Server Faces(ADF) à .JSF
Default Types 
1)      Constant
2)      SQL Statement
3)      Segment
4)      Current Date
5)      Current Time
6)      Profile
These are of 8 types of Validation types 
1)      NONE
2)      Dependent
3)      Independent
4)      Table
5)      Translate dependent
6)      Translate Independent
7)      Special
8)      Pair
Request Set:      Request set is a collection of report / Programs that you group together and can be submitted to run is a single interaction.
                                                   
Request Id:         When submit a request oracle application assigns a request id to the request. We can use this request id to search for request.

Concurrent Manager:    A concurrent manager is a component of concurrent processing that monitors and runs tasks without typing up your computer.

View LOG:                          Using this we can see the technical details of the concurrent programs.

View Output:                    Using this we can see the actual result data.

Concurrent Program:     After creation of executable we will create concurrent Program by attachingExecutable, if parameters are there we will attach parameter if incompatibility Programs are there we will add those programs , for single executable we create Multiple concurrent programs with different parameters.

Request Group:                                After creation of concurrent program we must add the program to the request
Group. Request Group is nothing but collection of Concurrent Programs and Reports.

Responsibility:                 It is level of authority where we will combine Data Group, Request Group and
Menu. Request Group is optional, where as menu and data group is mandatory.

Data Group:                       Data Group is nothing but collection of applications names and Oracle User
Names based on this Username data will be retrieved from database.

Menu:                                  Menu is nothing but collection of functions (forms) and submenus.

Value Set:           values set is nothing but list values. It will be used to validate values while entering the parameters.

Token:                  Token is one the field will be used to map concurrent Program parameters with report builder Bind variable. We will enter the Bind variable name in the toke field so that parameters will be passed to the Bind variable.

Required check box:       By using this check box we can make the parameters mandatory or optional

Enabled Check Box:        By using this we can enable or disable the parameter.

Display Check Box:          By using this we can hide or display the parameters in SRS Window.

Range Option:                  While defining the from and to parameters if we wanted to accept Values in accession order menus from values is low and to value is high we will Select the options called low and high.

Default Types:                   If we are hiding the parameters user can’t enter the values that Time we can pass default values by using default type and default value filed.

Scheduling the Concurrent program       We can submit the Concurrent program future date or date by using the schedule button in SRS window.

1. As soon as possible: This is default option whenever we submit the request it will submit the as soon as possible

2. Once: It will submit the rest only once for future date.

3. Periodically: WE can specify the from_date and to_date to submit program periodically no of. Days months, hours, minutes and so on.

4. Specific Days: If we want submit concurrent program in the specific days we write select this option

5. Save this Schedule: This check box will be used to save the schedule and apply same schedule to other concurrent programs by selecting the button called ‘Apply save schedule’

No comments: