SSRS 2008 CSV Design Problem Solved: Creating a Text File With Special Delimiters
One day I was given an interesting requirement – I needed to design a report that would render as a delimited text file. My first thought was to make it a report designed to be exported as a CSV, but I soon realized that the specs for this report required that the columns be separated by a “~” instead of a comma. My next thought was, this would be so much easier to do in SSIS (Sql Server Integration Services) where it fully and robustly supports the transfer of data in batch files, but that was out of the question. As the CSV format in SSRS delimits the data by comma, I needed to come up with a different method.
Here are the steps I took for my work-around:
1) Add a Tablix to the design surface. Remove the Header row. You might be asking, “Remove the Header Column? But doesn’t the file need column headers?”. Indeed it does. Usually when you create a CSV in SSRS, you can fill out the “Name” attribute of the textbox (found in TextBox Properties) in the data row (as opposed to the Name attribute of the actual header textbox), and SSRS will use that value as your header. This has limitations however – you cannot have spaces between words and your header cannot have special characters, but more importantly, using that method will cause you to rely on SSRS to do the delimiting, and as we know from above, that means the delimiter would be a comma.
2) This will leave you with only the detail row. Remove all columns except the first column. Now we’re left with a Tablix having 1 cell. Widen the Tablix to about page width or to what seems appropriate.
3) Right click on the cell and select “Expression”. This will open the window where you can set the value
4) In the expression window, begin by typing: IIF(RowNumber(Nothing) = 1. By using the IIF function, we can control what the expression writes based on the criteria we are trying to evaluate. By using RowNumber(), we can evaluate whether or not this line represents the first row in our dataset. By supplying Nothing as the scope, we are declaring that we want the top level data region to be our scope for aggregation.
5) Let’s say our file needs to be delimited by a “~” with column headings of “First Name”, “Last Name”, and “Address”. Our first row needs to be our header. When our expression above evaluates to true, that means we are returning the first row of our dataset. Therefore, we can write our True expression as follows:
“First Name~Last Name~Address” & vbcrlf & Cstr(Fields!FirstName.Value) & “~” & Cstr(Fields!LastName.Value) & “~” & Cstr(Fields!Address.Value)
Notice how we concatenated data fields to the header. This is because we are writing the header for the first row of the dataset. If we neglected to include our data in this expression, we would miss the first row of data. Not good! Also notice the use of vbcrlf. This will cause the data to be in a new line in the file.
When our RowNumber function evaluates to false, only concatenate the data in the delimited format, like so:
Cstr(Fields!FirstName.Value) & “~” & Cstr(Fields!LastName.Value) & “~” & Cstr(Fields!Address.Value).
Putting it all together, our final expression will look like this:
IIF(RowNumber(Nothing) = 1
, “First Name~Last Name~Address” & vbcrlf & Cstr(Fields!FirstName.Value) & “~” & Cstr(Fields!LastName.Value) & “~” & Cstr(Fields!Address.Value)
, Cstr(Fields!FirstName.Value) & “~” & Cstr(Fields!LastName.Value) & “~” & Cstr(Fields!Address.Value)
And that’s it. Your data will now appear in a delimited text format. You can apply this to any delimiter according to requirement or preference.
One noticeable potential flaw with this method is: What happens when my dataset has no rows? The answer to that is, the file is blank because there are no rows to process. You could get around this if necessary by having an additional row that has visibility set based on the row count of your dataset, and that would house the header in this case.
One final note:
Earlier in the article I mentioned briefly that SSRS will use the “Name” attribute (found in textbox properties) of the textbox in the detail row as the column header when exporting to a CSV file. Whether or not you deleted the header row of the tablix, the Out-of-the-Box CSV exporter will add this to your file as a header. For example, our tablix only had the one cell. If we were to export using SSRS’s built-in CSV exporter, the top of the file would include an undesirable line like the following:
(Our Actual Header)
In order to get around this, you would need to extend your export options by creating a custom format for text files and using that as your export format (out of scope for this article).
In this article, we’ve seen how we can use SSRS to create a delimited text file using custom delimiters. There are many different characters that can be applied using this method based on requirement or preference. While there may be better ways to achieve the same goal, I hope to have provided a worthwhile example of one method that can be implemented.