Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Report according to input criteria

Posted on 2001-06-30
7
Medium Priority
?
339 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 50 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

650 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