This month’s tip comes from Jade Hildahl, Business Intelligence Consultant with Superior:
In Microsoft SQL Server Reporting Services 2008, the Tablix data region can be unwieldy when you’re wanting to turn on the feature where “keep headers visible while scrolling” is being used. This causes table headers to remain in place while the user scrolls down through rows, or right through columns on the report.
What if there aren’t any group headers, where table grouping gives you an option to add a header or a footer row?
As a result even if you select the option to keep headers visible from the Tablix properties dialog, the “headers” don’t remain visible because they’re not defined as a group headers. To get around this, fixed rows in a table can be treated as a header or other non-scrolling area by
- Go to the advanced options for the column groupings in the Tablix(the quite-hard-to-locate drop-down in the upper –right corner of the Tablix groups area), and then then select the “Static” row grouping that is associated with the text boxes in the Tablix that you want to treat as “header” row(s).
- Next, select the “Static” row group for the header row and from its “Properties” pane, toggle “FixedData” to be “True” for the entire row.
(This may not apply to every situation where one wishes to do this, but at this point, one may also want to make sure the option to “Keep Column Headers Visible While Scrolling” is NOT enabled from the Tablix Properties window otherwise you will get a row/column conflict error when trying to build the report)
At this point, after deploying, you’ll have scrolling detail rows with fixed headers. However, the detail row data will still be visible in the background behind the header rows, because the default setting for new text boxes in a Tablix is to have no background color. To get around this,
- Selected all the text boxes making up header rows, and set their background to a non-transparent color, such as “White”.
After deploying, the rows to be treated as “header rows” are fixed and opaque as desired, without having defined a parent group or “header” row, or for that matter, any groupings of any kind in the Tablix beyond my standard “detail” row.
1. Select a text box in the row you wish to have not scroll
2. Click on the “Advanced Mode” drop-down arrow to the right of your Column Groupings
3. Click on the now visible “Static” row under your “Row Groups”
4. Ensure the first text box in your desired, non-scrolling row is automatically highlighted at this point
5. In the properties pane for the Static Row Group, toggle the “FixedData” option to “True”
6. (Optional: in the Tablix Properties dialog, make sure that you have not also selected the “Column Headers” option to “Keep header visible while scrolling” or it will conflict with the new configuration to keep your Row Headers visible while scrolling)
7. Select all your header row text boxes, and in their shared properties dialog, ensure that their background color is anything aesthetically pleasing other than “transparent”
Hopefully these tips help you in your development. If you have further questions, or are in need of assistance, please contact us directly and we would be happy to help!