Can anyone out there show me how to use Crystal reports to solve two problems that I am facing when printing.

Let me outline the steps that I used to develop the bound formatted Report named EndTermResult using Crystal ActiveX control.



On Visual Basic Form I have the following controls

·        Data control

·        Crystal ActiveX control

·        Command Button

On the Data Control:

·        I have set the DatabaseName property to the name of the database being reported on.

·        On the RecordSource property I have used an SQL query statement.

On the Crystal ActiveX control:

·        I have the DataSource property to the Data control

·        Set the ReportSource to 3-All Data Control Fields.

·        Open the Custom property and select the Data-Bound Report Tab

·        I Clicked on the Save Report AS button and entered a name for the report.

·        Set the ActiveX control to print to a preview window, and run the application

·        I clicked on the Export button in the Preview window, and export the report to a disk in .RPT format.

·        Then I opened it up in Crystal Report.

·        Performed all formatting changes that I needed and saved the report

·        Set the ReportSource to 0-Report File

·        Set the reportFileName to the .RPT file

On the Command Button:

            Private sub Command1_Click ()

                        CrystalReport1.Action =1

            End sub



Now I want to print EndTermResult with fields:



LastName,FirstName,Math,English,Physics,Chemistry and Biology


LastName   FirstName    Math   English   Physics  Chemistry   Biology  TotalMarks
David            Levi              90             80          80              90            70            410

John             Mark            90             70          80              90           70              390

Joseph         Haroun          80            80          70               80           70              390

Mary            Ann              70            80          90               70            65              375

Ranganathan   Madhuben  60            90          80               65             70             365

Lee            Chang           90            60           65               70            60              345

Mary          Wanja            70            90           60               60            65             345

Hanghton    Spencer         90            80          60                50           40              320

Charles       Andreas        70            60           70               50            40              290

George       Michael         90             40          40               60            50              280



But I would like the table above to include another field name Pos to indicate the position of the students in a class and be sorted by field TotalMarks in descending order and where Totalmarks are equal to skip number accordingly in Pos field to indicate a tie.

For example the above table, I would like it to appear like the one below for the Final result.

Pos    LastName      FirstName    Math   English   Physics  Chemistry   Biology  TotalMarks

   1      David            Levi            90       80          80           90             70            410

   2     John             Mark           90       70          80           90             70             390

   2    Joseph         Haroun         80       80          70           80             70             390

   4     Mary            Ann              70       80          90           70             65             375

   5    Ranganathan   Madhuben  60     90          80            65             70            365

   6     Lee            Chang            90        60          65           70            60             345

   6    Mary          Wanja             70        90          60           60            65             345

   8    Hanghton    Spencer         90       80          60           50            40             320

   9    Charles       Andreas         70       60           70           50            40             290

10    George       Michael         90        40           40           60            50             280

From the table above there are no Numbers 3 and 7 in field Pos for there are ties in positions 2 and 6 (TotalMarks are equal in both cases)

Please help me how to go about it.



NB: TotalMarks field is a calculated field by adding all subjects’ fields.



Second problem is to print a customized report for each student from the same formatted bound report and include three headings: Grade, Points and Comments in a table. The contents of the headings Grade, Points and Comments are determined by what the student get on the Score field.  

I would like the format to appear like the example below for every student.



FULLNAME: Hanghton             Spencer

Subjects            Score                       Grade              Points                   Comments


Math                       90                       A                       12                       Very good
English                    80                        B                        8                        Good

Physics                   60                        C                        4                        Average

Chemistry                50                        C                        4                        Average

Biology                    40                        D                        1                        Weak



Apart from the Names, Subjects, Score from the table above that show well when it runs, including headings: Grade, Points and Comments do not even let the table run. Even if it were to run, how do I print the table above for a single student based on AdmNo.field.



NB: Even though the AdmNo field is not shown in the preceding example it is there only that I did not include it in the SQL statement.



Let me print the code that led to my failure:


In order to get the variables for grade, points and comments I declared them on same math line as Grd, Pnt, and Cmt in text fields.



Illustration:

FULLNAME: LastName                    FirstName



Subjects         Score               Grade             Points             Comments
Math                ###                  Grd                  Pnt                   Cmt



After declaring variables I switched to the formula field from Insert menu. I entered Mathline as the name for the formula field.

According to the Crystal Report Pro documentation you have to declare the variables before using them, just like VB but with data types and syntax that I find strange.

By modifying example given to suit my need I came up with the following:-



Illustration:-
Number score := {BoundControl . MathTerm1}

String Grd

Number Pnt

String Cmt

If score >= 90 then
Grd = “A”;

Pnt = 12;

Cmt = “ VeryGood”

Else
If score >= 70 then
Grd = “B”;

Pnt = 8;

Cmt = “Good”

Else
If  Score >= 50 then
Grd  = “C”;

Pnt = 4;

Cmt = “Average”

Else
If Score <=49 then
Grd = “D”;

Pnt = 1;

Cmt = “Weak”

Else
“   ”

I assumed if it works well with math subject, it could work the same with rest of subjects by repeating the same procedure for every subject. Now my woes begin when I crick the check button in formula window. I get the error message. Formular Parser Error:

'The remaing text does not appear to be part of the formula'



Given I am Newbie to the Crystal Report, Can Someone out there help me to archieve the desired result.



I will appreciate even more when the code is provided.

I am using VB6 and Access97 as my database.



Thanks.


            

                        
      

This is called a looong question.

quote:

---

But I would like the table above to include another field name Pos to indicate the position of the students in a class

---

quote:

---

Apart from the Names, Subjects, Score from the table above that show well when it runs, including headings: Grade, Points and Comments do not even let the table run. Even if it were to run, how do I print the table above for a single student based on AdmNo.field.

---