Solved

Crystal Report Writer. Using SQL requiring a parameter

Posted on 1998-09-02
8
322 Views
Last Modified: 2010-04-30
I have SQL statement designed in Access. I want to write a report using Crystal report writer in VB5 using the SQL statement as the data source. The problem is My SQL statement requires a parameter and it is not listed when designing the report.

The SQL statement is

SELECT * FROM customer
WHERE customer.name = anyname

I want to dynamically pass anyname to Crystal report and have it display a report.
Please let me know if that can be done.
Thanks
0
Comment
Question by:g6yassin
[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
  • 5
  • 3
8 Comments
 
LVL 2

Accepted Solution

by:
BergJC earned 120 total points
ID: 1432626
Set the following property of the Crystal control:

CrystalReport1.SelectionFormula = "{customer.customer_name} = " &  '" & anyname & "'"
0
 
LVL 2

Expert Comment

by:BergJC
ID: 1432627
Here's the rest of the code to print a Crystal Report (of course, you need to place a Crystal Report control on your form):

CrystalReport1.ReportFileName = "C:\wherever\page01.rpt"
CrystalReport1.Action = 1

There are many other things you can set in VB for a Crystal report, but this is a way to simply print a normal report to the default printer. Let me know if you need more.
0
 

Author Comment

by:g6yassin
ID: 1432628
The report writer does not list the SQL queries that require a
paramer during design time. How can get I get it into the design
area in the first place.
Steps I took.

Start Crystal report writer.   select the white key under "file"
"Create New Report" window opens.   select "standard" . New window opens.  click "Data file", then "choose database" opens.
I select my Access data base where the SQL query I want to use in my report is designed and stored.
All other tables and SQL queries that do not require parameters are listed but the SQL queries that require a parameter are not listed.
How can I get it listed in order to use it in my report design.
I tried the other key "SQL/ODBC" now every thing is listed but
as a beginner I find more errors that I can't underestand.
I will be grateful if you take a moment and try to explain to me
as a beginner. I'll raise the points.
thanks
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 2

Expert Comment

by:BergJC
ID: 1432629
I'll have to look into it further. If you select the "Data file", Crystal assumes you are using the normal jet databases. If you select the "SQL/ODBC" button, it assumes you are using ODBC. It shouldn't make a difference to what fields show, but you should probably use the "Data File" one.

However, there are easier ways to place queries in your Crystal report. What I showed in my first answer was an example of that. With that, you place all the fields you want displayed on your report. You don't have to worry about the query yet. In your example, you'd place all the fields from the "customer" table where you want them on the report (not the query fields, just the ones from the table). Now, at run time, when you set the (CrystalReport1.SelectionFormula = "{customer.customer_name} = " &  '" & anyname & "'"), it will only show records that have a customer_name equal to any variable you want in VB (i.e. "anyname").

If you have to display your SQLquery in the report...you might see if anyone else has a solution. In my experience, I've found this method has always been easier when I need to use a user-entered variable. I can walk you through it if it sounds like something you can use.
0
 

Author Comment

by:g6yassin
ID: 1432630
Your last answer is correct an it does most of what I wanted.
I raised the points to 100 to interest you this topic further.
I plan also to give you an "A" because of you correct answer and
your prompt and clear explaination. However, I would like you to look at this situation. I have the following query

SELECT *
FROM customer INNER JOIN (account INNER JOIN hasAcct ON account.acctnum = hasAcct.accntnum) ON customer.ID = hasAcct.ID
where account.balance > maxBal AND
             account.lastupdate < twomonthsAgo;


The tables are customer=>hasAcct=>account
parameters are "maxBal" and "twomonthsAgo"
Do you have an idea if I can use the information produced by the above query in Crystal Report and if that is posible How?

It can't be done is also a good answer.

Thanks
0
 
LVL 2

Expert Comment

by:BergJC
ID: 1432631
That's a little more difficult, but I think you can do it. Crystal Reports uses something called "linking" to join 2 or more tables together. This should do what you are looking for. Try this:

In Crystal, click on the "Database" menu choice, and then "Visual Linking Expert". There should be a button called "Tables". Click on that and from there, you can select which tables you want to join. After you finish that, it will display your tables with arrows linking up to your key values. Hopefully, the fields you are joining on are index values. Crystal only allows linking on key values. You can play around on this screen to get your tables joined as you need. You'll notice that you can move, delete, and/or add arrows as needed. Just make sure that arrows are pointing from {account.acctnum} to {hasAcct.accntnum} and from {customer.ID} to {hasAcct.ID}. You probably won't want any more arrows linking up other fields. When you are all finished with that, the report should already be joined based on those values, which leaves only one more thing. Save your report and go to VB. In your VB app, you now need to send the parameters to the report through the control.

CrystalReport1.SelectionFormula = {account.balance} > " & "'" & CStr(maxbal) & "'" & " and {account.lastupdate} < " & "'" & CStr(twomonthsAgo) & "'"

Whew. Get all that? I'm throwing this out by memory, so if you need more help, let me know. That should at least get you in the right direction.
0
 

Author Comment

by:g6yassin
ID: 1432632
Excellect!
Thanks BergJC, Can you point me anywhere where I can get a reading material about Crystal Report. Like Book, Web tutorial
. It seems I can't find anything.
Hey, more points for you.
0
 
LVL 2

Expert Comment

by:BergJC
ID: 1432633
That's an excellent question. I wish I had a great answer for you, but Crystal Reports isn't well documented anywhere. Especially in the manual that comes with it, as I'm sure you've found out. The only useful site I know of is the Crystal Reports User Group page (http:\\www.crystaluser.com). There a chat board/BBS feature there that can connect you to other users. There might be a few other things you can check out too.
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

726 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