Post reply

SSRS is is possible to update existing Excel file with SSRS output

  • January 9, 2014 at 11:15 am

    #396081

    Hi, all

    I have monthly report produced by SSRS, now it's mailed as Excel file thru subscription, and users now want to keep history of this report for 12 month on 12 Excel tabs ile (and to have some chats for this timeline), one tab - for each month.

    Do you think it's possible with SSRS, produce output then somehow insert it into existing file, I can't run this report for prev months as data is dynamic + I don't want extra overhead. Alternative is easy : do it manually, receive monthly file, copy/paste into 12m file, send it to final user and viola... But need to eliminate human intervention completely as per our policy...-(. Or maybe I can create file as excel on network and run macros to update master file, but not sure is it possible to create excel file with ssrs...?

    And I operate only SSRS, Excel don't have any c# etc.

    Thanks

    Mario

  • January 9, 2014 at 1:01 pm

    #1678951

    I don't see this working with SSRS alone. I've seen solutions to this but it requires some programming knowledge. Since the data is dynamic and this prevents you from re-running a query for historical reports it might work out for you if you kept your monthly results in a history table. After getting the current month data go grab the historical data. If you have a grouper in your report for month and tell the group to start a new page after then the export to Excel should create each month's report on a separate tab.

    Cheers

  • January 9, 2014 at 1:12 pm

    #1678955

    mario17 (1/9/2014)

    I can't run this report for prev months as data is dynamic

    Can't you store the data somewhere every month so you can access it for your report.

    Build some sort of data mart?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • January 9, 2014 at 2:24 pm

    #1678972

    Thanks,

    yes I already stored all data in History table, so this issue can be solved,

    But in 2008 I still can't name Excel tabs I think, and I need them to be 'Jan-2014','Feb-2014'...

    I mean I'm using document map to send each month in separate tab.

    Best

    M

  • January 9, 2014 at 2:42 pm

    #1678985

    Yeah, I think it is SSRS 2008 R2 that introduced the ability to name tabs. I came up on this problem myself a few months ago, but our report server is just 2008. Since automation is mandated it looks like you are going to need the subscription to export the report out to the file system and then have some macros execute on the file. Solving this issue with just SSRS isn't going to happen given the version you are running.

    Cheers

  • January 9, 2014 at 3:18 pm

    #1679001

    Thanks, again for your help.

    I'm on Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) (Build 7601: Service Pack 1)

    I thought that this ability comes only on 2012.

    I also will explore subscription to file system, never tried it before.

    Best

    Mario

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply