none
Exporting Summary Totals as formulas to Excel RRS feed

  • Question

  • I'm running the most current versions of SSRS and Excel.

    I have an SSRS report with 10 columns and three group summary levels.  I desperately need those summary rows exported as formulas to Excel as real formulas.  

    I need to know how I create some kind of text formula or something in the summary cells that will export as a formula.

    I know the column letters.  They won't change.

    But one summary level might export as, for example "=SUM(G3:G12)" (There may be several of these)

    And the second level may export as, for example "=SUM(G3:G26)"  (There may be several of these)

    And the third level should export as, for example, "=SUM(G3:G135)"

    I really need to know if there's some kind of decent workaround for this; a way of constructing a string that will sum the rows above properly.  

    My users need to see these formulas, not the resulting values.  

    Yes, I know Microsoft dropped support for natively exporting formulas some time ago.  I also know they've been asked to restore this capability, and those requests have yet to be answered.

    I just need a decent, do-able workaround.  I just don't know how to get the row numbers into some kind of formula that can be interpreted as something like the formulas above.  Would row number work?  Might you be able to show me a sample of a function I can use? Should I set up my own row counter column?  I really don't know how to approach this.

    Please help.

    Sunday, October 6, 2019 2:20 AM

All replies

  • I'm running the most current versions of SSRS and Excel.

    I have an SSRS report with 10 columns and three group summary levels.  I desperately need those summary rows exported as formulas to Excel as real formulas.  

    I need to know how I create some kind of text formula or something in the summary cells that will export as a formula.

    I know the column letters.  They won't change.

    But one summary level might export as, for example "=SUM(G3:G12)" (There may be several of these)

    And the second level may export as, for example "=SUM(G3:G26)"  (There may be several of these)

    And the third level should export as, for example, "=SUM(G3:G135)"

    I really need to know if there's some kind of decent workaround for this; a way of constructing a string that will sum the rows above properly.  

    My users need to see these formulas, not the resulting values.  

    Yes, I know Microsoft dropped support for natively exporting formulas some time ago.  I also know they've been asked to restore this capability, and those requests have yet to be answered.

    I just need a decent, do-able workaround.  I just don't know how to get the row numbers into some kind of formula that can be interpreted as something like the formulas above.  Would row number work?  Might you be able to show me a sample of a function I can use? Should I set up my own row counter column?  I really don't know how to approach this.

    Please help.

    Sunday, October 6, 2019 2:19 AM
  • Any suggestions, Zoe Zhi?
    Monday, October 7, 2019 2:42 AM
  • Hi Karen,

    From your post, I am afraid looking into SSRS might not be the right way for this kind of request. SSRS report is more of a report showing tool not a analysis tool. When the processed report was exporting to other format, only the evaluated value would be export, not the expressions. You could see here: Exporting to Microsoft Excel (Report Builder and SSRS)--Text Boxes and Text


    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 7, 2019 3:20 AM
  • I'm not doing analysis.  I just need to export summary formulas.
    Monday, October 7, 2019 3:58 AM
  • Yes, we know. Just this is the designed behavior that SSRS only exports values.

    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 7, 2019 5:33 AM
  • Hopefully that will change soon.  But in the meantime, I'm trying to work out creating the summary formulas using the correct column and row references to correctly summarize the data in the above cells in the group at all three levels.  This has proven VERY time-consuming, but I do think it will be ultimately successful.  Sorting the data into groups and starting a sum command at row 3 where the data begins on each sheet isn't easy.
    Monday, October 7, 2019 6:36 AM
  • If you really need this kind of function, I suggest you submit the requirement at https://feedback.azure.com/forums/908035-sql-server.

    If the requirement mentioned by customers for many times, the product team may consider to add this feature in the next SQL Server version. Your feedback is valuable for us to improve our products and increase the level of service provided.


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 9, 2019 4:38 AM