Solved

Report according to input criteria

Posted on 2001-06-30
7
324 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
[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
  • 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
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.

 
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

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

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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 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