none
SSRS CSV Rendering RRS feed

  • Question

  • We have a SSRS subscription which emails a report in CSV format to our client. When there is no data, client expects the file to just show the header:

    colume1,colume2,colume3,colume4

    However, the file is showing an extra line with comma, suggesting a data row with empty string fields:

    colume1,colume2,colume3,colume4

    ,,,

    This screws up client's file parsing. Is there any way to remove the extra line of ",,," in the end? We already made an adjustment in the configuration file to specify "ExcelMode" as False, because CSV rendering also adds an extra line feed by default when there is data.

    The version we have is 2008 R2.

    Wednesday, January 9, 2013 3:36 PM

All replies

  • Hello,

    When export report to CSV, the delimiter string to put in the result. By default, “FieldDelimiter” value is ",", so if there is no data, the report will display commas line below the header. This behavior is by design.
    To work around this issue, we can try to edit the FieldDelimiter of the CSV device settings, for example, change the commas with whitespace. Please change the config file as follows.

    <Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
    <Configuration>
                 <DeviceInfo>
                 <FieldDelimiter xml:space="preserve"> </FieldDelimiter>
                 </DeviceInfo>
    </Configuration>
    </Extension>

    Personally, I recommend you that submit a feedback at https://connect.microsoft.com/SQLServer/. Your feedback is valuable for us to improve our products and increase the level of service provided.

    For more information, please see:
    CSV Device Information Settings

    Reards,
    Fanny Liu

    If you have any feedback on our support, please click here .


    Fanny Liu
    TechNet Community Support

    Thursday, January 10, 2013 3:34 AM
    Moderator
  • Thanks. But this will not work. The configuration change will affect all our CSV subscriptions. In addition, client does want to have comma as delimiter when there is data. Any other work-around to remove the line containing just commas?
    Thursday, January 10, 2013 4:02 AM
  • Hello,

    see this link  might be helpfull to you

    http://www.bi-rootdata.com/2012/09/remove-non-required-columns-and-give.html



    blog:My Blog/

    Hope this will help you !!!
    Sanjeewan

    Thursday, January 10, 2013 4:32 AM
  • Is the report rendering a table? if so, can you build two tables the first one with the heading and the second table with the data. When no data is returned hide the second table-  this should fix the issue.

    Raul

    Friday, January 11, 2013 8:21 AM
  • Hi Raul,

    I am trying to involve someone more familiar with this for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here .


    Fanny Liu
    TechNet Community Support

    Wednesday, January 16, 2013 8:46 AM
    Moderator
  • Thanks, I will wait for a more permanent solution. Some work-around mentioned here may work for one report. But we have large number of reports and each has subscriptions in different rendering format based on clients' requirement. Therefore, trying workaround for each report / subscription is not realistic. 
    Wednesday, January 16, 2013 1:58 PM
  • Quick Suggestion. If you need to give a data dump in CSV why dont your try SSIS, I know it does not solve your problem immediately but that possibly will be more sustainable solution.

    Good Luck.

    Cheers


    Raul

    Thursday, January 17, 2013 12:13 AM
  • Hello,

    Unfortunately I am not aware of a straightforward solution to this. CSV is a data renderer and it flattens out the data into a text format. It ignores the settings in the Hide element.
    This is documented in these articles:
    Exporting to a CSV File
    http://msdn.microsoft.com/en-us/library/ms155919.aspx
          “Interactivity is not supported by either CSV formats generated by this renderer. The following interactive elements are not rendered:
              •Hyperlinks
              •Show or Hide
              •Document Map
              •Drillthrough or clickthrough links
              •End user sort
              •Fixes headers
              •Bookmarks”

    Following article discusses interactive features and what rendering formats are supported:
          Comparing Interactive Functionality for Different Report Rendering Extensions
          http://msdn.microsoft.com/en-us/library/dd255288.aspx


    I would also suggest that you open a https://connect.microsoft.com/SQLServer/ item for this, so change to this would be considered in future version of Reporting Services.


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thanks,
    Orsi
    Microsoft Online Community Support

    Friday, January 25, 2013 1:12 PM
    Answerer