Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Dispalying a report's output in an Excel sheet

Posted on 2004-09-20
Medium Priority
Last Modified: 2008-02-20

am working on developer 9i.  When displaying a report, am using the WHEN-BUTTON-PRESSED trigger :


but i dont want to generate the report in a PDF format, but i want it in an excel sheet...how can i do that?

Question by:ausan
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +2
LVL 22

Expert Comment

by:Helena Marková
ID: 12100997
You can try

Expert Comment

ID: 12101173
if u have ability to use another reporting tool, Crystal Reports can generate reports in pdf, doc, xls, ...

Expert Comment

ID: 12101283
You can create report in Discoverer (easy to convert into excel, but it's much of work aside), use delimited ascii extract (easy to read in excel), or use data query in excel via ODBC. For general usage of excel outputs we usualy use Discoverer.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Expert Comment

ID: 12104585
You can use desformat=DELIMITEDDATA Henka suggested.  Also add this parameter in your URL:  "mimetype=application/vnd.ms-Excel"

For an Oracle report in DELIMITEDDATA format, you cannot use the same Oracle report file (RDF) that you use for .PDF output.  If you do, the user will see all the column headings on each row on the left hand side of the spreadsheet.  These DELIMITEDDATA reports are very easy to create, as they require no formatting or grouping.  In order to make the Excel output format correctly, here are some tips

1.      Only use one SQL Query.  If you need more complicated stuff that can’t go in a SQL Query, you can use functions, but put the functions in the same group with your other fields.  
2.      Only use one group and don’t put totals or subtotals on the Report.  The users can add subtotals and totals wherever they feel it is necessary
3.      You don’t need column headings in the Layout, but make sure you alias the columns in your SQL statement so that they will make sense to the user as Excel column headings.  If you don’t do this in your very first time you create the SQL statement, you will have to go back and modify the XML tag of each field in your Data Model.
4.      You only really need one field and one group in your Layout.  No need to put everything in there and formatting it.  However, make sure dates are formatted within your SQL statement, as you can’t format them in the Layout.
5.      Make sure the fields are in the correct order in the Data Model, as that is the way they will appear on the Excel Spreadsheet.

Another thing to consider is security.  You are passing your userid/password@database in your URL.  If you need a workround for this:

1.      On the server, Add ‘hide_pass_key:  userid=trans/trans123@omntlp%*’ to the key mapping file: $ORACLE_HOME/server/cgicmd.dat.  

2.  In your URL instead of "&userid=trans/trans123@omntlp", just put in "hide_pass_key"

Author Comment

ID: 12109185
when i tried what u suggested, it gave me the following error,

REP-826: Invalid printer driver 'DELIMITEDDATA' specified by parameter DESFORMAT.

LVL 22

Expert Comment

by:Helena Marková
ID: 12109233
... try with DESFORMAT=delimited ...

Author Comment

ID: 12109384
hi again,

ok...i had the data but the fields are not properly dispalyed,

STATUS      APPROVED      Serial No      Request Date      Empno      Emp Name      From Date      To Date      1046      21/09/2004      53220      MOHD ALI SAID BAIT SHAJEEB      21/09/2004      21/09/2004      

can u help plz...
LVL 22

Accepted Solution

Helena Marková earned 210 total points
ID: 12109671
Maybe this Note:107764.1 from Metalink will help you:

Generating to Delimited Text Output Causes Report Headers to be Repeated

Problem Description

You are using the new feature in Reports 6.0 to generate delimited Text output. You run the report and produce the text output. You have chosen any of the delimiter types to test. You are now pulling the file into Excel and find that the Header fields are not at the top of the data columns as expected, but at the left and repeated for every record.

Solution Description

This is a documented feature of the text delimited feature. In order to produce output and get an excel file that is usable then do the following steps.

The available steps are to:

1. Open the output in notepad or wordpad after the report is complete

2. Highlight the desired Header fields

3. Use Ctrl-C to copy

4. Click at the begining of the first line

5. Use Ctrl-V and Ctrl-V again to copy the headers twice in order to position the headers on top of the data columns

6. Press return to place line 1 on the second line

7. Save the file

8. Then open the file in Excel

9. Highlight the header columns to the left and delete.

You will note that the header is in the right place above the data columns.


This is a documented result of the delimited output feature. You can read up on it by using the Online help, then select FIND, then type in Delimited. In window scroll down to "delimited output." If you look at the examples using the buttons you will see this output.

Expert Comment

ID: 12111712
Did you get it to work using DELIMETED or DELIMITEDDATA?

If you are using DELIMITED, then the reason your fields are not displaying properly, is because they need to be formatted exactly right.  Make sure all the items in the Layout are all the same height and line up perfectly.

Did you put your column headings in the Header section?

Author Comment

ID: 12121073
It worked using the DELIMETED data type,

i checked the column headings,and they were not in the header's section, how can i transfer them to the Header's section?

thank u...

Expert Comment

ID: 12122449
Go to your Layout.  
Select each of the column labels then click on Ctrl-c
Under the View menu, go to Layout Section and click Header Section
Then paste your labels into the header by clicking Ctrl-v

Author Comment

ID: 12130687
i cant copy and paste it

Expert Comment

ID: 12132522
you can always just put in new labels in the header section and delete the old ones from the body.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question