Referencing Rows and Columns

Tutorial 13-3 looked at how to format individual cells with the CurrentValue function. You saw how to use conditional formatting to change the cell properties based upon what was being printed. The cross-tab object also has a function that lets you determine which row and column the current cell is located in. This lets you make changes across an entire section of the cross-tab object. Rather than focusing on individual cells, this gives you a higher level view of what is being printed.

The function GridRowColumnValue() returns the value of the row or column that the cell is in. Pass it the name of the group field (either the row’s field name or the column’s field name), and it returns the current value. For example, if the column groups by months of the year, passing the function the date field will result in the third column returning the month name “March”. A formula can look at both the current row value and column value to determine the cell’s exact location within the cross-tab. In most cases, you’ll probably just use one field to apply a specific formatting to an entire row or column. The following line of code returns the Product Type Name of the current row being printed.

GridRowColumnValue(“Product_Type.Product Type Name”);

Notice that you need to pass both the table name and the field name to the function. You have to type it in exactly as it is shown in the cross-tab expert. It might be easier to create an alias to refer to the row or column field. This is done by going to the Cross-Tab Expert dialog box and selecting the Customize Style tab. Click on the row or column field you want to modify and enter a new name in the Alias for Formulas input box (located in the Group Options section). Now the GridRowColumnValue() function can reference the alias name instead of the field name. For example, if you assign an alias for the Product Type Name field and called it Product Type, then you could change the previous line of code to the following:

GridRowColumnValue(“Product Type”);

Using an alias makes the field name a little shorter and you no longer have to reference the table name either.