Filters:

  • Technologies

  • Instructors

  • Repeating and Freezing Column Headers in SSRS Tables

    In this article I’ll show you how to configure a table tablix to both repeat column headers at the top of every page and freeze them while scrolling. While tablixes have properties for these things, they only work in matrix tablixes but not in tables. I’ll show you a way to accomplish these things using an approach that does work; however, be warned: the way to do this is clunky, counter-intuitive, and requires maintenance if you make certain changes to your tables.

    First, let me show you the properties that don’t work in a table. For the approach I’ll be showing you here to work properly, those properties have to be set to False.

    Press F4 to display the Properties Window. Then, in the dropdown at the top of the Properties window, select the tablix in your report for which you want to control column headers.

    001-Repeating-and-Freezing-Column-Headers-in-SSRS-Tables

    There are two properties you’re looking for. FixedColumnHeaders will prevent column headers in a matrix from scrolling off the top of the page and RepeatColumnHeaders will make sure that column headers in a matrix appear at the top of every page in the tablix. When working with tables, you want to make sure these properties are both set to False.

    002-FixedColumnHeaders-Column-Headers-in-SSRS-Tables

    With both of these properties set to False, we’re ready to configure the tablix to repeat and freeze the column headers. Start by putting the group panel at the bottom of the report design screen into advanced mode. Do this by selecting Advanced Mode from the dropdown off to the right side of the groups panel.

    You may also like:  A Real-World Example of a Non-Correlated SELECT Subquery and Cross Join

    003-FixedColumnHeaders-Column-Headers-in-SSRS-Tables

    Don’t be alarmed by what you see next! This is a notoriously confusing layout that many people struggle to understand. The good news is that you don’t need to understand it in order to use it for what we’re doing as long as you keep in mind that you always work with the first static item in the list. Select the first Static item and press F4 to display its properties in the Properties window.

    004-Properties-Repeating-and-Freezing-Column-Headers-in-SSRS-Tables

    You’ll need to set two properties to make the column headers repeat on every page: KeepWithGroup must be set to After and RepeatOnNewPage must be set to True. To prevent scrolling, set FixedData to True.

    (A note of caution: Keep in mind that this approach works as long as these properties are always set on the first static item. If you change the table in a way that changes what the first row was when you set these properties, what used to be the first static item will no longer be the first one. You will have to find where that static item ended up, undo the property changes on it, and reset those properties on the new first static item.)

    You may also like:  Optional Parameters in SSRS

    Alas, we are not quite out of the woods, yet. After you set the FixedData property to True, preview your report and try scrolling. Notice that the column header stays put, but because its background is transparent by default, things look messy.

    005-fixedData-Properties-Repeating-and-Freezing-Column-Headers-in-SSRS-Tables

    To fix this, just change the background color of the cells in the header row to something other than “No Color.” Select the top row of the table, press F4 to show the Properties window, click the dropdown of the BackgroundColor property, and chose a color, such as White or any other color (if you choose a dark color for the background, it may be difficult to read the column headers, and you may have to change the Color property, which controls the foreground color, to something light).

    006-BackgroundColor-Properties-Repeating-and-Freezing-Column-Headers-in-SSRS-Tables

    Enjoy!

    Peter Avila
    SQL Server Instructor – Interface Technical Training
    Phoenix, AZ

    See what people are saying...

    1. David

      Correction. It works in Visual Studio Preview but not in the production environment. What is with that?

    2. David

      It worked when the other articles didn’t.

    3. Sherry Dunn

      I followed the steps above and got two error messages,
      1. The tablix ‘Tablix1’ has an invalid TablixMember. All TablixMember elements in a TablixColumnHierarchy must have the RepeatOnNewPage property set to false.\
      2. The tablix ‘Tablix1’ has an invalid TablixMember. All TablixMember elements in a TablixColumnHierarchy must have the KeepWithGroup property set to “None”.

      This is for a table. Thanks.

    4. Nirmal Lawrence

      Hello can i get a sample code for this scrolling functionality. i am struggling to freeze the header column while scrolling. Please do the needful.

    5. Mitchell Small

      Great Article! Thanks

    6. Raj

      Very helpful. thnx.

    Share your thoughts...

    Please fill out the comment form below to post a reply.