Freeze Column Headers in excel after export from ssrs

Question

I have created a report and now I need to freeze tablix header. I tried all the ways related to freeze panes in SSRS, but after export to excel, the freeze pane is not working. Do you know any way to achieve this in SSRS 2016?

Below are the steps that I've tried:

1. Select the tabix and click on Tablix properties.
2. In the General tab under Column Headers section you can see "Keep header visible while scrolling" checkbox, check it.
3. Now the header row will be remain fixed in the report.

OR

1. In the grouping pane, make sure to turn on advanced mode (click on the small black down arrow on the far right of the grouping pane)
2. Select the corresponding (Static) item in the row group hierarchy
3. In the properties grid, set RepeatOnNewPage to true
4. KeepwithGroup to After

OR

1. Freeze the header of all columns[ Freezing table header ] : To do select static member of table header row from row groups [ Advanced Mode ] and set FixedData to true
2. Freeze the initial 2 columns : To do select static member of columns in column group and set fixedData to true.

Answer 1

There are a two different solutions that I know of to this problem. Each has its pros and cons.

Solution 1

You can follow this guide on mssqltips.com. This solution has you create a text box for each column and place it in the report header. It is a tedious and cumbersome way to get the job done but it works and it is all contained within the report.

Solution 2

The second way to accomplish this is with powershell. This solution only works if you can schedule the distribution of the report instead of having on demand access in the SSRS portal. You have powershell generate the report, modify the output, and distribute. Below is a sample powershell script.

#Set variables
$ReportServerUri = "http://MySsrsServer/ReportServer_MySsrsServer/ReportExecution2005.asmx?WSDL"
$ReportPath = "/MyReportPath"
$ReportOutputType = "EXCEL"
$ReportOutputDirectory = "C:\SsrsOutput\"
$ReportOutputFileName = "MyReport.xlsx"
$ReportOutput = $ReportOutputDirectory + $ReportOutputFileName

#Connect to web service
$ReportServer = New-WebServiceProxy -Class 'ReportServer' -Namespace 'ReportServer' -Uri $ReportServerUri -UseDefaultCredential
$ReportServer.Url = $ReportServerUri

#Load report
$Report = $ReportServer.GetType().GetMethod("LoadReport").Invoke($ReportServer, @($ReportPath, $null))

#Other variables to hold parameters and output values
$parameters = @()
$deviceInfo = "<DeviceInfo><NoHeader>True</NoHeader></DeviceInfo>"
$extension = ""
$mimeType = ""
$encoding = ""
$warnings = $null
$streamIDs = $null

#Render the report
$RenderOutput = $ReportServer.Render($ReportOutputType,
    $deviceInfo,
    [ref] $extension,
    [ref] $mimeType,
    [ref] $encoding,
    [ref] $warnings,
    [ref] $streamIDs
)

#Write file
$Stream = New-Object System.IO.FileStream($ReportOutput), Create, Write
$Stream.Write($RenderOutput, 0, $RenderOutput.Length)
$Stream.Close()

#Open Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$excel.DisplayAlerts = $False

#Open File
$workbook = $excel.Workbooks.Open($ReportOutput)

#Disable Split
$excel.ActiveWindow.Split = $false

#Freeze Panes
$excel.Rows.Item("10:10").Select() | Out-Null
$excel.ActiveWindow.FreezePanes = $true

#Save and Close Workbook
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$workbook.SaveAs($ReportOutput, $xlFixedFormat)
$workbook.Close($true)

#Close Excel
$excel.Quit()

#Send out email
Send-MailMessage -From "[email protected]" -To "[email protected]" -Subject "My Report" -SmtpServer "email.myorg.com" -Attachments $ReportOutput