none
ssrs 2012 report export to excel and sort thje data on excel spreadsheet

    Question

  • In a new sql server report 2012 (ssrs 2012), I have a dashboard report where users export various pages to excel. The users want to be able to sort the data on their excel spreadsheets. However they are getting an error message about merged cells.

    To svolve the problem so far, I have changed some test ssrs 20012 reports where there are no report headers. The only type of headers the reports have are column headers. When there are only columns headers, I can export the reports to excel and sort all the data columns.

    What I have removed in the test ssrs 2012 reports is the report header that is the same width as the detail lines of the reports. In addition, I have removed the 'sub category' report headers that together make up the same width as the detail lines.

    The original report looked like the following:

                                Customer Purchase Order

            customer name: customer name1
            customer number: customer number1
           
            item       item               quantity       unit
            number     description         ordered        price
             1234         item_kind           2           $10


    Can you tell me if there is a way that I can keep any of the existing report headers? If so, can you tell me how I can keep the report headers?

    If I am not able to keep the report headers, can you tell me how the customer name, Customer number and name of the report can be exported to excel? The only thing I can think of is to make the additional information as detail line information that. Thus the detail line infomation would contain the following:
    report name, customer name, customer number, item number, item description, quantity ordered, unit price.

    Thus can you tell me if you have any ideas of how I can solve my problem of exporting the ssrs 2012 reports to excel and the users can sort the data?

    Sunday, November 10, 2013 3:08 AM

Answers

  • You displayed 2 tablixes that were right next to each other. However can you have 2 tablixes that were 1 tablix is under the second tablix? The user could scroll down the page to see the second tablix.  Could there even be a blank line or two between the 2 tablixes?

    Hi Wendy,

    If you want to display multiple tablixes vertically, then the columns of the tablixes must be aligned vertically strictly. There will only one blank line between two tablixes.

    If we want to sort the data on one tablix, then we can select the data and click Sort sign on Editing tab.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, November 12, 2013 2:58 AM
    Moderator

All replies

  • Hi ,

    Similar discussion - http://social.msdn.microsoft.com/Forums/en-US/3149aaa7-d7e2-489d-bf60-fcace0cba1a6/ssrs-2008-r2-export-to-excel-2013?forum=sqlreportingservices

    Try this link .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, November 10, 2013 3:27 AM
    Moderator
  • The reply is similar. However this does not answer the question about what to do with the header information.
    Sunday, November 10, 2013 4:49 AM
  • I have tried to export a report from sql server reporting services (SSRS 2008) to excel so the report looks like the following:

                                Customer Purchase Order

            customer name: customer name1
             customer number: customer number1
            
             item       item               quantity       unit
             number     description         ordered        price
              1234         item_kind           2           $10


     The users want to be able to sort the data on their excel spreadsheets. However they are getting an error message about merged cells.

    To solve the problem so far, I have changed some test ssrs 20012 reports where there are no report headers. The only type of headers the reports have are column headers. When there are only columns headers, I can export the reports to excel and sort all the data columns.

    What I have removed in the test ssrs 2012 reports is the report header that is the same width as the detail lines of the reports. In addition, I have removed the 'sub category' report headers that together make up the same width as the detail lines.


     Can you tell me if there is a way that I can keep any of the existing report headers? If so, can you tell me how I can keep the report headers?

    If I am not able to keep the report headers, can you tell me how the customer name, Customer number and name of the report can be exported to excel? The only thing I can think of is to make the additional information as detail line information that. Thus the detail line infomation would contain the following:
     report name, customer name, customer number, item number, item description, quantity ordered, unit price.

    Thus can you tell me if you have any ideas of how I can solve my problem of exporting the ssrs 2012 reports to excel and the users can sort the data?

    Sunday, November 10, 2013 4:54 AM
  • Hi,

    As your description,your issue is more related for SSRS 2012, I'll move your question to the SQL Server Reporting Services & Powerview forum

    http://social.technet.microsoft.com/Forums/en-us/home?forum=sqlreportingservices&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support

    Monday, November 11, 2013 2:43 AM
  • Hi Wendy,

    This issue is caused by Merging Cells when exporting to Microsoft EXCEL. I have tested it on my local environment, if the merge cells were avoid, we can sort the column without any problem. To avoid the merged cells, the rows of the two tablixes must be aligned horizontally strictly, e.g. a row in the first tablix and the corresponding row in the second tablix must be in the same Excel row perfectly. The screenshots below are for your reference.

    For the detail information about it, please refer to the link below to see Merging Cell section.
    Exporting to Microsoft Excel (Report Builder and SSRS)

    Regards,
    Charlie Liao

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


    Charlie Liao
    TechNet Community Support


    Monday, November 11, 2013 7:59 AM
    Moderator
  • You displayed 2 tablixes that were right next to each other. However can you have 2 tablixes that were 1 tablix is under the second tablix? The user could scroll down the page to see the second tablix.  Could there even be a blank line or two between the 2 tablixes?

    If what I asked above is possible, can you tell me how to accomplish my goal?

    Monday, November 11, 2013 3:27 PM
  • Absolutely right Liao!!

    I have experienced the same issue and I killed half a day for finding the issue. It is with the alignment of Tablix, Headers, Footer or any other text boxes, they must be aligned horizontally.

    Monday, November 11, 2013 4:30 PM
  • You displayed 2 tablixes that were right next to each other. However can you have 2 tablixes that were 1 tablix is under the second tablix? The user could scroll down the page to see the second tablix.  Could there even be a blank line or two between the 2 tablixes?

    Hi Wendy,

    If you want to display multiple tablixes vertically, then the columns of the tablixes must be aligned vertically strictly. There will only one blank line between two tablixes.

    If we want to sort the data on one tablix, then we can select the data and click Sort sign on Editing tab.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, November 12, 2013 2:58 AM
    Moderator