Extracting data in SQL server using crystal report

[sandyboy]

Hi All,

I am new to sql server. I am working with Sql server 2000 which has tables
containing customer details. My question is :

Is there any way to extract the data in Sql server database using Crystal Reports. I know how to use Crystal report for reporting purpose but not for extraction.Since my company will not have any sql programmer in future they are planning to do this way. Is this possible?

[Motoma]

What do you mean, exactly, when you say "extract?"

You can use CR to query a database and build reports from that, if that is what you are asking.

[sandyboy]

What do you mean, exactly, when you say "extract?"

You can use CR to query a database and build reports from that, if that is what you are asking.

Yes. I have a table that contains details of Customers. For ex,

I have to pull all the details of customers whose age is greater than 20.How can i do this in CR.

[Motoma]

In the Field Explorer, Right-click on Database Fields, and select Add/Remove Database.

Yes. I have a table that contains details of Customers. For ex,

I have to pull all the details of customers whose age is greater than 20.How can i do this in CR.

[sandyboy]

In the Field Explorer, Right-click on Database Fields, and select Add/Remove Database.

Thanks for the reply. I have done till here. Say for example the table t1 contains
the following fileds.

Cust_id
Name
Address
City
State
Zip
Age
Date of Purchase.


Now i want a report that conatins only Name,Address and Age. I want to do this in CR. So i connected to the database and inside SQL expressions Field i wrote the following query,

SELECT name,address,age FROM t1

Is this correct. But i got a error which says

Error in compiling SQL Expression
Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Vendor Code:156]'


Note :I am establishing connection between CR and SQL server through ODBC driver.


So is the problem with the connection or with the query i wrote.
I have one more question. Should someone know little about SQL to query the database in CR . I am asking this question because there are no programmers here. So once i leave is it possible to do all extarctions using CR.

[iam_clint]

SELECT name, address, age FROM t1
where age > 20

[sandyboy]

Thanks for your reply. I wrote the query

SELECT name,address,age FROM t1 where age>20 ,but got the error

Error in compiling SQL Expression
Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Vendor Code:156]'

So we need to query the database in SQL through CR using SQL commands only. Is there any other way to do this?

[Motoma]

I am not sure what aspect of the original solution I posted was not acceptable for you.

[sandyboy]

I am not sure what aspect of the original solution I posted was not acceptable for you.

No. I accept that is the solution. But i got this error. I dont know why i am getting this error

Error in compiling SQL Expression
Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Vendor Code:156]'

[PhallaDyck]

Hi, I am not good at SQL server and I have never used CR but I think the problem you are facing is because of the connection between CR and SQL.

Because I used to see this message when I fail my connection between Ms. Access project and SQL server 2000. So let's rebuild your connection between this two programmes.

In fact the code you have written(select fieldname1, fieldname2..... from tablename where age>20;) was correct. So please try as I told you. I hope it may work.

Thanks

[Motoma]

Creating an SQL based report in CR does not require any manipulation inside the SQL Expression Field. Simply add your database to the Database Fields,, then drag and drop the columns onto the report.

No. I accept that is the solution. But i got this error. I dont know why i am getting this error

Error in compiling SQL Expression
Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Vendor Code:156]'

[hnminh]

Thanks for the reply. I have done till here. Say for example the table t1 contains
the following fileds.

Cust_id
Name
Address
City
State
Zip
Age
Date of Purchase.


Now i want a report that conatins only Name,Address and Age. I want to do this in CR. So i connected to the database and inside SQL expressions Field i wrote the following query,

SELECT name,address,age FROM t1

Is this correct. But i got a error which says

Error in compiling SQL Expression
Database Connector Error:'42000:[Microsoft][ODBC SQL Server Driver][SQL Server][Incorrect sybtax near the keyword 'select'.[Database Vendor Code:156]'


Note :I am establishing connection between CR and SQL server through ODBC driver.


So is the problem with the connection or with the query i wrote.
I have one more question. Should someone know little about SQL to query the database in CR . I am asking this question because there are no programmers here. So once i leave is it possible to do all extarctions using CR.

Now, I show you 2 problems:

1. To report only some fields, you choose the database, and the table you want to report (click on the Datatabase Fields item in the Field Explorer panel to open a wizard for it), then you drag and dop the columns you want onto the report. Very simple!

2. Solving your error. It is a syntax error. And here is the solution:

"Doing a SELECT in a SQL Expression field:
When teaching SQL expression fields I have always tried to stress that SQL Expressions are different from SQL Statements. A SQL Expression is a column in the report, where a SQL statement is a full query. My short version of this was to say "a SQL Expression can't do a "SELECT". Well I recently learned that this is not precisely true. Under certain situations, a SQL Expression CAN do a completely separate select from the main report.

The main limitation is that it can only return a single value. So you probably will need a summary function. The following example comes from the Xtreme Sample Database:
(SELECT Max ( Orders.`Order ID`)
FROM `Orders` Orders)

Normally a CR SQL Expression would error on the SELECT, but if you put this expression in parentheses, Crystal will pass it to the database as a separate query. Amazingly, the column being queried does not even have to come from one of the tables in the report, but can be from another table in the database. In the past I would have recommended doing this via subreport. The advantage of a SQL Expression is that the value returned can be used to control things like Selecting, Sorting and Grouping in the report. Of course, if you need to select multiple rows or multiple columns, you will need a subreport. "

(This lecture is from Ken Hamady, www.kenhamady.com)

Is it suiteable to your problem? I think so!