?
Solved

Crystal Report Writer. Using SQL requiring a parameter

Posted on 1998-09-02
8
Medium Priority
?
323 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 480 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

764 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