Retrieve data in Crystal Reports using SQL

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
vastAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
docterminatorConnect With a Mentor Commented:
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
 
mlmccCommented:
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
 
EYoungCommented:
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
 
vastAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.