Using Arrays to Store Cross-Tab Data

Using the CurrentValue and GridRowColumnValue() functions gives you excellent control over formatting the cross-tab object. But you can get a lot more power by combining these two functions together. You can use them to store the entire cross-tab object in arrays. These arrays can be used to overwrite the cross-tab data with your own custom formulas or you can reference the arrays elsewhere in your report.

The GridRowColumnValue() function returns the current row or column being printed in the cross-tab object. You can use this to determine the X-Y coordinates of the current cell and store its value in the proper array. Use the CurrentValue function to determine what value should be stored in the array. As the cross-tab object is being created, you can use these functions to duplicate each value in an array.

To copy the cross-tab values into arrays, you need to know how the cross-tab object is built internally. The cross-tab object creates its values by going from top to bottom and then left to right. It starts at the first column of the first row and prints down the cross-tab object till it gets to the last row. Then it starts at the top of the next column and works its way down again (see Figure 13-11).

Figure 13-11. Print order of the cross-tab object.

As the cross-tab object is created, you need to copy each value into the array. There are two challenges here. The first challenge being that Crystal syntax doesn’t have a multi-dimensional array data type. Instead, you have to use a single array for each column and pair them up. Matching columns with arrays can be difficult because you have to know your data well enough to know how many columns there are ahead of time. This allows you to plan the number of arrays you’ll need and to think of a proper name for each one. This technique works best with reports that have a limited number of columns (e.g. the months of the year). For cross-tabs that have a dynamic number of columns, you will have to write a lot of code to account for each possible one.

The second challenge with using multiple arrays is knowing when to switch to the next array. Since each column is printed consecutively and there is one array for every column, you will completely fill each array one at a time. It’s important to know the printing order because you need a way to determine when to quit populating one array and start on the next array. You can use the column header value to determine when to start a new array. By comparing the current column value to the previous column value (stored in a variable that you track), you know that when they are different, the new column is starting and you have to start populating the next array.

Since you are storing the entire cross-tab object in arrays, you get the benefit of letting the cross-tab cells reference each other. This lets you create your own summary functions that aren’t allowed by Crystal Reports. For example, you could use the cross-tab object to sum the data in the detail columns as normal. But you can override the Total column to display the average of the three columns. As another example, you could calculate a running total across each row. Of course, this isn’t a trivial matter because it is going to take a decent amount of code to create the arrays and perform the calculations. But, after going through the tutorials in this book you should have enough practice to do it without too much trouble.

If you want to populate the cross-tab with new data, Crystal Reports lets you override the current cell value using the Display String setting. It is found in the cross-tab’s Format Editor dialog box on the Common tab. You can use the Formula button to display any string in the current cell of the cross-tab. This formula can take existing values from the cross-tab object (using the arrays you built) and create custom formulas for any column. Taken to the extreme, you could use the Display String setting to build your own cross-tab objects.

It’s important to realize that each column can only reference the columns that come before it. Since columns print consecutively, you can’t reference a future column. For example, when column three starts printing you know that the arrays for columns one and two have already been filled. You can use their values in a calculation. But since column four hasn’t been printed yet, this array is still empty and those values can’t be referenced yet.

A second use for copying the cross-tab object into arrays is that you can use these arrays elsewhere in your report. This lets you use the cross-tab object for calculating the summary functions at the beginning of the report and reference those totals in different calculations elsewhere in the report.