[RESOLVED] Looping Through Each Record

[faizee36]

Hi everyone
I using Crystal Reports XI release 2. Firstly I should explain what I need to have. In my database I have got a parent table called Initiatives and a child table called benefits. One initiative has got many benefits associated with it and benefits are updated regularly by the users. Now my manager wants me to create an Initiative reports in such a way that all the initiatives are displayed and against each initiative there should be a box filled with either of these colour (red, yellow, green).

Let’s say if Initiative “X” has got 10 benefits and if one or more benefits are outstanding by more than 2% then the colour of the box should be red. And if no benefit is outstanding by more than 2% and one or more benefits are outstanding more then 1% and less then 2% then colour of box should be yellow. And if no benefit is outstanding more than 1% then colour of box should be green.

Now for this I have created a report displaying all initiatives and a sub report with a SQL command containing the data from the benefits table. I have also included a database field which I would use as a box.

Now I want to loop through each record of the crystal report till the last record and format the field as per condition. I would probably use nested loop to look for the status of the benefits but I don’t know how to do this in CR. In short I am looking to do this thing in CR

While Recordset.EOF
Do

But I can’t find a key word for EOF or count-1 or some thing like that.
Seeking the help of you guys.
Regards,

[brucevde]

You don't need a Loop, besides that is exactly what Crystal does. Once the data is grouped, sorted etc Crystal basically loops through the records applying the formulas.

Is the "Outstanding" value a database field or a simple formula. Regardless, from what you posted the report would be quite simple. There would be no need for a Sub-report.

Link the Initiatives and the Benefits table together in the Main Report.
Group the report by Initiative
Sort by the "Outstanding" field or formula.
Suppress the Details section.
in the Group Footer add a Textbox. Use the following for the BackGround color formula of the textbox.

If  {Benefits.OutstandingField} > 2 Then 
   crRed
Else
   If  {Benefits.OutstandingField} > 1 Then  
      crYellow
   Else
      crGreen

[faizee36]

Aaahhhhhhhhh thanks brucevde you are realy champ. it's working now
many many thanks again