Solved

Retrieve data in Crystal Reports using SQL

Posted on 2002-04-08
4
696 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: 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

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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