Solved

Retrieve data in Crystal Reports using SQL

Posted on 2002-04-08
4
684 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
4 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now