Tutorial 13-8. Copying a Cross-Tab into Arrays

For this tutorial, we are just going to focus on how to copy the cross-tab object into arrays. Since there are so many things you can do with these arrays, I’ll leave it up to you to customize it as your needs dictate.

If you need more examples, Tutorial A-4 in Appendix A demonstrates how to use the cross-tab functions for customizing output. Tutorial A-5 goes into great detail on how to copy the cross-tab into arrays and override the cross-tab data with custom formulas. Those tutorials should provide you with ample code samples so that you can learn how to implement these techniques in your own reports. Unfortunately, the tutorials in Appendix A discuss financial reporting and are very advanced. It might be easier to jump to the parts that discuss customizing the cross-tab object. If you don’t want to read the entire tutorials in Appendix A, look up the GridRowColumnValue() function in the index and read the pages that demonstrate its usage.

When you’re writing a complex piece of code for the first time, it’s more than likely not going to work. There is going to be some small bug in the code that you didn’t notice and will cause it to return the wrong results. That means you have to dig into your code and debug it to figure out what is wrong. The problem with writing code in the cross-tab object is that it is a black box – you have no idea what is going on inside it. If the results are wrong, debugging it can be very difficult.

The way to debug the cross-tab object is to print out the expected results using the Display String property. This lets you see how a variable changes as the cross-tab prints. What I like to do is to start writing the code in very small increments and print test values each step of the way. This lets me immediately find an error and fix it before I go any further. If I wait until the end to start debugging it, it could take much longer to find the problem. In essence, I’m accepting the fact that something will probably go wrong. I test early so that I can catch small bugs before they become bigger problems. In this tutorial, I’m going to walk you through the steps that I use to debug the code in a cross-tab object. This will make the tutorial a bit longer than necessary, but it will be good to demonstrate the process for debugging your own report.

1. Open the CrossTab.rpt sample report that was installed with Crystal Reports. Save it as CrossTab Arrays.rpt.
2. This cross-tab report uses the Order Date as the column field and groups by quarter. This creates a cross-tab object that has many columns and spans multiple pages. For this tutorial, we want to make the cross-tab easier to manage. We are going to have it group by year so that there are only three columns to work with.
2. In Design mode, right-click on the cross-tab object in the top left corner. Select Cross-Tab Expert.
3. In the Columns list to the right, click on the Order Date field to select it.
4. Click on the Group Options button to open the Cross-Tab Group Options dialog box.
5. Change the grouping from For Each Quarter to For Each Year.
6. Click the OK button twice to save your changes and return to the Design tab.
7. Preview the report to confirm that the column header shows the year and that there are three years listed (2003-2005).

8. Click on the Design tab to go back to editing the cross-tab object.
9. We are going to create the formula which creates three arrays (one for each year) and copies the cross-tab data into them. This is done by associating the formula with a conditional formatting property of the dialog box. We don’t want to actually change the conditional formatting, we just want to use it to force the cross-tab object to run the formula for each cell. It’s best to attach it to a formatting property that you never use and have it return a dummy value. Personally, I never use the CSS Class Name property so that is where I’m going to put it. You can use a different property if you like.
9. Right-click on the Sum Of Orders field on the cross-tab object (the field in the first column and on Row #2-Product Name) and select Format Field. This opens the Format Editor dialog box.
10. Select the Common tab and click on the Formula button for the CSS Class Name property.
11. Enter the following formula:

NumberVar CurrentRow;
NumberVar CurrentYear;
if CurrentYear <> Year(GridRowColumnValue("Orders.Order Date")) Then
(
CurrentYear := Year(GridRowColumnValue("Orders.Order Date"));
CurrentRow := 0;
);
CurrentRow := CurrentRow + 1;
"";

12. Click the Save and Close button to save the formula.
13. This formula uses the CurrentRow variable to track the row being printed by the cross-tab. Each time another cell prints, its value is incremented by one. It also uses the GridRowColumnValue() function to find out which column is being printed. If the column is different than the previous column printed (the CurrentYear variable), we know that we are at the top of a new column and at the first row. When this happens, the CurrentRow variable is reset back to zero.
14. The last line of the formula is important because it returns an empty string. You have to be careful when attaching a formula to a non-related field property so that you don’t change the property. By returning an empty string, we make sure that the CSS Class Name property stays unchanged. If you are using a different property in your own report, make sure to return the appropriate value so that the formatting doesn’t change it.
15. I mentioned earlier that we’ll test the code periodically to make sure it is working as expected. To do so, let’s print the CurrentRow value on each cell to see if it is really being incremented the way we expect it to.
13. The Format Editor dialog box should still be open. Click on the Formula button for the Display String property and enter the following code:

NumberVar CurrentRow;
Cstr(CurrentRow,"#");

1. This formula first declares the CurrenRow variable so that it can be shared with the previous formula. Next, it converts it to a string so that it can be displayed in the cell. It has to be converted to a string because the Display String property only accepts strings.
14. Click the Save and Close button to save the formula.
15. Click the OK button to close the Format Editor dialog box.
16. At this point, we’ve created a variable to track the current row and print it in the cross-tab cell so that we can confirm that it is working properly. If you preview the report, it should look like the following figure:

1. This figure demonstrates two things. First, the row counter is working correctly because it increases by one for each row printed. At the top of each column, it resets the counter and starts counting from scratch. Second, it proves that the cross-tab really does generate data by going down each column before starting at the next column.

It’s important to look at this figure and notice that the row number has only been printed in the detail cells for the Product Name field. The other fields and sub-totals have not been overwritten and still have their original values. This is because the cross-tab object treats each field separately. This can be good or bad. It is good because you have the option of deciding which data you want to capture. If you want to perform custom calculations on a certain field, then you want to capture only that field’s detail data. If you want to use the totals in another part of the report (or referenced by a sub-report), you will capture the values in the sub-total cells. Breaking this out into two different fields can be bad if you want to apply formatting or calculations across every value in the column. You will have to duplicate your code in all the detail fields and sub-totals to apply it to the entire column.

16. Now that we know that the row counter is working correctly, let’s modify the formula to create three arrays that store the cross-tab data. Open the Formula Editor for the Sum of Orders field again. Click the Formula button for the CSS Class Name property.
17. 17. Update the existing formula to the following code. The first half is unchanged.

NumberVar CurrentRow;
NumberVar CurrentYear;
if CurrentYear <> Year(GridRowColumnValue("Orders.Order Date")) Then
(
CurrentYear := Year(GridRowColumnValue("Orders.Order Date"));
CurrentRow := 0;
);
CurrentRow := CurrentRow + 1;
//Store the data in arrays
CurrencyVar Array Col1;
CurrencyVar Array Col2;
CurrencyVar Array Col3;
ReDim Preserve Col1[50];
Redim Preserve Col2[50];
Redim Preserve Col3[50];
Select CurrentYear
Case 2003:
Col1[CurrentRow] := CurrentFieldValue
Case 2004:
Col2[CurrentRow] := CurrentFieldValue
Case 2005:
Col3[CurrentRow] := CurrentFieldValue;
"";

This formula creates three arrays and populates the correct one based upon the current year being printed in the column header. Notice that I dimensioned the arrays to have 50 elements so that the cross-tab has room to grow.

18. To test that the arrays were populated correctly, we’ll print their values. Go back into the Format Editor and click on the Formula button for the Display String property.
19. Enter the following formula:

NumberVar CurrentRow;
CurrencyVar Array Col1;
CurrencyVar Array Col2;
CurrencyVar Array Col3;
Select Year(GridRowColumnValue("Orders.Order Date"))
Case 2003:
CStr(Col1[CurrentRow])
Case 2004:
Cstr(Col2[CurrentRow])
Case 2005:
Cstr(Col3[CurrentRow]);

1. This formula declares all the array variables so that it can share them with the other formula. Then it uses the GridRowColumnValue() function to determine the current column and print the array associated with that column. If you preview the report, the data in each cell should be the same data that was printed prior to us making any changes. Look back at the figure in Step 7 to see if your cross-tab object has the same values.
2. Since this data is coming from the array, we know that the array is truly an exact copy of the cross-tab data. The formula works perfectly and we can now use this data elsewhere in the report or use it with custom functions within the cross-tab.
20. We need to remove the formula from the Display String property so that we aren’t overriding the cross-tab data anymore. Open the Formula Editor dialog box and click on the Formula button for the Display String property. Delete the formula and save your changes.