Data and Analytics Resources

Provide Confident Assurance to Your Organization

Our Top 5 Challenges in Making a Complex Crosstab Look Like a List

by Mary Bolen

Nov 30, 2015

You may be asking yourself, “Why not just use a list? Why complicate things by making a crosstab resemble a list?” The answers are simple: Better Performance and less maintenance.

Performance on the original report was slower than preferred, mostly because of the amount of logic built into the individual data items. The data items for the Exams displayed in the report contained a DECODE function in order to pivot the data in the list. Although using the DECODE function met the original business requirements for viewing the data, it was in about 15 data items per report and noticeably slowed down the report run time.

Also, the list was built to only show the Exam columns containing data. Therefore, each column had a render variable on it that would check for data at run time, and display the column if the Exam had populated values. This also added many seconds to the run time of the report.

The second reason for making the switch from the list to a crosstab, was for easier maintenance.   Each time a new Exam was added, the reports had to be updated manually. Multiple data items for each Exam were added, along with a new column and new render variable.   This was done in two different versions of each report, and sometimes across multiple subject areas. Add in testing of the manual report changes ,and this update could take multiple days. By switching to a dynamic crosstab, the new Exams will automatically appear in the crosstab as soon as the data has been properly loaded in the data warehouse.

Although these two reasons for the switch made the move to a crosstab advantageous, it was not without its challenges. I’ve narrowed it down to the top five challenges we faced, and how we overcame them.

Challenge #1: Column Headers

The column headers needed to resemble those in a list. Unfortunately, crosstabs do not have headers. The crosstab space has to be manipulated in order to create a header.

The method used as the solution for this challenge was one from an IBM technote.

  • Drag a crosstab space to the right of the crosstab corner.
  • Unlock the report and drag data items into the crosstab space that you want to use for the headers.   Change the source type to Data Item Label.
  • Set the Crosstab Corner to Box Type “None”.
  • Set the Crosstab Cells for the newly created Crosstab Spaces to Box Type “None”
  • Repeat these steps for the number of column headers you need in the crosstab.



Challenge #2: Dynamic Columns

In order to gain the full benefit from switching from a list to a crosstab, the columns needed to be dynamic and pull in only the Exams that had data values.   To make this work, we dropped the measure of the Exam into the Crosstab Node Member. Then, we unlocked the report, and used the data item value instead of the data item label for the measure. This way the crosstab would create the same amount of columns as the criteria being used. In this case a column would appear for each Exam with data.

crosstab3     crosstab4

Challenge #3: Freeze Columns

Due to the number of columns in the report, we needed to be able to freeze the first two columns of crosstab that contained student information. This is important for two main reasons. One, with the columns frozen, the users can scroll to the right and still keep the relationship between the student and the Exams. Two, due to the resolution of the screens of the users, we were working with a limited amount of virtual real estate on the report page. By adding the ability to freeze columns and scroll, we prevented the crosstab from being too wide, and made the report more manageable and viewable.

In order to achieve the freeze columns capability, we used the IBM Proven Practice for freezing row and column headers in a crosstab or List. This is something that we had implemented in the List prior to the changeover, but revisited the steps to make sure there weren’t any unknown nuances in applying this solution to the crosstab.

Challenge #4: Conditional Formatting

Two different types of conditional formatting needed to be used in the Crosstab. The first being the data format of the Exam score, and the second being a color coded formatting based on the range of the Exam score (Red, Green, Yellow). Sounds easy enough right? Well, not so fast. In the list we were able to add the data items that were used for the conditional formatting as properties of the list. So how do we do that when a Crosstab doesn’t have those same properties? The solution was to nest the necessary data items in the crosstab as Crosstab Node Members and then set them to Box Type None so they were hidden. Now they are in the Crosstab and have the relationship needed with the Exam to create the conditional formatting, but those data items do not show up when the report is run.


Challenge #5: Excel Output

There were two small challenges when it came to the Excel output. The first is related to the column headers. Remember in #4 when we had to nest data items in order to get the conditional formatting to work? Well those nested Crosstab Node Members were causing the headers to merge across two rows in the Excel output.   Our users love Excel, and need a convenient way to sort in Excel. With the headers spanning two rows in the output, it would make sorting more tedious and difficult. To correct this problem, we added a hidden crosstab space to the top of every column to match the columns that had a nested item. A crosstab space was dragged in and dropped above each Crosstab Node Member across the top of the crosstab (excluding the ones that already had a nested item), and then the Crosstab Spaces were set to Box Type None.

The other challenge with the Excel output was that the output varied between different versions of Excel. We overcame this challenge by setting a standard for Excel output and having the Users use only the 2002 version of Excel.


External Links:

Crosstab Headers

Freeze Columns