How to identify underlying table and column names

[nickdaniels]

Apologies for the simple nature of this question, but I had never used Crystal until today:

I have a report with an item called 'Connects'. I want to trace the definition of this back to the database table and field(s) it is derived from. If I click on the item on the report, I can see that the formula is 'Combined Call Statistics.Connects'. Combined Call Statistics is one of six what I assume are database tables. However, if I create a new report base on this same database, the names of the various tables available are all quite obscure. I guess that 'Combined Call Statistics' is an alias for one of these obscurely named tables. How can I trace this back??

Thanks, Nick

 

[Lion3000]

I did the same thing: created two different reports based on the same database. In both reports, the database field (what I assumed that the word "item" in your question means), in my example, on a table named "Banks". I used the same database field "Bank_id" and dragged it in the report to have the displayed name be: Banks.Bank_id
That displayed name appears in the status bar as follows:
Field: Banks.Bank_id
I am trying to understand your question, but tell here, have I done the same steps that you did? Please be more detailed. I am trying to help.

 

[nickdaniels]

Thats the same as I did. Except, the first report already exists and has nice database table names and field names. When I create a report based upon the same database, all the database tables available have names along the lines of no_src1 - that sort of thing. I think in creation of this existing report someone has started as I have done, but then have done a piece of work to give all the tables and fields meaningful names.

Hope this helps

 

[Lion3000]

What I understood now is that you are not the only one who worked with that report. The first report had the database fields insert in the report with meaningful names as you assumed. Let us say that the table used has name "Statistics" and that person who worked with the report before you renamed that to "Combined Call Statistics". Normally, for this to work, he had to insert the table "Statistics" first, then insert another table "Statistics". Here, Seagate Crystal Reports displays a dialog box with the title "Database Warning" and the following message:

The table &quot;<database name>.<connection type>.Statistics&quot; has already been added to this report with alias &quot;Statistics&quot;.
Do you really want to add another alias to this table?

Note: <database name> is the database name that you used and <connection type> is the connection type that you used, for example, &quot;dbo&quot;.

At this point, that person would have clicked the &quot;Yes&quot; button in that dialog box to change that table name (to be inserted in the report) to &quot;Combined Call Statistics&quot;. After that, he deleted the original table &quot;Statistics&quot; from the database.
Was I able to show you the situation that you are facing? I hope so.

 

[MalcolmW]

Database|Set Alias is the place you can get at the name that Crystal uses for a table. Here you can change the aliases of existing tables.
By default, Crystal uses the table name defined in the database definition, which can be cryptic. You can change the alias to virtually any name you want, and all the formulas etc are updated to reflect that new name.

 

[Lion3000]

MalcolmW is right. Thank you, MalcolmW. Sorry nickdaniels for my indirect way in representing the problem.

 

[nickdaniels]

Right, I understand how this has been setup. So now my problem with that on the Database menu I do not have the Set Alias option. In fact, I only have a couple of available options and about 15 greyed out. Available are Verify database and logon server, logoff server. I'll look into getting access to this option, I think this would solve all my problems...

thanks for your help

Nick

 

[nickdaniels]

To take this a stage further, would it be possible in this aliased table to only make certain columns available/visible. Would it be possible to give the visible columns meaningful names?

 

[Lion3000]

You only display the columns (or database fields) that you want in your report and change the column names displayed in the report. For example, a database field (column) with name &quot;States&quot; and the records would be displayed in the report as follows:
States
AZ
CL

and so on... Or do you mean as a means of security that no person can see the columns that are ino the database, but only be able to view the columns used in your report?

 

[nickdaniels]

I assume it has been done for security purposes so if an individual looks behind the scenes, all they see is meaningfully named tables with meaningfully named columns, and only those columns that are used.

 

[Lion3000]

I think you do contact your database administrator to limit the access for any person you do not want to view certain tables. As for the meaning names for the columns, it can be done from the database, that is, create a meaningful names for the columns when creating the database. For sure, the column names can be changed after the database creation.
I hope that helps.

 

[MalcolmW]

Using a stored procedure or view are common ways to insulate users from the underlying table structure, either to simplify things or just keep their prying eyes off the stuff they don't need to see. Access rights to views and stored procedures are set by your dba. [sig][/sig]

 

[kenhamady]

Nick,

It may also be that your are reading a Crystal Dictionary or a Crytal SQL Query file. If you use the menu options
&quot;Database - Set Location&quot; you will know.
If the source is a QRY file then you are using a Crystal Query. If the source is a DC5 file, then you are reading a Dictionary. If this report is reading a dictionary, then you can create reports from the same dictionary by using the dictionary option on the data tab of the report expert. Just point it to the same DC5 file.

Another clue is if you can use the menu option Database - Show SQL Query. If you can use this menu option you can see from the SQL what tables and fields are being used.

Good Luck,
Ken Hamady

http://www.kenhamday.com

[sig][/sig]