Solved

Crystal Report Writer. Using SQL requiring a parameter

Posted on 1998-09-02
8
316 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
  • 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

11 Experts available now in Live!

Get 1:1 Help Now