How To: Scrolling Crosstab with Fixed Row Headers

A few weeks ago, I saw a question in the forums about fixing the left column of a table while allowing the rest of the columns to scroll. In this blog, I’ll walk through the solution I proposed for the issue, with one difference: I’ll set this up on a crosstab. This seems like a more valid case because of the higher likelihood of not knowing how many columns you’ll end up with.

Initial Report Setup

For this example, I used the Classic Models Sample Database with the query:

In the data set, I also created a computed column that will be used later to help fix the row dimension column. The computed column is a concatenated listing of the values in the row dimension field. You can see how the computed column is set up, below:

Once you’ve created your data set, you’ll also create your data cube. For this example, my cube is set up as shown here:

Now that we have our data cube set up, we can lay out our design. First, we’ll add the containing grid.

1. Add a 1 column, 1 row grid to your layout.
2. Select the grid cell, go to the Property Editor’s General section, and set the overflow property to “Scroll.”

Next, create your crosstab inside the grid.

1. Use the date grouping for the column dimension.
2. Use the productline field as the row dimension (the dimension that we’ll fix into place).
3. Use the lineprice as our measure field.
4. Select the General tab in the Property Editor for the crosstab and select the “Hide Measure Header” check box.
5. Set the width of a cell in the productline column to 1.5 inches.
6. Set the width of a cell in the measure column to 0.85 inches.
7. Add your desired styling to the crosstab.
8. Double click on the month column dimension and edit it to look like the following:



9. With the month element still selected, go to the Property Editor and set the DateTime Format to Custom with a format code of “MMMM” (This will give us January, February, etc. instead of 1, 2, etc.).
10. Also set the Number Format for the measure element to Currency.

The resulting layout can be seen below:

If we run the report as is, we get a scrolling crosstab, but as we scroll to the right, you lose sight of what product line is associated with each row.

Fixing the Row Dimension Column

Fixing the product line column requires client-side scripting, so this solution will only work in HTML and the Web Viewer. To set this up, we need to start by setting up bookmarks on crosstab cells. These bookmarks become the elements’ IDs so we can access them with our client-side script.

1. Select the cell with the “Year” label in it, go to the Advanced section in the Property Editor, and set the Bookmark to “cella”.
2. For the “Month” label cell, do the same except set the Bookmark to “cellb”.
3. For the actual dimension cell, we’ll have to use a dynamic value. For this Bookmark, set it to data[“PRODUCTLINE”].

The last step will be to create an HTML text control that will contain our client-side script.

1. Drag a Text element from the Palette into your layout below your crosstab.
2. In the text element editor, select “HTML” from the drop down and enter the following HTML code and select ok.



3. In the binding tab of the Property Editor, bind the text element to the data set we created above.

The word “replaced” in the second row gets exactly that…replaced. This is where our computed column we created earlier comes in.

1. In the onCreate script of the text element, you’ll put the code:

That’s it. Now when we run our report and scroll to the right, we can see our product lines the entire way across:

Thanks for reading.