• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Report according to input criteria

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
pak_slm
Asked:
pak_slm
  • 3
  • 3
1 Solution
 
dinoang22Commented:
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
 
pak_slmAuthor Commented:
i want to put all output fields of SELECT statement in detail section of the report.
0
 
DRRYAN3Commented:
pak_slm

Are you using Visual Basic or are you prompting for parameters using Crystal Reports?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
pak_slmAuthor Commented:
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
 
pak_slmAuthor Commented:
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
 
dinoang22Commented:
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
 
dinoang22Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now