?
Solved

Retrieve data in Crystal Reports using SQL

Posted on 2002-04-08
4
Medium Priority
?
715 Views
Last Modified: 2008-03-10
Hi all,

I am using Crystal Reports 8.5 Developer, pdsoledb.dll ODBC database driver

I need to find out if it is possible to retrieve specific values from a database using SQL or equivalent, separate from the main query / Select Expert.

Here's the situation:

I have an invoice with line items in the details section. The report receives the customer number and invoice number as parameters.

I have 3 tables:

Customer Master
Address Master
Invoice Master

The Customer Master contains 2 separate FK columns (primary billto address, primary shipto address) which each reference a primary key column in the Address Master table. In the Report Header, I need to display both the shipto and billto addresses for this customer.

Now it would make perfect sense to be able to use the SQL expression:
'Select address1, address2, address3, city
 From AddressMaster
 Where AddressMaster.AddressCode = CustomerMaster.PrimaryBilltoAddress
 ...'
somewhere in Crystal Reports and magically be able to format the results, but in all my searches I have yet to come across a way to do this.

However, in the Crystal Reports Help there is a section describing that you can use SQL Expression Fields to enter SQL expressions, but whenever I try to enter SQL, I get a syntax error stating that there is an error near 'Select'.  Is there some mysterious syntax involved here, and if so, where do I find help on this?

thank you in advance
0
Comment
Question by:vast
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 6925589
Can you create the query you want to use in the database and then use it as the data for the Crystal Report?

mlmcc
0
 
LVL 7

Expert Comment

by:EYoung
ID: 6926808
What I have done is to write a small Visual Basic program that prompts the user for the particular invoices they want to print.  Then the vb code creates a temporary table containing all the data for the report.  The Crystal Report is then called which points to the temp table.

I find it easier to control the data selection and formating using VB instead of trying to do it in CR.  Especially if the CR is complex.

Hope that helps.
0
 

Author Comment

by:vast
ID: 6927508
I want to if at all possible to limit all database operation to the crystal report. All I need to know is whether it is possible in crystal to accomodate seperate SQL queries appart from the main report SQL.

So in short, can I use the above mentioned SQL query anyware in my crystal report, seperate from the main report query?
0
 

Accepted Solution

by:
docterminator earned 800 total points
ID: 6927906
You need to add the address tablde again to the report with an alias then use the sql statement to use the aliased table for the shipping address details.

Select address Master.addressID, SA.AddressID
from customer master CM, Address Master, Address Master SA Where CM.BillingcontactID=Address Master.AddressID
AND CM.ShippingContactID=SA.AddressID

SA.AddressID will give you the Shipping address information and the other will give you the billing address information

Thanks and any further explanation let me know

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question