March 26, 2010 at 12:30 pm
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
March 26, 2010 at 12:34 pm
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 😀
March 26, 2010 at 12:37 pm
hmm.. never tired linking a report. that sounds like a good option I will look into that. Thanks 😎
March 28, 2010 at 1:54 pm
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.
March 30, 2010 at 7:15 am
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!
April 2, 2010 at 7:48 am
just wanted to post a thanks for the suggestion of liked reports. worked like a charm.
April 2, 2010 at 8:07 am
you are welcome, thanks for letting me know it worked for you.
April 12, 2010 at 3:22 pm
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.
April 12, 2010 at 3:27 pm
that sounds interesting, i'll have to look into that
April 12, 2010 at 5:05 pm
I think you need the Enterprise Edition to use Data driven subscriptions
April 14, 2010 at 7:47 am
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.
April 14, 2010 at 8:03 am
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