Crystal Report data source table with empty data 1

[yanming]

I am new to a company and new to Crytal Report.

It's hard to get help from my company's IT person.

I have one question. Hope get help from you.

We have tables and stored-procedures available.

But, after I selected some fields from a table
to report, I see only the column titles in the
preview report without any data. I tried to do
reflesh, but there were still no data show up.

However, when I selected a stored-procedure to
report, all the fields I inserted into the report
show valid data.

Do I missed anything? Or, I need to ask someone
to do some "update" action to the tables.

 

[SQLSister]

read your stored procedure to see where it is selecting the data from. ANd then match it with what you are running that returns no records.

 

[yanming]

Hi SQL Sister,

The table I tried to use contains different data
than the stored-procedure(SP) produced data.

This means that tables and SPs are not one-to-one
corresponding. I checked that all the files in the
database have their tables listed in the data source,
but, the SPs are under some special name, e.g.
Crystal_AR_Invoice, Alert_Booking, Crystal_Booking_Monthly.

An expert suggested that my problem is due to that
I don't have the proper privileges (access rights in the database), even I have the right to use SPs.

How you think?

Thanks.

 

[SQLSister]

That could be true. If you have execute rights to a stored procedure you don;t have to have rights to the underlying tables or views. However, if you are directly querying them you do. Many dbas only allow access through stored procedures to prevent problems with people messing up the data through direct access to the tables. CAn;t you use the stored procedrues as the basis for your Crystal Report?

 

[yanming]

Hi SQL Sister,

The problem is that there are many tables (>200)
each is for one database file. I need to use the
fields in the tables.

But, the is only 20 or so SPs, and they don't
provide all the data I need.

I think, in theory, even an user can use tables,
he could be controled to not be able to mass
up the tables (they couldn't do query to create
or update tables.)

Based on this thinking, I don't understand that
why I can use SPs but not allowed to access tables.

Thanks.

 

[SQLSister]

Because it is much more work for the dba to keep up with permissions for individual tables than it is to keep up with permissions for stored procedures. Yes you can set up individuals with table permission that only include select and not update, insert or delete, but if you have hundreds of tables and hundreds of users with each one needing different acceses to different tables it can be a nightmare to manage. And you still have to manage the permissions for the sps anyway.

Users generally do not have any direct way to access the database anyway as they should never have Enterprise manager or Query Analyzer. These are tools which should be only used by professional dbas or programmers.

If you do not have the permissions you need to do your job, then ask the dba for the correct permissions or ask him/her to write an sp for what you want. IF you get turned down, explain the problem to your boss and have him/her deal with the dbas boss until the issue is resolved. It's good to be protective of your database, but not to the point where people can't do their jobs.