none
Excel rendering not correctly for SQL SERVER 2012 (and reporting server)

    Question

  • hi

    I have a tablix that has a category, subcategory and total items column. The total items are grouped into subcategories which are in turn grouped into categories. When using the wizard we explicitly indicate that the subtotals and totals should NOT be displayed.

    When displayed in SSRS on IE and we expand/collapse all the categories this works fine, however when we export these to an excel file using the export button we notice that the subcategories total is displayed with an empty cell when we toggle all the subcategories under one category.

    It seems that it used to be a SSRS2008 r2 problem but is that still the case for 2012 and what can I do to fix this?

    help would be much appreciated.

    thanks

    eddy


    eddy.a

    Wednesday, February 05, 2014 12:10 AM

Answers

  • Hi eddy,

    Based on my test, I can reproduce the same issue in SSRS 2012. When exporting to Microsoft EXCEL, excel will add an extra row for each group, if we use the “=Sun(fields!column.value)” in the details row, then the extra row will display the total of this group, if we use the fields!column.value in the details row, then the extra row will display the first value of this group.

    Please refer to the work around to fix the issue: add an extra row to the bottom of the tablix, outside of the group below. Then, set the border style with “None” value of these text box.
    Reference: https://connect.microsoft.com/SQLServer/feedback/details/508823/reporting-services-2008-group-by-export-to-excel-duplicate-rows-csv-ok-pdf-ok

    If the issue is persist, 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.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Eddy.a Thursday, February 06, 2014 2:02 PM
    Thursday, February 06, 2014 4:23 AM
    Moderator

All replies

  • Hi eddy,

    Based on my test, I can reproduce the same issue in SSRS 2012. When exporting to Microsoft EXCEL, excel will add an extra row for each group, if we use the “=Sun(fields!column.value)” in the details row, then the extra row will display the total of this group, if we use the fields!column.value in the details row, then the extra row will display the first value of this group.

    Please refer to the work around to fix the issue: add an extra row to the bottom of the tablix, outside of the group below. Then, set the border style with “None” value of these text box.
    Reference: https://connect.microsoft.com/SQLServer/feedback/details/508823/reporting-services-2008-group-by-export-to-excel-duplicate-rows-csv-ok-pdf-ok

    If the issue is persist, 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.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Eddy.a Thursday, February 06, 2014 2:02 PM
    Thursday, February 06, 2014 4:23 AM
    Moderator
  • Hi Alisa

    This absolutely did the trick (even though doesn't make much sense unless i am missing something) thank you very much


    eddy.a

    Thursday, February 06, 2014 2:02 PM
  • ok so the trick work to hide the subtotals

    but when i do so the aggregated totals also are hidden when the report is initialized and when it's sent over to excel.

    so back to our example -

    before adding a row ; when the report is rendered you will have the categories and the subcategories aggregated to it with the total items belonging to it displayed in the sum column

    after adding the row ; when the report is rendered the totals are hidden however when you expand the subtotals are not showing as expected.


    eddy.a

    Thursday, February 06, 2014 2:25 PM