472,805 Members | 918 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Extracting data in SQL server using crystal report

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?
May 15 '07 #1
11 17629
Motoma
3,237 Expert 2GB
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.
May 15 '07 #2
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.
May 15 '07 #3
Motoma
3,237 Expert 2GB
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.
May 15 '07 #4
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.
May 15 '07 #5
iam_clint
1,208 Expert 1GB
SELECT name, address, age FROM t1
where age > 20
May 15 '07 #6
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?
May 17 '07 #7
Motoma
3,237 Expert 2GB
I am not sure what aspect of the original solution I posted was not acceptable for you.
May 17 '07 #8
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]'
May 17 '07 #9
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
May 18 '07 #10
Motoma
3,237 Expert 2GB
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]'
May 18 '07 #11
hnminh
1
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!
Jul 31 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mike | last post by:
Hello All, I'm trying to deploy my windows-based application using crystal report. i package the setup by including the crystal report file, the exe file, and two merge modules for the crystal...
0
by: Ray | last post by:
Hi all, While I use the ReportDocument of vb.net to set filter to the data to the Crystal Report, it is successful for the first time. However, for the second time, when I set filter to other set...
6
by: Ray | last post by:
Hi all, While I use the ReportDocument of vb.net to set filter to the data to the Crystal Report, it is successful for the first time while running the program. However, for the second time while...
0
by: Ray | last post by:
Dear Bernie, Yes, if the same pfieldname is used. The same data will be filtered. But I only use the code shown you before. I have not used any dataset or datatable. How to fix the problem?...
0
by: edward zhang | last post by:
I have a VB.Net application which uses the Crystal Report(Crystal Report 8.5) Viewer to open reports. One of the reports uses a stored procedure and another one uses tables as data resource, both...
0
by: Rabbit | last post by:
recently I monitor my web application using Crystal report for .net, I notice everytime I call CrystalReportView control to display the report, the memory usage go up by 2MB, upto certain level,...
2
pureenhanoi
by: pureenhanoi | last post by:
i'm using VB6 to create application. My application connect to database (MS-SQL Server) through ODBC. i'm using Crystal Report too. But there is a problem here: if my SQL Server does not need...
1
by: gzali | last post by:
hi, i want to send parameter on web by using crystal report,the problem is that i am using crystal report 8.5 i am using a heperlink on a report filed, when the user click on the heperlink the...
0
by: Jason7899 | last post by:
hi Iím creating a small program to draw up an estimate and after that I want it to be printed using the crystal report 7, and I wonder if someone has a similar sample program. I also thought of...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.