Managing the Columns

One of the most interesting features of the cross-tab object is that it is dynamic. The number of columns changes according to the data being displayed. As an example, assume that you have a cross-tab report where the columns represent the historical sales figures per year. If a company has been in existence for five years, then there will be five columns printed. Once a new year starts, a new column is automatically added to the cross-tab. Columns are created on an as needed basis without requiring any additional work on your part.

Dynamic columns are a blessing and a curse. The fact that you can even have dynamic columns is great. Being able to have the number of columns grow and shrink according to the data in a table is very powerful. You can’t do this with standard reports objects. The drawback is that you can’t control how many columns are created and you can’t insert additional columns.

Having columns dynamically added to your report is a problem when you get more columns than you expected. For example, your company has twenty divisions world-wide and you formatted the cell’s width so that there is just enough room on the page to represent each division. The report runs fine until six months later when your company acquires three new divisions. Now your columns run off the edge and onto a new page. The number of pages printed has just doubled and everyone is complaining to you about it. You have to watch out for this behavior and correct it if necessary.

When a cross-tab object spans multiple pages, the only data printed on the “virtual” pages is what is generated by the cross-tab report. The other report objects on the report do not get printed. If there is a header value that should be printed above the cross-tab, it will not appear after the first page. You can simulate a report header by adding an additional column field to the cross-tab object that only prints the header text.
To simulate a cross-tab header, follow these instructions. Create a formula that generates the header text. Open the Format Cross-Tab Expert and add the formula as the first field in the Columns window. Go to the Customize Style tab and experiment with the grid settings to make the report look just the way you want. You will probably have to turn on Suppress Row Grand Totals and turn certain grid lines off. An example is shown in Figure 11-11.


Figure 11-11. Adding a heading to the cross-tab object.

Another problem with dynamic columns is that you can’t insert additional columns in the grid. This is a common problem with reports that use the month of the year as the column. When the report is run as the end of the year there are twelve columns, and this is what you would expect. But when the report is run in February, only two columns are printed: January and February. Some people want their reports to show all twelve months even if they haven’t occurred yet. Cross-tab reports won’t do this. A similar problem is a report that uses the weeks of the year as the column heading. Assume a company is a production plant and they want to see the volume of units produced every week. Occasionally, the plant builds up too much inventory and is shut down for a week. The report should show a zero balance for the week that was shutdown. But since there wasn’t any activity, no records exist for that week and a column won’t be printed. The cross-tab report can’t print a zero-filled column for that week because it doesn’t have any data to even know that the week exists.

There really isn’t an easy solution for this. The best advice is to write a SQL query that uses a creative Outer Join statement to generate zero value data for the missing records. Another option is to create a zero filled table with a record for each column. Use a SQL Union statement to join it with the live data. Each situation is unique and presents a new challenge.