SSRS : Remove non required columns and give appropriate column name in CSV output

In case of CSV export, we always need to give some additional treatment to get the desire output in CSV. Most of the time, we face the issue with the unwanted column in CSV export.

Let’s take a sample report example with columns EmployeeCode, FirstName, LastName, Email and Phone

Here we will show Email and Phone in one column and will give an expression to column header that will be ="EMail "+VBCRLF+"Phone"

Now execute the report and export it into CSV format. I am getting this output in CSV format

In the output, we notice 2 issues.

1.       Unwanted column A in CSV.

2.       How to give an appropriate column name to column E. Currently it is showing Textbox5

To resolve the unwanted column in CSV output, select that cell/textbox in report design and set NoOutput in DataElementOutput in property window. In our scenario, we will set NoOutput to “Email Phone” column header.

To resolve our 2^nd issue, we have following two ways:

·         Select the data cell/textbox (Not the header cell/textbox) and give appropriate value to Name property or

·         Select the data cell/textbox (Not the header cell/textbox) and open the property window and give appropriate value to DataElelmentName property

In our scenario, we are giving “Email_Phone” as a value to DataElelmentName property of the “Email Phone” data cell/textbox.

Now let’s execute the report and export the output in CSV.

I am getting this output in CSV format that looks fine now.

To achieve required CSV output, we need to give attention to following points while designing the report:

1.      DataElementName Property: Use this property to get expected column name in CSV. CSV export format consider s data cell/textbox Name or DataElementName property as column name. Since it is compulsory to give Name to all cells/textboxes. But if you give value to DataElementName property then CSV format will consider DataElementName value instead of cell/textbox Name.

2.      DataElementOutput Property : this property has three values for selection.

Auto: - If SSRS consider a cell/textbox as a plain header cell/textbox then  that cell/textbox will not be exported in CSV format.

Output: - If it is compulsory for you to show a particular cell/textbox in CSV export then use this option.

NoOutput: - It is opposite to Output option.

Sample RDL can be downloaded from https://docs.google.com/open?id=0ByYnpeuIfYV0OUVVcERMSm1NRU0