Over a million developers have joined DZone.

How To: Scrolling Crosstab with Fixed Row Headers

· Java Zone

Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code! Brought to you in partnership with ZeroTurnaround.

Originally Written by Michael Williams

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

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.
    clientSideScript
  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:
    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. You can see more blog posts from me, here. Also, feel free to follow me on Twitter or friend me on Facebook. If you have any questions or comments, either use the comments section below or visit the BIRT forums. The report used in this blog can be found in the BIRT Developer Center community devShare.

The Java Zone is brought to you in partnership with ZeroTurnaround. Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code!

Topics:

Published at DZone with permission of Michael Singer, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}