Solved

Report according to input criteria

Posted on 2001-06-30
7
280 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
Comment Utility
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
Comment Utility
i want to put all output fields of SELECT statement in detail section of the report.
0
 
LVL 12

Expert Comment

by:DRRYAN3
Comment Utility
pak_slm

Are you using Visual Basic or are you prompting for parameters using Crystal Reports?
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Author Comment

by:pak_slm
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

772 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