Crystal Reports 2008 doesn't show database tables / views that I know exist

Question

I have a schema in an Oracle 11g R2 database that I'm trying to connect Crystal Reports.

I have two users; an admin user (where I create the views, etc.) and a reporting user that has the ability to query certain tables/views.

In any other database tool (SQL Developer, TOAD, DB Visualizer), I can see the schema along its tables and views, and can query against them and create new views, etc. as I should be able to.

However, in Crystal Reports 2008, when attempting to access the data, the proper schemas/views aren't displayed. Examples:

• Creating an ODBC datasource in Crystal (which I believe connects to one I've pre-created in windows that works just fine), only a small subset of schemas are shown in Crystal (but not the one I should be able to see).
• Creating an Oracle datasource in Crystal shows me the schema, and all of the tables I believe, but only one of the views (not the one I need).

NOTE: Normally I would think that it's a permissions issue on the database, except that I can access these schemas/tables/views properly from every other client I've tried.

Any ideas? Is it the drivers that Crystal 2008 uses? Is it still somehow possibly a permissions issue? I'd appreciate any insight you fine folks have.

Answer 1

OPEN CRYSTAL REPORT THEN CLICK FILE -> OPTIONS -> SELECT TAB DATABASE -> IN THE EXPLORER OPTIONS PUT TICK MARK ON TABLES AND Onwer Like < add schema name> click ok

this will list only that schema. Crystal Report has some limit is loading all table names so select the scheme so that it will load only that schema. thanks, praveen.

Answer 2

I've got a better one... I was working with this for a long time today, trying to help one of our new developers. He had developed a report from a different workstation against a different data source, and we needed to swap the data source when we transferred it to the new network. Fired up CR, Showed him how to "Set Datasource Location", we get the account information, check the connection string, etc. Get ready to show him how to replace one db w/ another... find the connection, open the server, pop out the databases, open the database to show the tables and... Nothing. Hm...

Try a different account that I know works. Strange, THAT one doesn't see any tables either. Try a different database. OK, now I'm a little off-balance... Remote into the web server to see if I can run one from there. Fire up CR, Open an existing report, hit refresh, put the PW in, and voila! Data. Lots. Copy his report up, remote in, open it, get ready to Set Datasource Location, and ... nothing.

Spoke w/ the DBA, watched/walked him through the check, still nothing.

Funny thing was, if I had a report that had connected before, it would run. Wonderful! Check the available tables... nothing. Quick jump to look at the db... I can see the privileges, I can see everything set fine. Cool. Tried again, nothing.

OK, spoke to another DBA. I walk him through CR to show him the issue, he and I are going to explicitly set permissions. I open the data source in CR, right click to look at Properties, and... noticed that I hadn't check Options. Sinking feeling in the pit of my stomach. Open Options, and notice in the Data Explorer section, TABLES is not checked.

I remember WHY I set it... a long time ago. The DB has thousands of tables, and I knew which ones I needed. I paste a command and go, I never CHOOSE tables.

So... Check TABLES, and thousands of tables show up again. Sigh.

Answer 3

Looks like this was indeed an error on our DBA's part. A certain level of "select" permissions in their permission model was preventing access. It appears to have been resolved.

But if anyone would like to help me gather all copies of Crystal 2008 in a warehouse and light them on fire, be my guest. :)