While developing a tabular report, its always flexible if the output is generated in an excel spread sheet format. It becomes easy for the user to perform manipulations and other data comparisons.
Oracle supplies a default .prt file located at $ORACLE_HOME/reports/printer. Its contents will look like this.
So, often the customer demands for an excel output from the oracle reports concurrent program.
Here is the sequence of setup steps to create a report in excel format:
- Create Printer file (.prt)
- Create Oracle report and specify appropriate printer codes for the fields
- Define Printer Driver
- Define Printer Style
- Associate Printer Driver and Style to a Printer Type
- Associate the Printer Type to the Printer
- Define concurrent program and specify the report style created
- Run report and save the output as text file and open it with excel format.
Create Printer file:
Oracle Reports reads the printer definition file (.prt) specified by the "desformat" parameter to determine the values of certain printer attributes such as the line feed control code, the page break control code, or the control code for bold or italic. It is called "prt file" after the extension of the file.
Printer file (.prt file) need to be created with printer codes for tab and newline and should be placed in $FND_TOP/$APPLREP Directory.
Oracle supplies a default .prt file located at $ORACLE_HOME/reports/printer. Its contents will look like this.
printer "dflt"
height 66
width 80
after page control(L)
return control(M)
linefeed control(J)
height 66
width 80
after page control(L)
return control(M)
linefeed control(J)
printer "dflt" | This line puts information about the printer. That is to which printer it belongs. From usage point of view it's just a information |
height 66 | Indicates no. of lines which can be printed |
width 80 | Reports width in terms of no. of character which can be printed |
after page control(L) | This is to break page. So when output file is generated it will have control(J) for Page break |
control (M) | for "return key strokes" |
control(J) | for linefeed |
So, use the below contents and create a prt file for our purpose (xls output)
printer "Excel Format"
height 66
width 80
code "600" control(J)
code "500" control(I)
Create Oracle report and specify appropriate printer codes for the fields:
Create an oracle report and as created in the prt file, specify &600 for new line and &500 for tab in the objects properties of the report layout.
When printing the first field in the layout of a tabular report, print it on a new line. So, use ‘&600’ in “printer code before” property for that object (F_1).
After printing the first object, leave a tab space. So, use ‘&500’ in “printer code after” property for the same object, as shown below.
Similarly specify for all the objects.
This will enable to print the fields within the repeating frame in a new line and also after leaving a cell from second record onwards as the printer codes are mentioned in the “Printer Code After” property.
Also in the report output generated if value gets truncated for the fields, the Horizontal elasticity for the field should be set to variable so that the contents of the field will fit in a cell.
Define Printer Style
Navigation Path:
Go to System Administrator Responsibility
Browse through Menu=>Install=>Printer => Style
Define a Printer style and mention the name of the .prt file in the SRW Driver field.
Define Printer Driver
Navigation Path:
Go to System Administrator Responsibility
Browse through Menu=>Install=>Printer => Driver
Create a Printer Driver and mention the .prt file name in the SRW Driver field.
Provide the following in the arguments field.
lpr -P$PROFILES$.PRINTER -#$PROFILE S$.CONC_COPIES -T"$PROFILES$.TITLE" $PROFILES$.FILENAME
- $PROFILES$.PRINTER retrieves the operating system name of the printer associated with the request.
- -P destination the name of the printer from which you wish to print too.
- $PROFILES$.CONC_COPIES retrieves the value of the profile option Concurrent: Report Copies, unless this value is updated at runtime.
- "$PROFILES$.TITLE" retrieves the title of the output file, typically titled as Application username. Request ID -T calls out the report title to print on a banner or header page.
- $PROFILES$.FILENAME calls out the filename of the report to be printed. The value retrieved is the output file name, including the path to the file.
Associate Printer Style and Driver to a Printer Type
Navigation Path:
Go to System Administrator Responsibility
Browse through Menu=>Install=>Printer => Types
Associate the printer style and driver to a new printer type or to an existing printer type based on the requirement.
Associate the Printer Type to the Printer
Navigation Path:
System Administrator Responsibility
Menu=>Install=>Printer => Register
Associate the printer type to an existing printer or to a new Printer based on the requirement.
Define concurrent program and specify the created report style
Provide the printer style while defining the concurrent program and enable the style required checkbox.
Run report and save the output as text file and open it with excel
Run the concurrent program and save the output generated as text file in your local machine. The output file is a tab delimited text file, now right click and open the file in excel format.
