locked
SSRS repeat table headers on each page in Excel export RRS feed

  • Question

  • User1508373147 posted

    I have created a report in SSRS 2017. I would like to have the table headers repeat when exported to Excel.

    I have already selected the Tablix Member properties -> Advanced Mode -> Static Row Group and set the properties to FixedDate:True, KeepWithGroup:After, RepeatOnNewPage:True. It works fine when I preview on the web browser and if I export to PDF. But when I print preview in Excel, it does not repeat the Table Headers on each page. I have looked endlessly for 2 days on the web and found no solution. I do not want to have to move my table headers to report headers to have them repeat. Any ideas what I need to do to make this format correctly in SSRS before the export?

    Tuesday, April 23, 2019 1:44 PM

All replies

  • User-1174608757 posted

    Hi katkngiht,

    According to your description,in SSRS, RepeatOnNewPage:True is  designed to show the headers on each page when you view it through Report Server or Report Manager.So ,when you export your report to an excel sheet you cannot set the headers to be visible in each page.

    To work around the issue, you could do as following:

    1.     Drag a list control to your report body.

    2.     Right-click the list control to select properties.

    3.     Click Edit details group…button to type in the expression =Ceiling(RowNumber(Nothing)/5)  5  is the number of the columns you want to display in a row, here you could modify it to the value you want.

    4.     Click OK.

    5.     Right-click the table control, and then select Properties, select the checkbox of Insert a pagebreak after this table.

    6.     Drag the table control into the list control.

    In report, you will get there is only five rows in one page, of course you can reset the value to decide how many rows should be displayed in each page. If you export the report into the excel, different pages will be displayed in different sheets. Every sheet has the table header.

    Best Regards

    Wei

    Wednesday, April 24, 2019 3:27 AM
  • User1508373147 posted

    This works but I do not want each page to print as a separate worksheet. I want one worksheet with with the functionality that if you were to export the report to Excel and print it, your headers would automatically repeat on each page printed. 

    Wednesday, April 24, 2019 9:41 PM
  • User-1174608757 posted

    Hi katknight32,

    As far as I know , if you use table headers, when exported to excel, it couldn't show header in each page by choosing any option.

    So if you don't want to add another list control.I suggest that you could use group header. It could repeat the header row on each excel sheet.

    Best Regards

    Wei

    Thursday, April 25, 2019 1:45 AM