Reports 9i Patch 1 - Reports

I tried to use Excel as a datasoruce to run a report, but I got an error "ERR 62007: SQL-error: S1C00 [Microsoft] [ODBC Excel Driver] Optional feature not implemented.
I am using Oracle 9iDS R2. I understand that ODBC drivers require the PDS to set some properties which are not set in the initial release. Where I can find the Reports 9i Patch 1? 

Hello ,
This issue has been solved. Please download the latest patch from following link or contact Oracle Support.
With Regards

Thanks for your reply. I have applied the patch,, to my Reports 9i and followed every step from the Reports 9i Demo (using Excel as a data source), but now I got different error message.
The error message is "SQL Error: 37000 [Microsoft][ODBC Excel Driver]'novalloc' is not a valid name Please make sure there is no special character or punctuation...". The 'novalloc' is my Excel spreadsheet's name and it has no special character or punctuation. The SQL query that I entered on the JDBC PDS is select * from [novalloc$], which is the correct syntax according to the demo.
Could you shed some light on this problem ASAP; I already have spent a week on this and have no idea what caused the error. Thanks.

Syntax for the query is : select * from [worksheetName$] .
Please verify your ODBC connection. User has to make ODBC Datasource to the excel file, from which user want to
create the query. Then user can create a query on a worksheet avaiabale in that excel sheet.
Please try with simpler case, create a new Excel file, with single worksheet. Add some rowsa and verify, if it works.
Note : Excel Driver may not work for some really complex worksheets.
With Regards

Thank you for your time. It's working. The whole problem was I used Excel's file name to run the query intead of Worksheet's name. 

I am trying to create a report with 2 worksheets. To start with, I created two worksheets in excel for the template and saved as xls. I added the required queries to the report, one for each worksheet. Now when I run the report and saved as jsp...When I try to run it, I get the error message :
Missing file:...\Temporary Internet Files\Content.IE5\89A3K1I3\sheet0001.jsp
Any idea why is it looking for the files under Temp Internet files and how may I resolve this issue?


Error When Open Template with BI Publisher Desktop

I have been done the OBE for Oracle Publisher. I installed the Oracle BI Publisher Template Builder. This can be access in MSWord, however when I try to open a template, this display the next error: "Error ocurred. Pleace check the settings and try again"; and whe i try to insert a object (chart, table, ....) this say: "Please Load DataSource first".
Any Idea? 
You need to load a template from the server. First login to the server from Word and choose a report and its corresponding layout(if required). When you do that some part of the data (for you to get the charts and tables working) would get automatically loaded. Then you would be able to create a report.
Yes, I login sucessfull but, it is precisely when I try to load the template I get the error:
"Error ocurred. Please check the settings and try again"
Hmmm.... By any chance are you on the release of BI Publisher?
Yes, I am in
Go to Start->Programs->Oracle BI Publisher Desktop->Template Builder for Word Language and set the language as en-US. That should make it work. If it still does not work try installing the version of BI Publisher desktop alone.
After log in in the BI publisher template builder, tried to Open the report from the report Template . Then the message is displayed as Error occurred: ‘Please check the settings and try again’.
I am using the OBIEE version and Ms-Office 2000......
I did the same thing ->
Start Menu\Programs\Oracle BI Publisher Desktop\Template Builder for Word Language
But still i am getting that error
Did this ever work before? Can you check whether your query first works in the web console (it should give some data)? Also, are you trying to create a new template on an existing report data set?
Dear Mr Venkat,
That problem is comming even while we are opening the predifined(sample reports) also.
thanks for ur reply
I've had this problem before, I read somewhere on the Oracle website that this may be due to a result set that is too large to be handled by template builder. I fixed the problem by restricting the number of rows returned by the query
If I am not wrong then you are able to access report/template earlier. This problem comes suddenly. If you are using any database connection then please check database connectivity. I think you are unable to access your database.
Amit D. 
We had the same problems...
Can you load a standard XML file?
If you go to: start --> programs --> oracle bi publisher desktop --> samples --> rtf templates
Open a template over here.. and then go to data --> load xml schema and load the xml.
Now when you can preview there must be something else wrong.
Now when you can't preview.. (what we had as well).. We tried reinstalling bi publisher desktop, but the only remedi was to reinstall the computer with using a new windows profile. Only this worked... 
Has anyone found a resolution for this?
I can create templates manually, just can't connect via the 'Open' command. I've tried most things mentioned here but nothing helps.
I think this issue happens when your report data template has parameter. So when you try to create a template for the first time it gives this error. How I do is add a additional and clause stating rownum = 1 and design my template and upload the same. After that you can add the parameter to the report. It should work fine.
This error can occur if your query takes way too long time to run. I guess there's some setting that's preventing the query to execute. Workaround for this problem is
1. Do not run your query for all records, use some sample data by adding an additional where clause (make sure your query does not take too long time) and create the template.
2. Next open the DataTemplate File (XML file) and delete the additonal where clause.
This should resolve your problem. Atleast it did for me.

BI Publisher Database Connectivity.

Dear All,
Can any body tell me, how to connect Publisher with Oracle Database to create rtf file.
You need to set up the JDBC configuration under Admin tab once you login as Administrator. You need to provide the connection URL , userid/pwd; a sample is provided with the install. You can quickly test the connection on the same page. Check the user guide for the details. 
Hi Pradeep,
Yes that has been done and that is working fine.
the problem is when i try to create a new dataset it shows lot of options
1. SQL Query is selected.
2. Data Source is 020 which is created in jdbc Connection.
3. Once i click on Query Builder It gives 'Could not load schema Information. Please contact the administrator.'
Futhur I could not proceed, Please help me to fix this challenge.
Did you test the connection while creating the JDBC connection ? If that works then it should be able to load schema unless it has too many tables. Try entering few letters of any table known to you and see if that brings any table name. Also check if there is any error getting logged in the console. 
yes Pradeep. Connection is tested against the database and got the message 'Connection established successfully'.
1. but still when i click on Query Builder Button, it gives the same error.
2. it has to give the schema or table from the database, but not giving.
What version of BI Publisher are you using ? .. before clicking on the query builder did you select your datasource from the LOV and saved it ? In case you want to continue with the report you can simply type the SQL query in the text area provided. So please check if that works for you. 
Yes. I am using BI Publisher,
1. Selected value from the LOV for Datasource and saved.
2. I have given the sql query in the text area and also not working, on clicking the query builder.
3. If i select bursting in the right side menu, it shows a pop up that 'Failed to retrieve lov from main dataset. Please check your main dataset definition and try again.'
What may be the problem 
Query builder just facilitates the building up of SQL query, so you can bypass the use of query builder by simply typing the query in the text area.
You do not need to select bursting unless you intend to burst your report (ie split your data and deliver your report to multiple destinations). Please read the user guide to understand the screen flow.
If you are able to write a query, save it and then view it in XML data format, then it means your database connectivity is working for the report. So while we check your query builder issue, you can continue to build and test reports. 
Hi its not the problem in the application, u want to set the jdbc connection details in ur database tnsnames.ora,
try this 
The problem gets solved.
I got the error because when i created the Datasource, i checked the Use Proxy authentication check box.
Now i unchecked the check box and now its working fine.

Excel Analyzer:errors on opening reports

Hi all
I am on Excel 2007, OBIEE
Finally got the Analyzer menu tab to appear in Excel. After logging in I I am able to see on-line reports (but not templates?). When I try to open any report I first get an error box which says
<<Translation file not found for locale:'en-GB'!>>
When I ok this i get an Excel message:
<<Object variable or With block variable not set>>
The report then appears to load but the cursor remains an hour glass and none of the Analyzer menu options are available. I therefore cannot create or upload templates!
Can anybody help with this please?
Has anyone progressed beyond here and created Excel templates? I have tried setting the locale to different values but I get the same result. 
Has nobody got the guts for this one? 
Found the problem,
In the Install directory of Excell Analyzer there a number of translation files, and bizarely this file is missing.
The directory should be C:\programfiles\oracle\XML Publisher\Analyzer for excel\config
I've copied the en-US.xlf file and renamed it en-gb. Not sure if this is correct but my reports are working now. The real question is how to update the xlf file on the bi server so all users dont encounter this problem.
Hope this helps anyone else who has this issue.
Can the 2007 version handle reports that have more then one dataquery call?
2003 can only do reports that call 1 SQL statement. 
Hi - I am not getting the Oracle BIP in Menu tab... i installed Excel analyzer and i have .net framework also. When the excel opens it opens with a blank file and when i click on addins Oracle BIP menu is not in there. Am i missing something... It's a urgent requirement any kind of help will be really useful... Can you tell me how you resolved this issue?

FDM Reports publishing empty contents

Hi All,
We upgraded FDM from 9 to approximately two years back.
The testing was not conducted as the hole and later the issues are raising, Now the problem is No Reports are publishing with contents. Means report window opens with empty content. An additional information that I am able to see the RPX files in the report folder in the installed path. So they migrated the report artifacts also.
Please suggest me how to debug this issue.
Thanks & Regards,
First of all, if they are all doing it, I'd be suspect that some type of configuration item is to blame. What happens if you make a new report?
To help narrow this down, here's what I'd do...
#1 - Go to Server and open Workbench client
#2 - Login / Open App
#3 - In the window pane on the left that Shows Adapters, Scripts, and Reports, click on Reports.
#4 - Find one of the reports in question
#5 - Double click on report which should open the Report Properties window
#6 - Click on the SQL Statement tab
#7 - Click on the Zoom button
#8 - Click on the Preview Data button
If no data here, there is EITHER some form of a communication issue between FDM/Reports and database. or there's no data....
Then I'd .....
#1 - Copy the SQL Statement
#2 - Connect to SQL Server and execute query against database to see if data is returned.
If data is found, there is a communication issue (physical or user access to tables) between FDM and your database, review database settings to confirm that settings look correct on FDM And confirm in the database that the user being used has access to the tables
being referenced...
If you did get data from the query, then I'd look more carefully at the report itself.
#1 - In reports panel on the left, right click on the report you are investigating and Design Report
#2 - Verify the report open in the designer correctly.
#3 - At the top of the report area are two tabs, Design and Preview. Click on Preview and it will then generate a report. Verify if this is working.
Depending on how that turns out, you might get more clues as to where to look next. 
Thanks for your quick response.
Let me check all this diagnosing steps and post my results. 
Yes. I am seeing the data when click on the preview data button.
Report designer is opening and I am seeing the property tool box for all the Page header, Details and Page footer.
In the details I am seeing an icon named ADO in the top left grid and nothing other than that.
Still generating the blank report when click on the preview report.
Please advice. 
What format are you running the reports in? If .PDF do you have a PDF reader installed on the client machine? 
Yes. we are fetching in the PDF format but the not publishing in the work bench itself not only in web client. 
I can view the Reports via ‘FDM Workbench’, and select ‘Design Report’ and seeing that the reports are actually blank for the build But the ‘Report Definition Properties’ displays that the expected values are sourced: 
Can you change the format so it doesn't default to PDF and see what happens?
Sounds like some type of PDF generation problem if you are getting data from the workbench client. IF you try Excel, etc, and data is flowing in that should confirm. 
Please let me know how to chage the formate in work bench. 
Changed the formate in to xls. and published and exported. Still no use.
Mean while let me know how to design(create) this same report from scratch. 
can you trace the SQL query being fired? which db are you using?
You could use SQL profiler (MSSQL) or wireshark to see the exact SQL query fired when report is previewed.
F Amores,
Why would he need to do that? From workbench, you can run the SQL directly and he already replied that he was getting data when he does this. 
Is the report a standard report? If so, tell me which one and I'll send you the RPX from a newer version of FDM.
Perhaps there is some type of migration/upgrade process that should have happened that didn't?
If I recall correctly, my custom reports from System 9 just worked when I built our environment; however, that was a while ago and perhaps I have forgotten something.
Are there any errors being logged on the server in the event log or anything getting logged in FDM? Perhaps there are some clues there.
The only thing that I have found about blank reports that might fit your situation was a reference to the fact that the service account needs to have it's language set to English in the REgional Settings of the computer or some reports may come out blank. However, that was a very old article I found so it may not even apply anymore.
Just to discard that the report content is not blank because the query being wrong (regional settings, etc.) 
From the report designer, you can execute just the SQL against the database and get the raw results back. He has already stated above that he can do that. If that works, using Wireshark / Profiler is pointless as he's already proven the data is making it's way from the database server to the FDM server. Something is going wrong after Active Reports gets the data and tries to create the output.

Problem printing a report in Report Builder 6

Hi people, I'm experiencing tremendous difficulty when attempting to print a report in Report Builder 6. I've NEVER had this problem before, and it is a bone of contention to our client. Whenever I try to print a report from the report previewer, I get the following error:
"REP-1849: Failed while printing."
This error occurs irrespective of the query's complexity!
Even if I write a simple query such as, "SELECT 'johann' FROM dual", I get the above error. PLEASE help! Failure to test my report (and implement it) is causing a loss of approximately R570,000.00 to the company.
Your feedback will be appreciated; feel free to e-mail your suggestions to me at:
first, test with another printer driver.
You can download the "Oracle Reports Printer Driver Utility (2.0)" at
The latest patch(patch 11) addresses this issue.