[Solved] Report could not be opened error

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
BillKnott
Posts: 14
Joined: Thu Mar 13, 2014 9:47 pm
Location: Washington UK

[Solved] Report could not be opened error

Post by BillKnott »

I have recently converted my embedded database to a split database by following the tutorial by DACM.
Everything seems to work fine apart from some of my reports. Two of my reports open up OK but for the rest, I keep getting this error message: "The document 'DocumentName' could not be opened. User lacks privilege or object not found". I am using Oracle Report Builder 1.2.1.
I have tried re-installing ORB and also copying working instances of the reports from the working embedded .odb file, neither of which resulted in a cure.
Reading some of the messages on this board it seems that the ORB can be a bit unstable. Any ideas how I can resolve this please?
Last edited by BillKnott on Mon Jul 14, 2014 9:59 am, edited 1 time in total.
Open Office 4.1 on Windows XP sp3
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report could not be opened error

Post by Sliderule »

Please tell us, for the ORB ( Oracle Report Builder ) Reports, that you canNOT open ( did you mean . . . ¿ OPEN for editing, or, RUN to produce a report ? ), do you know, if the 'source' in those reports . . . is from:
  1. a Table
  2. a View
  3. a Query ( pre-defined Query )
  4. SQL ( a SELECT statement you hard-coded into the report
In any of the above scenarios, can you retrieve the data ( rows ) from them? For example, if it is a pre-defined Query . . . can you run that Query withOUT any problem, outside of the Report?

The reason I am asking, is to attempt to narrow down the problem / solution. Perhaps, it is the syntax ( function ) in a Query that might need refinement, just a thought. :crazy:

Sliderule
BillKnott
Posts: 14
Joined: Thu Mar 13, 2014 9:47 pm
Location: Washington UK

Re: Report could not be opened error

Post by BillKnott »

I have 6 reports in total. All will OPEN for editing. 4 reports will not RUN. These 4 reports plus one that does run are all based on the same query. The query runs OK outside of the report. The reports have a filter enabled based on each individual report's required output. The [analyze SQL command] is set to Yes in each case. In my previous embedded db all these reports run OK.
Open Office 4.1 on Windows XP sp3
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Report could not be opened error

Post by Sliderule »

I am sending you a PM ( Private Message ).

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
BillKnott
Posts: 14
Joined: Thu Mar 13, 2014 9:47 pm
Location: Washington UK

Re: Report could not be opened error

Post by BillKnott »

I am most grateful to Sliderule who spent some time looking at this problem and advising me of the following solution which worked.

1. With regard to the ability to create your reports, please try the following, simple, solution.
For the Query:
Open it for EDIT ( right click ) and choose: *Edit in SQL View... *
next, from the menu, choose: *Edit-> Run SQL command directly *
next, SAVE THE QUERY.
Now, you should be able to run the reports successfully, exactly as you want/need and NOT change any of the reports.
Be sure to SAVE your database file ( *.odb ) when you have changed the Query.

2. With regard to the database . . . the SIZE of your data file and backup file can be significantly reduced ( much smaller )..
Right click on the Tables icon on the left . . .
From the Menu: *Tools-> SQL... *
In the Command to Execute box, enter: *CHECKPOINT DEFRAG * and click on the Execute button.
Close your database, you should see the size of the data file ( mydb.data ) is reduced.

The second point about the size of the data file and therefore also, the corresponding backup file, when tables or rows are deleted, the space is NOT reused in the file, until a command such as CHECKPOINT DEFRAG is issued. Good trick, if you know about it.
Open Office 4.1 on Windows XP sp3
BillKnott
Posts: 14
Joined: Thu Mar 13, 2014 9:47 pm
Location: Washington UK

Re: [Solved] Report could not be opened error

Post by BillKnott »

Following sliderule's advice about running the reports directly under SQL, this will produce the report, but if you need to sort or filter the data first, you cannot do this by opening the query and using the toolbar icons as these are now greyed out and unavailable. You need to edit the SQL directly. i.e. by using a WHERE statement. This is a bit of a long way around and somewhat cumbersome, especially if you need to filter the data several different ways for separate reports and then need to unfilter again. I have since re-examined the problem. In my embedded database, my reports are set up with the 'Analyze SQL command' property set to 'Yes' thereby allowing filtering to be performed. This works fine for all my reports. However, the same set-up does not work in my split database, with the error message coming up (see first post).
I have managed to get around this problem for the split DB as follows: With the report opened for edit, I set the 'Analyze SQL command' property to 'No' and saved the report, then ran it normally, it worked OK. The only problem is that I have lost some of my filtering capability. To get round this I have had to set up separate queries for each version of my reports, filtering the data via the query before running the report. This works reasonably well and I can get my reports in the normal way. It seems that the report builder has lost some of it's functionality when running with the split DB as the filtering cannot be set up from within the report editor.
Open Office 4.1 on Windows XP sp3
Post Reply