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…

OpenText profile picture

OpenText

December 30, 20144 minutes read

Descriptive text explaining the contents of the image.

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:

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:

computedColumn

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:

dataCube

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. month

  10. 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.).
  11. Also set the Number Format for the measure element to Currency.

The resulting layout can be seen below:

crosstab

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.

scrollingCrosstab

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. clientSideScript

  4. 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:

onCreateScript

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

scrollingCrosstabWithFixedDimensionColumn

Thanks for reading.

Share this post

Share this post to x. Share to linkedin. Mail to
OpenText avatar image

OpenText

OpenText, The Information Company, enables organizations to gain insight through market-leading information management solutions, powered by OpenText Cloud Editions.

See all posts

More from the author

Manutan combines digital services with the human touch to delight customers

Manutan combines digital services with the human touch to delight customers

At Manutan, we equip businesses and communities with the products and services they require to succeed. Headquartered in France, our company has three divisions, serving…

January 31, 2024 4 minutes read
Reaching new markets in Europe and beyond

Reaching new markets in Europe and beyond

How information management specialists at One Fox slashed time to market for innovative products with OpenText Cloud Platform Services At One Fox, we’ve driven some…

January 18, 2024 4 minutes read
SoluSoft helps government agencies tackle fraud faster

SoluSoft helps government agencies tackle fraud faster

Fraud, in all its forms, is a pervasive problem, spanning industries and preying on vulnerabilities in federal and state government systems. Each year in the…

November 21, 2023 3 minutes read

Stay in the loop!

Get our most popular content delivered monthly to your inbox.