Configuring Custom Render Formats for SQL Server Reporting Services in SharePoint-Integrated Mode

Configuring Custom Render Formats for SQL Server Reporting Services in SharePoint-Integrated Mode

SQL Server Reporting Services (SSRS) is a very versatile tool, allowing report users to export reports in many standard render formats, including Excel, PDF, CSV, and many others. However, there are many reasons why a user may need to export a report in a non-standard format. For example, a user may need to export data from an SSRS report in to a legacy application that requires a very specific format. While manual manipulation could likely get the report in to the required format, creating a custom SSRS format to export the report would be a more reliable long-term solution.

The scenario had the following export requirements for a standard tabular-format report, displaying financial data:

  1. Export must be tab-delimited
  2. File extension must be “.CSV”
  3. Column headings must be presented in Title Case, not in Pascal Case (i.e. “First Name” and “Last Name”, not “FirstName” and “LastName”)

Analyzing this scenario, our team found that the many existing resources provided instructions that applied only for SSRS running in Native mode. Because the client had SSRS 2008 R2 running in Integrated Mode with SharePoint 2013, none of the resources found led directly to the solution. To accomplish this, two changes were required, outlined further below:

  • Modifying SSRS Server Configuration to allow additional render formats
  • Modifying SSRS Reports to allow for additional table configurations

Though this solution was tested in an environment running SSRS for SQL Server 2008 R2 with SharePoint 2013, it is expected to work with SSRS for SQL Server 2012, SQL Server 2014, or SQL Server 2016, and may work with other versions of SharePoint.


Modifying SSRS Server Configuration to allow additional render formats

As mentioned, many existing online resources provide instructions for modifying the RSReportServer Config file to allow for additional render formats, including this MSDN Article. However, when running SSRS in Integrated Mode, the application server must be modified to change or add additional render format options. To change native reporting services render formats, follow the instructions here. To add a new render format, follow the following 4 step process:

  1. Get a reference, in this case a GUID, to the Reporting Services Service Application using the Get-SPRSServiceApplication This command provides a listing of SharePoint Reporting Service Applications, and in typical cases will only return one item. Note the ID value that is returned; for example, the ID returned in the scenario was “aaa65303-374f-4857-900e-72c5a9b9436a”.

  2. Provide the render format by creating XML that describes the format. More information on the format can be found here. Based on the business requirements, the business scenario outlined above was translated to the following XML.

    <OverrideNames><Name Language=’en-US’>TAB Delimited</Name></OverrideNames>
    <DeviceInfo>
    <Encoding>ASCII</Encoding>
    <FieldDelimiter xml:space=’preserve’>`t</FieldDelimiter>
    <UseFormattedValues>False</UseFormattedValues>
    <NoHeader>True</NoHeader>
    <FileExtension>csv</FileExtension>
    </DeviceInfo>

    The first XML block will be provided to our PowerShell command to satisfy the ServerDirectives argument, and the second XML block will satisfy the ExtensionConfiguration argument.  Note that the field delimiter for a space in PowerShell is “`t”.  “`t” is the escape character for the tab key in PowerShell, whereas the tab delimiter in Native mode this would be ” “.

  3. New-SSRSExtension on the application server passing it the identity found in Step 1 and the XML built in Step 2.

    New-SPRSExtension -identity aaa65303-374f-4857-900e-72c5a9b9436a -ExtensionType “Render” -name “TAB” -TypeName “Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering” -ServerDirectives “<OverrideNames><Name Language=’en-US’>TAB Delimited</Name></OverrideNames>” -ExtensionConfiguration “<DeviceInfo><Encoding>ASCII</Encoding><FieldDelimiter xml:space=’preserve’>`t</FieldDelimiter><UseFormattedValues>False</UseFormattedValues><NoHeader>True</NoHeader><FileExtension>csv</FileExtension></DeviceInfo>”

  4. Perform an iisreset on the application server to apply changes.

Now that the new export format has been configured, the following section will provide instruction for leveraging the new format within reports.


Modifying SSRS Reports to allow for additional table configurations

Server configurations outlined above allowed the first two business requirements to be met. However, the last requirements cannot be set by render formatting and must be achieved by report and query configuration. When SSRS exports to a .CSV format, any table formatting is lost, a design of the export format. With .CSV format, the text box labels are displayed in alternative of the standard header configured for the report. The text box labels will match the field label by default and can be changed, but these labels do not allow the space character. Because the business requirement stated that the column headers required spacing, an alternative approach is required.

SSRSConfig1SSRSTable2

As mentioned above, this approach first requires that the render format is configured to not display headers, configured by the render setting of NoHeader set to True. Instead, to display the headers in the format required for the business, a UNION query can be used.

  1. Add an additional SELECT statement with all headers and any aliases desired. This will be the first row of the query, and will now display the column headers in the format desired.
  2. UNION ALL this query to the original query. Note that it may be required to cast values in the original query as a different data type in order for the UNION to function correctly. For example, the scenario below requires casting Bit and Money fields using the STR() function to allow the column heading to display.
  3. Add an additional field to both queries to use for sorting and ensure that the column heading row displays first. In this example, the field added was populated with “1” for the column heading section of the query and populated with “2” for the original section of the query, and then ordered by this field, aliased as Sort. Executing this query and exporting using the configured render format will meet requirement #3.

Example:

SELECT
1 Sort
,’Customer First Name’ CustomerFirstName
,’Customer Last Name’ CustomerLastName
,’Account Number’ AccountNumber
,’Online Order Flag’ OnlineOrderFlag
,’Order Date’ OrderDate
,’Ship Date’ ShipDate
,’Order Total Due’ TotalDue

UNION ALL

SELECT
2 Sort
,C.FirstName
,C.LastName
,OH.AccountNumber
,STR(OH.OnlineOrderFlag,1,0)
,FORMAT(OH.OrderDate, ‘yyyy-MM-dd’)
,FORMAT(OH.ShipDate, ‘yyyy-MM-dd’)
,STR(ROUND(OH.TotalDue,2),25,2)

FROM Sales.SalesOrderHeader OH
JOIN Person.Person C ON C.BusinessEntityID = OH.CustomerID

ORDER BY Sort

SSRSTable1


Summary

In summary, options exist to allow SSRS to render in additional formats when running in SharePoint Integrated Mode and allow for additional table configuration beyond standard options. These additional options include:

  • Adding an additional render format by executing PowerShell by modifying the SSRS Application Server.
  • Using a UNION statement instead of traditional column headers allows for formatting flexibility

Your email address will not be published. Required fields are marked *

Phone: 312-602-4000
Email: marketing@westmonroepartners.com
222 W. Adams
Chicago, IL 60606
Show Buttons
Share On Facebook
Share On Twitter
Share on LinkedIn
Hide Buttons