• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2894
  • Last Modified:

"Database Name" -- How to display it on a Crystal Report?

I'm using an external tool to make some changes on some Crystal Reports, and just wanted to check if the change to "database server" took effect when I preview the report.  

So, is there a way for me to Display the Database Name on a Crystal Report (so I can quickly see if my changes took effect in my tool's viewer, and to show the difference between the "production" report and the new "test" reports I'm modifying)?
0
quellcoder
Asked:
quellcoder
1 Solution
 
Brian CroweDatabase AdministratorCommented:
Since the database will be set in your application you can just send it over as a parameter or set the value of a textfield.
0
 
mlmccCommented:
You could also use a formula that gets set by the application.

mlmcc
0
 
quellcoderAuthor Commented:
By "external tool", I mean rptInspector, which makes changes to Crystal Reports (fonts, database location, formulas, etc -- neat in concept, but I'm just now getting into it, so don't know just yet if I'd recommend it, btw).  

So, in response the BriCrowe, I'm not "sending" the database name over as I call the report.  I just need the syntax or function on how to display the database name in a text-box on the report, so I can 1) see it when I print the test reports, and 2) make sure the test reports are actually pointing the test server (I guess I can "look" for test data, but the concept would be the reports would show almost identical data results).
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
vidruCommented:
Try using a SQL Expression field (if your type of db connectivity supports it).  It has a cross-db-platform function that gets the database name (under Functions > System > DATABASE()):

{fn DATABASE()}

-dave
0
 
quellcoderAuthor Commented:
How?  (sorry)
I did the following (trying to be detailed here, in case there is another way I'm supposed to do it).

I opened the report in Crystal Reports, went to FormulaFields, New, gave it a name, UseEditor, and I pasted in " {fn DATABASE()} ", but when I did "Check Formula", I got the error of "The Field Name is not known.".

(and I tried both "Crystal Syntax" or "Basic Syntax" in the editor.)
0
 
vidruCommented:
Instead of Formula Fields, do you see SQL Expression Fields?  If you don't see it, then you can't use it - that would mean your db/connectivity doesn't support their use.  If you do see it, that's where you'd write the formula.

-dave
0
 
quellcoderAuthor Commented:
oh.  
No, it's not a node in Field Explorer. I also looked up SQL Expressions in the Crystal Help, and it shows "where it would be", and it's not there. I'm using Oracle 9i, Crystal Reports 9.   (But, that would have been Very Handy for other sql syntax that I Could have used. Thanks).

ok, any additional suggestions?
0
 
vidruCommented:
The long way #1:
Try running the report, then going to Datbase > Show SQL Query to see if the correct database is being used.

The longer way #2:
Use the 'Add Command' option to add another 'table' to the report(s) that uses the valid syntax for Oracle to get the current database name.  The text of the Command would look like this (I have no idea what the Oracle syntax for retrieving the current database name is, so you'll have to find that):

SELECT <Oracle function to get the current database name>
  FROM Dual

I'm surprised that SQL Expression fields aren't an option with Oracle 9i.  Are your reports using stored procedures (that would also be an instance where SQL Expressions aren't allowed)?

-dave
0
 
quellcoderAuthor Commented:
Two "solutions" in one page.    

The Original Q, AND, How make "SQL Expression Fields" show up in Field Explorer:
I went into Record Selection, and it put me in "Formula Workshop" --- and a node for "SQL Expression Fields" is in that tree.
I created a new field (using the {fn DATABASE()} mentioned above by "vidru" -- thanks),
and "checked it", and saved.  
It now shows up in Field Explorer, and I can just insert that new field onto my report. AND IT WORKS GREAT!

Also, now every time I open Crystal Reports, the "SQL Expression Fields" node is always available in the Field Explorer.

====================
Also, as an aside, I created my own 'function' to use in SQL if I need it (following Vidru's last suggestion).

CREATE OR REPLACE FUNCTION Get_Dbname
RETURN VARCHAR2
IS
  vName VARCHAR2(100);
BEGIN
   SELECT name INTO vName FROM v$database;
   RETURN(vName);
END;
/

And, I'd call it with:   SELECT Get_Dbname FROM dual;

============

So, the "SQL Expression Field" using {fn DATABASE()} was the solution to this question. Thanks.
 
0
 
tcalbazCommented:
I know its way after the fact but I  found another solution to getting the Server Name.  With what ever Selection method you're using just add one more field.  ie:  

SELECT Field1, Field2, SELECT @@SERVERNAME AS MyServer FROM MyTable

That field MyServer will be available in the report where ever you want to place it.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now