How can I show, in Crystal Reports 2008, the current and previous record on the same Detail section?

Question

Hello all, I wonder if there is a way in crystal reports to do something like the following:

I have a table with data like this:

    ID       MeshName      RetainedWeight
    654      3.3 inches    20.00
    789      3.0 inches    20.00
    321      1.4 inches    20.00
    ...

And I need to show them in crystal reports like this:

    Title Here                                       %
    Retained 3 inches                               33.3
    Retained 2.3 inches, passing 3 inches           33.3
    Retained 1.4 inches, passing 2.3 inches         33.3
    Retained ... inches, passing 1.4 inches         ... 

So, as you can see I need to show the current record as well as the MeshName of the previous one, any ideas?

I'm thinking: maybe with some variable that retains the previous MeshName...?

Answer 1

If the ID values are always sequential (i.e. by "previous record", you mean the record with current record's ID - 1), you could do it on the SQL side instead of in crystal reports. I assume since you said "table" you're using a database. Here's a complete example (I used Oracle in this example):

CREATE TABLE DATA (ID NUMBER, MeshName VARCHAR2(50), RetainedWeight NUMBER(25,2))

INSERT INTO DATA VALUES (1,'3 inches',20.00);
INSERT INTO DATA VALUES (2,'2.3 inches',20.00);
INSERT INTO DATA VALUES (3,'1.4 inches',20.00);

SELECT 'Retained ' || a.meshname || 
       CASE
         WHEN b.id IS NOT NULL THEN
           ', passing ' || b.meshname
         ELSE
           ' '
       END
  FROM DATA a
 LEFT OUTER JOIN DATA b
   ON b.id = a.id-1
 ORDER BY a.id

The results of the SELECT query are:

Retained 3 inches 
Retained 2.3 inches, passing 3 inches
Retained 1.4 inches, passing 2.3 inches

2. If you want to try to do it directly in crystal, then you can check out the Previous function. Here's a link that might be helpful: http://www.tek-tips.com/viewthread.cfm?qid=1460030&page=9

Answer 2

You can refer to values in the previous or next details row by using the functions Previous and Next.

So in your example, it might be a formula called @Title with the text :

"Retained " & ToText({MyTable.Inches}) & " inches, passing " & ToText(Previous({MyTable.Inches})) & " inches"

You can also use the PreviousIsNull and NextIsNull functions to make sure you don't have problems with the last or first rows.