accessing previous record fields in crystal reports

1
votes

I'm displaying grades for student records. They sit a lower year (AS ...), then choose to continue certain courses to the upper year (A ...). This means that I have the following dataset for each student:

+----------------------+-------+-----------+-----+
|            Name      | Grade |    End    | Code|
+----------------------+-------+-----------+-----+
| A Business           | C     | 06-Jul-12 | BUS |
| A English            | B     | 06-Jul-12 | ELL |
| A History            | C     | 06-Jul-12 | HIS |
| AS Business Studies  | E     | 08-Jul-11 | BUS |
| AS Critical Thinking | B     | 08-Jul-11 | CRT | <-- lower year only
| AS English           | D     | 08-Jul-11 | ELL |
| AS History           | F     | 08-Jul-11 | HIS |
+----------------------+-------+-----------+-----+

For each student record I only want to display the grades sat at both A and AS level. In the above example that would be suppressing AS Critical Thinking:

A Business           | C
A English            | B
A History            | C
AS Business Studies  | E
AS English           | D
AS History           | F

Without editing the underlying dataset is there a way that Crystal Reports can suppress those records that only appear as AS grades? For example:

If Code not present in records AND Name starts with AS
  Suppress
Else
  Display
2
crystal-reportscrystal-reports-2010
Do you care about the order? It would be easiest to group by course code, but then your report would no longer be sorted by class name. – Ryan
Sorry meant to add that each student has one of these lists of records. I'll try and make the question a little clearer – pluke
Right, but does the order that the classes appear for each student matter to you? – Ryan
it would be great to have the A courses followed by the AS courses. But it's not too much of a nightmare if they aren't – pluke

2 Answers

2
votes

Assuming all of the "A" values come in before the "AS" values, I'd use an array to store the codes found so far.

First, initialize the array, this can be in the report header or if the results are grouped by student, in the student group header.

WhilePrintingRecords;
Global StringVar Array CodesFound;

Redim CodesFound[1];

CodesFound[1] := "";

//print nothing
"";

Then, update the list in the details

WhilePrintingRecords;
Global StringVar Array CodesFound;

If Left({table.Name},2) = "A "
    Then (
        If CodesFound[1] = ""
            Then (
                CodesFound[1] := {table.Code};
            )
            Else (
            Redim Preserve CodesFound[UBound(CodesFound) + 1];
        CodesFound[UBound(CodesFound)] := {table.Code};
        );
    );
//print nothing
"";

Finally, in the conditional suppression section:

WhilePrintingRecords;
Global StringVar Array CodesFound;
Local BooleanVar found := false;
Local NumberVar i;

If Left({table.name},2) = "AS"
    Then (
        For i := 1 to UBound(CodesFound) do (
            If CodesFound[i] = {table.Code}
                Then found := true;
        );
        //suppress if not found
        Not(found);
    )
    Else
       //Not an "AS", don't suppress
        false;
1
votes

** edit **

I better solution to the implied question would be to join the logical table to itself on the Code field:

-- Oracle syntax
WITH

V AS (
  SELECT Name, Grade, End_Date, Code
  FROM   ...
)

SELECT *
FROM   V
INNER JOIN V V2 ON V.Code=V2.Code
  AND SUBSTR(V2.Name,1,2)='A '

If necessary:

Add a conditional-suppression formula to the Details section:

IsNull({table.Code}) AND Left({table.Name},2)="AS"