Solved

Report according to input criteria

Posted on 2001-06-30
7
300 Views
Last Modified: 2012-06-27
I want to create report between two dates and create a query using SELECT statement from different tables between these two dates.

Now the problem is

1. How display these dates in header of the report.
2. How use of query fields in detail of the report.

Please help and guide the best procedure.

Saleem

0
Comment
Question by:pak_slm
  • 3
  • 3
7 Comments
 

Expert Comment

by:dinoang22
ID: 6243475
Hi,

To answer you question

1. You can use the Formula field to display dates on the reports directly. Assuming you are using VB 6.0 and Crystal Reports 7, use the following code :-

 Report1.Formulas(0) = "country = '" & txt.text & "'"

Where Report1 is your ActiveX control name. Country is the name of the formula field. Just use your Reports designer and create a formula field, place it somewhere on your report and call the report from the VB program with the code above. Whatever text you have in txt.text, will appear in the formula field. Alternatively, you could use the parameter field, but I prefer to use the formula field. The brackets (), in the formula field denotes the sequence in the report which your formula fields have been created.

2. The detail part of the report is handle by what we call Formula Texts. Here is an example :-

  fmltxt = "{po.vendor} = '" & FVendor.Text & "' and {po_detail.oetd} >= date(" & Yr & ",02,01) and {po_detail.oetd} <= date(" & Yr + 1 & ",01,31)"
  Report1.SelectionFormula = fmltxt

Here you are issuing a SQL statement to retrieve information where the constraint is po_vendor = fvendor and is between the given date ranges. What data appears in the detail section is what fields you have put in the detail section when you during the design stage.

Hope this helps.


0
 
LVL 1

Author Comment

by:pak_slm
ID: 6248581
i want to put all output fields of SELECT statement in detail section of the report.
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6250620
pak_slm

Are you using Visual Basic or are you prompting for parameters using Crystal Reports?
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 1

Author Comment

by:pak_slm
ID: 6251314
I am using Visual Basic and have a form with two text box (for two dates) and a button. on click of button a SQL statement execute and then report call.
0
 
LVL 1

Author Comment

by:pak_slm
ID: 6251340
dinoang22

when i start crystal report it require a database and table. How i give table to start designing report because my output is according to a SQL statement fields.
0
 

Expert Comment

by:dinoang22
ID: 6251461
Hi Pak_slm,

From the questions you are posting it looks like you're pretty new with Crystal Reports. Just for your information, a report design in Crystal Report is based on a SQL statement without criterias. You do not actually need to send the SQL statement to the crystal report because that has already been done when you designed the report.

All you need to do now is to include the Crystal Report ActiveX component in your VB form and use the

Report1.SelectionFormula()

to put the criterias in.

For example, if I want to view the contents of one field called Field1 from the table Table1, I would issue a SQL select statement as :-

SELECT FIELD1 FROM TABLE1

Now, this will list all values for FIELD1, isn't it. Now lets assume you created a report and included TABLE1, then you added FIELD1 in the detail section of the report.

If you now click on Database->Show SQL from the Crystal Reports Designer main menu, you would see a type of SQL statement used by Crystal Reports.

Now if you were to call the report from VB (I assume you know how to do that), you should be able to view the report with all valued of FIELD1 from the table TABLE1.

Now how to you add a criteria??? Assuming our SQL statement is now :-

SELECT FIELD1 FROM TABLE1 WHERE FIELD1="TEST1"

This SQL will list all FIELD1 with the text "TEST1", isn't it. How do you pass this parameter over to crytal report? You use the .SelectionFormula method of the Crystal Report ActiveX control.

fmltxt = "{TABLE1.FIELD1} = "TEST1"
Report1.SelectionFormula = fmltxt

You could replace "TEST1" with a variable from your text box. It could be a date. If you are using date, you have to use the date function as in (Look at my previous posting)

date(2001,02,01)

Your report should now be able to view all records with only "TEST1" in them.

Hope this help.
0
 

Accepted Solution

by:
dinoang22 earned 25 total points
ID: 6251489
Hi Pak_Slm,

Another approch if you want to issue different SQL statements to your report is :-

a) Create different report for each SQL statement, make your VB call to each of them based on its respective criterias.

b) To change the SQL statement in Crystal Report, the only way to do it is to use the CRPE API engine which uses the Crystal Report DLL engine. You would have to use functions like PESetSQLQuery function in the API.

Best Regards,
Dino
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

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. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

813 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

8 Experts available now in Live!

Get 1:1 Help Now