Dispalying a report's output in an Excel sheet


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?

Who is Participating?
Helena Markováprogrammer-analystCommented:
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.
Helena Markováprogrammer-analystCommented:
You can try
if u have ability to use another reporting tool, Crystal Reports can generate reports in pdf, doc, xls, ...
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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.
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"
ausanAuthor Commented:
when i tried what u suggested, it gave me the following error,

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

Helena Markováprogrammer-analystCommented:
... try with DESFORMAT=delimited ...
ausanAuthor Commented:
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...
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?
ausanAuthor Commented:
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...
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
ausanAuthor Commented:
i cant copy and paste it
you can always just put in new labels in the header section and delete the old ones from the body.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.