Feb 19, 2012

Keep the column/row headers visible on scroll through a report in SSRS 2008

SQL Server Reporting Services 2008 has a bug concerning the FixedColumnHeaders property of a tablix.
The property's description is given as :
"Indicates whether the column headers remain displayed on the page when the user scrolls the tablix data region off the page."
Well that's not actually true because setting that property to, well, anything has no discernible effect. Microsoft have acknowledge the bug (without actually calling it a bug) and provided a workaround in a knowledge base article:

This problem occurs because the properties to enable the fixed headers feature have changed from SQL Server 2005 to SQL Server 2008. The structure of tables and matrices is replaced by a Tablix.

To work around this issue, use one of the following two methods:
Method 1: Keep the original structure of the current data region
  1. On the Report menu, click Grouping.
  2. In the Grouping pane, click the selector arrow in the upper-right corner, and then click Advanced.
  3. Select a Tablix member in the Row Groups or Column Groups pane that corresponds to a row or a column that you want to fix.
  4. In the Properties dialog box, set the FixedData property to True.
Note You can set the FixedData property to True only if there is no corresponding Tablix row header area or column header area in the data region.
Method 2: Add a row group or a column group to the Tablix
  1. To obtain the row or column grouping area in the Tablix, add a row group or a column group.
  2. After you add a row group or a column group, you can change the content in those areas as necessary. This reflects the content that you want to fix when you scroll.
Note When you create new tables in SQL Server 2008, add a row grouping area or a column grouping area to the table by adding row or column groups to the data region. Then, replace the content.

In a Tablix, there are explicit row header and column header areas of the data region that are defined by a double-dashed line. This line is visible on the design surface.

Tables that you upgrade from SQL Server 2005 do not generate a Tablix row header area or column header area. After you upgrade from SQL Server 2005, you may click to select the Header should remain visible while scrolling check box in the Tablix dialog box. This step does not generate fixed column headers or fixed row headers.

Assume that you define fixed headers in a table or in a matrix in SQL Server 2005, and then you upgrade to SQL Server 2008. The fixed header settings are translated to the new FixedData setting. This feature works correctly. If you do not enable this feature in SQL Server 2005, and you try to set it or change it by setting theFixedHeader property in SQL Server 2008, this feature does not work. 

When you create new tables in SQL Server 2008, the default table placeholder does not have a Tablix row header or column header area. Therefore, setting the FixedHeader property does not perform as usual.

Microsoft intends to improve the user interfaces for this feature in the next version of SQL Server Report Builder and Report Designer.

No comments:

Post a Comment


Thanks for your visit to this blog.
We would be happy with your Queries/Suggestions.