Solved

Dispalying a report's output in an Excel sheet

Posted on 2004-09-20
13
2,161 Views
Last Modified: 2008-02-20
Hello,

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

web.show_document('http://10.64.100.61:8888/reports/rwservlet?
destype=cache&desformat=pdf&report=\salalah_Trans\REPORTS\full_request_list.pdf
&userid=trans/trans123@omntlp&FROM_DATE='||:full_list.FROM_DATE||'
&TO_DATE='||:full_list.TO_date,'_self');

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?

Thanks
0
Comment
Question by:ausan
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12100997
You can try
desformat=delimiteddata
0
 
LVL 9

Expert Comment

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

Expert Comment

by:Jankovsky
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.
0
 
LVL 3

Expert Comment

by:dnarramore
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"
0
 

Author Comment

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

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

thanks...
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 12109233
... try with DESFORMAT=delimited ...
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:ausan
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...
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 70 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.

Explanation
-----------

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.
0
 
LVL 3

Expert Comment

by:dnarramore
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?
0
 

Author Comment

by:ausan
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...
0
 
LVL 3

Expert Comment

by:dnarramore
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
0
 

Author Comment

by:ausan
ID: 12130687
i cant copy and paste it
0
 
LVL 3

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now