Friday, April 10, 2015 #

SSRS–adding a pipe delimited text export option in Sharepoint Integrated mode

We are currently looking decommission an old reporting portal based on another technology stack and replace it with SSRS running in Sharepoint integrated mode. However one of the requirements was to support a “pipe delimited” CSV export format.

There are a number of posts you can find about changing reportserver.config when running SSRS in standalone mode, but I could not find much information about how to do this when running in Sharepoint Integrated mode.

MSDN documentation lists the various cmdlets, https://msdn.microsoft.com/en-us/library/gg492249.aspx but does not show examples of the parameter values which is pretty useless as some of the parameters require XML fragments.

This blog got me close http://blog.kuppens-switsers.net/it/customize-ssrs-extension-settings-sharepoint-integrated-mode/ and when combined with the information from this one http://www.mssqltips.com/sqlservertip/3379/modify-sql-server-reporting-services-rendering-format-in-sharepointintegrated-mode/ I was able to come up with the following powershell script which does what I needed.

You’ll need to run this from the Sharepoint Management Shell (I logged on to the console of one of our app servers to do this) using an account that has farm admin rights.

$svrDirectives = @"
<OverrideNames>
    <Name Language='en-US'>CSV (Pipe Delimited)</Name>
</OverrideNames>
"@
$extConfig = @“
<DeviceInfo>
    <FieldDelimiter>|</FieldDelimiter>
    <NoHeader>false</NoHeader>
    <FileExtension>csv</FileExtension>
    <ExcelMode>False</ExcelMode>
    <Encoding>ASCII</Encoding>
</DeviceInfo>
"@
$typeName = "Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering"

$apps = get-sprsserviceapplication
foreach ($app in $apps)
{
  New-SPRSExtension -identity $app -ExtensionType "Render" -name "CSVPipeDelimited" -TypeName $typeName –ServerDirectives $svrDirectives –ExtensionConfiguration $extConfig
}

This code loops over all the service applications in the current farm so even if you have a multi tenancy type situation it *should* work. Although if you are in this situation you should do your own testing. Our farm only has the one SSRS service application.

Posted On Friday, April 10, 2015 8:37 PM | Comments (2)