Moving values backward in a report

Tuesday 7 April 2009 @ 10:21 am

Here is a technique I have named the “wormhole”, because it lets Crystal go back in time.  It allows you to take one or several database values from the last record of a report, and then display them or use them in formulas that occur earlier in the report.  Likewise, it can take the last record of a group and print it’s values anywhere in the group, including the group header.  Variables can’t do this because they can only be used ‘downstream’, and subreports can slow things down significantly, so this method has some advantages.  Your report must be sorted, and the values you want to move have to be database values or formulas that can be totaled. Unfortunately, PrintTime formulas can’t be moved using this method. For that you would need a subreport.

Lets say that you have 300 orders sorted by a DateTime field in ascending order.  You are not allowed to change the sort of the report.  But, you need to start the first page of the report with the Date, Amount and Shipping method of the last order on the last page:

First you create the WormHole formula.  You do this by converting the sort fields into a single, fixed-length, text string.  The string elements must be formatted so that the string will sort just like the report.  That means that Date and DateTime fields should be converted to a string that is in YYYY-MM-DD hh:mm:ss format.  Numbers need to be padded with zeros on the left so that they are all the same length.  Strings should have spaces added to the right and then trimmed back off so that they are all the exact same length.  Then append these strings together placing the primary sort on the left and the others added in the correct order.  This combined string will then sort exactly as the report does.  Then if you calculate the Maximum of this formula you should get the value that matches the last record.

Next, append the fields to be moved onto the end of this string.  They also have to be converted to strings in the same way as the sort fields.  The following example starts with the sort field (DateTime) and adds the Order Amount and the Shipping Method.  All three are converted to fixed length strings:

ToText({@DateTime},'yyyy-MM-dd hh:mm:ss')  &
ToText({Orders.Order Amount},'########.00',2,'')  &
Left ({Orders,Ship Via} &
ReplicateString(' ' , 11), 11)

This formula, if placed on the details band, would generate values like these for the last three orders:

"2002-05-29 17:15:02     245.12Fedex      "
"2002-05-30 12:25:00    2346.14UPS        "
"2002-05-31 14:13:06   14512.15Purolator  "

Last, I write the following formula:

Maximum( {@WormHole} )
[30 to 42]

The first line gets the Maximum of that formula, which is the combined string of all 3 fields on the last record.  The second line extracts the Shipping Method from the string.  I can display this formula on any page of the report and see the same value.  Taking positions 20 to 30 would give me the Order Amount from the same record.  It would be a string but I could even add the VAL() function to make it into a number.  You can append as many “passenger” fields to the wormhole formula as are needed.

This also works within groups.  If you need to take values from the last record of a group, and use them at the beginning of that group, use a formula like this:

Maximum({@WormHole},{Your.GroupField}) [20 to 30]

By putting {Your.GroupField} in as an argument you are asking for the maximum (or last) value of each group, instead of the last for the whole report. There are even ways to add conditions to the WormHole value so that only specific records apply.  Contact me if you need help developing a formula using this method.  And if you like to learn methods like this you should get my 3 volumes of  “Expert Techniques“.  Each volume has 30 articles showing methods like this.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server