email excel report

  • ok, I've got a report on the report server that gets clients by state, either individual or all. very simple report, client name and state.

    they would like this report monthly, so I wanted to email it, probably by using subscription on report server, but here's the kicker. they want each state in a separate excel file.

    any suggestions on the best way to do that? can be script or whatever. i don't know if maybe a script that calls for each state in state column do an excel sheet?

    thanks

  • Create the report with a parameter, create 50 linked reports each with a different default parameter, create subscriptions for each report as needed. send the client a bill for 50 reports, go to the caribean with all of the extra money you made 😀

  • hmm.. never tired linking a report. that sounds like a good option I will look into that. Thanks 😎

  • I made a SSIS package that runs as a job. It puts the result of a view into an Excel file and then emails it to a list of people daily. It can also calculate weekly and montly results because the Excel destination in BIDS allows the specification of a tab.

  • ok, I setup the linked reports and emails.. sent a test one, and confirmed that they really wanted one report for each state. got a thumbs up, I'm thinking they will be surprised come april first and they've got an inbox full of reports..

    Thanks for the suggestion!

  • just wanted to post a thanks for the suggestion of liked reports. worked like a charm.

  • you are welcome, thanks for letting me know it worked for you.

  • You could also use a data driven subscription to select the list of States and use this as input into the subscription. This way if a new state is added you don't need to create a new linked report and a report for the new state will be generated automatically.

  • that sounds interesting, i'll have to look into that

  • I think you need the Enterprise Edition to use Data driven subscriptions

  • bobby.creech (3/26/2010)


    ok, I've got a report on the report server that gets clients by state, either individual or all. very simple report, client name and state.

    they would like this report monthly, so I wanted to email it, probably by using subscription on report server, but here's the kicker. they want each state in a separate excel file.

    any suggestions on the best way to do that? can be script or whatever. i don't know if maybe a script that calls for each state in state column do an excel sheet?

    thanks

    You can export file using BCP command and then attach in mail.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Paresh Prajapati (4/14/2010)


    bobby.creech (3/26/2010)


    ok, I've got a report on the report server that gets clients by state, either individual or all. very simple report, client name and state.

    they would like this report monthly, so I wanted to email it, probably by using subscription on report server, but here's the kicker. they want each state in a separate excel file.

    any suggestions on the best way to do that? can be script or whatever. i don't know if maybe a script that calls for each state in state column do an excel sheet?

    thanks

    You can export file using BCP command and then attach in mail.

    True, but I was looking for automation (I don't want to export every state and attach to an email) I have enough to do. the linked reports with subscriptions works great

Viewing 12 posts - 1 through 11 (of 11 total)

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