Solved

Retrieve data in Crystal Reports using SQL

Posted on 2002-04-08
4
700 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 100

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 200 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: 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.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Suggested Courses

751 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