Designing for CSV Output

The Comma-Separated Value (CSV) rendering extension renders reports in comma-delimited plain text files, without any formatting. It uses a string character delimiter to separate fields and rows, with the string character delimiter configurable to be a character other than a comma. The resulting file can be opened in a spreadsheet program like Microsoft Excel or used as an import format for other programs. The exported report becomes a .csv file and returns a MIME type of text/plain. The files are MIME version 1.0.

CSV Rendering

When rendered using the default settings, a CSV report has the following characteristics:

  • The first record contains headers for all the columns in the report.
  • All rows have the same number of columns.
  • The default field delimiter string is a comma (,).
  • The record delimiter string is the carriage return and line feed (<cr><lf>).
  • The text qualifier string is a quotation mark (").
  • If the text contains an embedded delimiter string or qualifier string, the text qualifier is placed around the text, and the embedded qualifier strings are doubled.
  • Formatting and layout are ignored.

To render a report, the CSV rendering extension iterates through the Report Object Model produced by the report processor. The following elements are ignored during processing:

  • PageHeader
  • PageFooter
  • Custom
  • Line
  • Image
  • ActiveXControl

The remaining report items are sorted, from top to bottom, then left to right. Each item is then rendered to a column. If the report has nested data items like lists or tables, the parent items are repeated in each record.

The following table lists the considerations applied to items when rendering to CSV.

Item Rendering behavior

Text box

Renders the contents of the text box. The data is formatted according to the item's Format property.

Table

Renders by expanding the table and creating a row and column for each row and column at the lowest level of detail. Subtotal rows and columns do not have column or row headings. Drillthrough reports are not supported.

Matrix

Renders by expanding the matrix and creating a row and column for each row and column at the lowest level of detail. Subtotal rows and columns do not have column or row headings.

List

Renders a record for each detail row or instance in the list.

Rectangle

Renders its contents as nested items. The parent item is repeated for each instance of the contents.

Subreport

Renders its contents as nested items. The parent item is repeated for each instance of the contents.

Chart

Renders a row for one set of the data values, and columns for the other set of data values.

The CSV rendering extension uses default values for several configurable device information settings, such as what character to use as the field delimiter, record delimiter, and qualifier. These device information settings can be accessed and changed through a custom application, and you can control the rendering of data, default delimiters, and page breaks through the use of the device information settings. The field delimiter, record delimiter, and text qualifier can be changed using the FieldDelimiter, RecordDelimiter, and Qualifier device information settings.

To control page breaks, the SuppressLineBreaks setting is a Boolean that specifies whether line breaks are removed when the data is rendered. When SuppressLineBreaks is true, line breaks and carriage returns are replaced by a single space. The Extension setting allows you to specify what file extension is used for the text file, while the Encoding setting lets you change the encoding from the default of Unicode to either ASCII, UTF-7, or UTF-8. Lastly, the NoHeader setting indicates whether the header row should be skipped when rendering. For more information on the device information settings, see Reporting Services Device Information Settings.

Note

The structure of the matrix data region in CSV may change in upcoming releases of Reporting Services.

See Also

Concepts

Design Considerations for Report Rendering

Other Resources

Reporting Services Programming

Help and Information

Getting SQL Server 2005 Assistance