www.BI Bits.co      

      A blog for discussing Business Intelligence and issues related to the Microsoft BI stack.


SQL Server 2008 SSRS Repeat Column Headings on New Page

In case you haven't noticed, there is a problem in SQL Server Reporting Services (SSRS) when you try to have tablix column headings repeat on a new page. For example, I have a report that uses a tablix.

tablix

I have a group that is based on GroupType.

grouping pane

group properties

group properties

When I run the report, Page 1 displays just like what I want. I have my data and all my column headings.

page break

But when I go to Page 2, there are no column headings.

I go back to the tablix properties and I check the box for Repeat header rows on each page. But there is no change in the report display.

report display

So how do I get my column headings to appear at the top of each page? I could place them on the page header, but then I have to add one textbox for each column and make sure it is properly aligned with the columns in the tablix. I prefer adding the column headings in the tablix. But they are not being displayed when I have a new page.

The solution? It’s kind of a trick. The RepeatColumnHeaders property works if there is a column headings area in the report. Unfortunately, the default tablix (and this report) does not contain a Column Header area so setting the property appears not to work. You can identify a Column Heading area if you see the double dotted lines in the tablix. A work around is to add a column group to the tablix. Then delete the group in the grouping pane but choose to leave the associated rows and columns. Now you can move your column header labels to the new cells above the double dashed lines and it should work. That’s one way. I am going to do it slightly different because I do not want to eliminate the column header row I already set up and recreate the column headers on a different row.

So what I need to do is first add a column group. Select one of the columns, right click on it, and a drop down menu appears. If I hover over the Add Group, I will see an option to add a column group parent group. A pop up box is displayed and I can select any of the columns as a column group. In this instance I am going to select GroupType.

tablix properties

We now see the column group for GroupType added to the tablix. As I mentioned before, you can identify a Column Heading area if you see the double dotted lines in the tablix.

tablix group

Next look at the grouping pane at the bottom of the report designer. Now we see an entry under Column Groups.

tablix group design

Turn on Advanced mode (click on the small black down arrow at the far right end of the grouping pane).

I will now see some new elements added and they are named (Static). I see them in both the Row Groups area and the Column Groups area.

grouping pane

Now I need to delete the group, but leave the related rows and columns.

delete group

After the delete, my grouping pane looks like this.

grouping pane

If I highlight the (Static) element under the (GroupType) element, right click it and look at the properties, I will see a property for RepeatOnNewPage. Change that to True.

tablix properties

I then highlight the row that was inserted when I added a column group and delete it.

row deletion

Now my report looks similar to when I first started.

tablix display

If you look at the grouping pane after all these processes though, the (static) elements will still be there.

grouping pane

And now when I run the report, I will see column headings on each page.

Once I close the project and reopen it, the (Static) elements will no longer be included with the report, but the column headings will still appear on each new page.

Obviously, this is not an intuitive process. And it sure takes a lot of work. In some instances, it is easier to place the column headings in the page header area.

Comments

6/29/2011 8:12:53 PM #

hoog in google

I was looking for some specific information about SQL Server in this post. I like the details and screenshots that you have shared. Thanks for sharing and keep up the good work.

hoog in google United States

1/9/2013 4:49:54 AM #

trackback

SSRS and Column Headers

SSRS and Column Headers

ObjectSharp Blog

3/5/2014 1:19:22 PM #

pingback

Pingback from search-r.biz

Repeating tablix header on each page, but want to change heading on repeated rows second page | Search RounD

search-r.biz

11/22/2014 1:17:15 AM #

pingback

Pingback from ecanswers.org

Repeating tablix header on any page, though wish to change streamer on steady rows second page | Zhong

ecanswers.org

Comments are closed