locked
SSRS: Column name changing when exporting report to csv RRS feed

  • Question

  • Dear All,

    I have a report with all all column names starting with numbers.

    For ex: 00130Field1, 00140Field2, 00150Field3 etc

    It is appearing in csv as ID00130Field1, ID00140Field2, ID00150Field3

    I need this leading characters to be removed while exporting to csv.

    Please Suggest.

    Thank You

    Julie

    Wednesday, January 17, 2018 6:03 PM

Answers

  • Hi All,

    There is a workaround for this issue.

    We need to generate the report from SSIS package. We can add a script task and call SSRS report inside that. Then the report will be copied in a shared path which you provide. Then, you will have to add a data flow task which source and destination must be flat file. source flat file calls the recently generated csv formatted report from the shared path and moving to the destination flat file. We need to keep a template for the destination csv file in the destination path. There you will have to remove all the unwanted characters from the headers. And this will be applied in the mapping as well. This is going to be a one time activity then later you can just run the apckage and then it will produce the required output file.

    Thanks 

    Julie

    Tuesday, January 23, 2018 4:08 PM

All replies

  • Hello Julie,

    The property "DataElementName" should work for you, see Rendering Data (Report Builder and SSRS) => "The DataElementName property controls the name of the data element. In CSV, this controls the name of the CSV column header"


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, January 17, 2018 6:30 PM
  • Hello Julie,

    The property "DataElementName" should work for you, see Rendering Data (Report Builder and SSRS) => "The DataElementName property controls the name of the data element. In CSV, this controls the name of the CSV column header"


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi Olaf,

    I tried to set the data element property name to 00130Field1, but it is showing an error that "Property value is not valid". Can you please suggest if I need to make any other change?

    Thank Yu

    Julie

    Wednesday, January 17, 2018 9:27 PM
  • Hi Julie,

    I meet the same issue as yours. It is because by design, the SSRS column name cannot start with a number. 

    So it is required to manually remove the ID in the csv file. 

    Personally, I recommend you that submit this suggestion at https://connect.microsoft.com/SQLServer/ . If the suggestion mentioned by customers for many times, the product team may consider to improve it in the later SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    BR,

    Henry 

    • Proposed as answer by Visakh16MVP Thursday, January 18, 2018 5:47 AM
    Thursday, January 18, 2018 5:32 AM
  • Hi Julie,

    I meet the same issue as yours. It is because by design, the SSRS column name cannot start with a number. 

    So it is required to manually remove the ID in the csv file. 

    Personally, I recommend you that submit this suggestion at https://connect.microsoft.com/SQLServer/ . If the suggestion mentioned by customers for many times, the product team may consider to improve it in the later SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.

    BR,

    Henry 

    Hello Henry,

    Thank You for your response!

    Tuesday, January 23, 2018 4:02 PM
  • Hi All,

    There is a workaround for this issue.

    We need to generate the report from SSIS package. We can add a script task and call SSRS report inside that. Then the report will be copied in a shared path which you provide. Then, you will have to add a data flow task which source and destination must be flat file. source flat file calls the recently generated csv formatted report from the shared path and moving to the destination flat file. We need to keep a template for the destination csv file in the destination path. There you will have to remove all the unwanted characters from the headers. And this will be applied in the mapping as well. This is going to be a one time activity then later you can just run the apckage and then it will produce the required output file.

    Thanks 

    Julie

    Tuesday, January 23, 2018 4:08 PM
  • Hi All,

    There is a workaround for this issue.

    We need to generate the report from SSIS package. We can add a script task and call SSRS report inside that. Then the report will be copied in a shared path which you provide. Then, you will have to add a data flow task which source and destination must be flat file. source flat file calls the recently generated csv formatted report from the shared path and moving to the destination flat file. We need to keep a template for the destination csv file in the destination path. There you will have to remove all the unwanted characters from the headers. And this will be applied in the mapping as well. This is going to be a one time activity then later you can just run the apckage and then it will produce the required output file.

    Thanks 

    Julie

    This is not a workaround but its a different approach altogether :)

    This is standard procedure you follow in ETL process and you can have any columnnames if you want for the csv . 

    You can even generate it on the fly using script task or using methods like bcp based on your table columns

    Since your question was based on SSRS and is posted on SSRS forums, nobody suggested this 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 23, 2018 4:34 PM