We recently ran into a challenge on a project for a current client, and found this solution that we would like to share. We feel it could be useful to others experiencing a similar concern.
We needed to create a SSRS Report that would export a fixed text file format for uploading to a payroll vendor (In this case the client was using PayChex for their payroll). SSRS stands for SQL Server Reporting Services, and is a server-based report generating software system from Microsoft. It is part of the suite of Microsoft SQL Server services, and allows for creation of SQL reports, which are used heavily in development environments. SQL is the acronym for Structured Query Language, the language that allows you to communicate with a database.
Creating the SQL Output was complex, but doable. The concern was that SSRS didn’t give us a “Text” option in this case, it was only providing a CSV rendering and it always would output the column heading in CSV format as well. There are a variety of formats including Excel, PDF, CSV, XML, TIFF and HTML Web Archive in which reports can be created. In this case PayChex required the client to upload their files as fixed text, that means a file that has a specific format in which the data is organized in order to be accepted by their system. As you can surmise, CSV format was not going to match the layout they required.
We were able to add a “Custom Rendering” option to the rsreportserver.config file on the SSRS Server. This allows one to override default rendering for reports on the reporting server. Modifying the rendering extension parameters created a new export option called “Text File” and suppressed the header. Now we were able to export a fixed format text file directly to text without a header, which is what Paychex required for files uploaded by the client.
Here’s the syntax of what we added.
Custom rendering extensions are not simple, and there are alternatives to this solution. In this case in order to achieve the format required, as well as the specific layout required, custom rendering was the best path forward. In essence, the ability to render extensions to support different report presentation options is great for custom uploads and custom output needs. Often companies are required to output files in specific formats, or they will be rejected.