January 28, 2011 at 11:54 am
I send out a report weekly on Fridays via email.
The report is distributed by SSRS in pdf format.
The report is currently attached to the emails with the name: "Weekly Report.pdf"
Is there a way to have ssrs attach the weekly report dynamically naming the report using the current date?
For example using today: "Weekly Report 01282011.pdf"
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
January 31, 2011 at 2:53 am
Hi DougGifford,
you can use the Data-driven subscriptions to dynamically change the Report file name, You can use the query
"select 'Weekly Report_'+convert(varchar,GETDATE(),112) as ReportFilename"
and configure the ReportFilename as the name of the file that u are e-mailing
February 1, 2011 at 8:22 am
If you don't have Enterprise Edition and data-driven subscriptions aren't available, you can build an SSIS package that runs the report, saves it with a dynamic name, and mails it. It's the same concept as a DDS. The only downside is that it runs serial, not parallel, so the process takes a little longer.
February 1, 2011 at 8:29 am
Doug Lane (2/1/2011)
If you don't have Enterprise Edition and data-driven subscriptions aren't available, you can build an SSIS package that runs the report, saves it with a dynamic name, and mails it. It's the same concept as a DDS. The only downside is that it runs serial, not parallel, so the process takes a little longer.
Any code sample you can send over? I could use that and I've never used SSIS before...
February 1, 2011 at 8:48 am
Ninja's_RGR'us (2/1/2011)
Doug Lane (2/1/2011)
If you don't have Enterprise Edition and data-driven subscriptions aren't available, you can build an SSIS package that runs the report, saves it with a dynamic name, and mails it. It's the same concept as a DDS. The only downside is that it runs serial, not parallel, so the process takes a little longer.Any code sample you can send over? I could use that and I've never used SSIS before...
Sure. I'll throw a vanilla copy together and post it here in a little while.
February 1, 2011 at 8:50 am
Thanks a mill.
February 1, 2011 at 10:12 am
Okay, here's the basic framework. I had to strip out a lot of client information, so there are holes here and there that need patching. If you're comfortable with SSIS, you should have little trouble figuring out what needs filling in. It's pretty handy; I'll have to flesh out a working generic framework and post it on my blog. For now, if you have any questions, just ask.
February 1, 2011 at 10:25 am
I'm a total 0 in SSIS so I think I'd rather wait for your true framework.
I just opened the package and I just don't know where to start.
February 1, 2011 at 10:39 am
Thanks for your suggestions.
In my original post I stated that I send out a report.
What I did not mention is, there are over 35 of these reports being delivered to over 100 people.
Each report is customized for each of the 35 customers, each report being sent to 3 or more persons.
I know that I can add an @ExecutionTime to the subject line in the Subscription Report Delivery Options.
That does not handle the renaiming of the attachment.
I will look into your suggestions.
Thanks
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
April 20, 2011 at 12:02 pm
bhaskar varada (1/31/2011)
Hi DougGifford,you can use the Data-driven subscriptions to dynamically change the Report file name, You can use the query
"select 'Weekly Report_'+convert(varchar,GETDATE(),112) as ReportFilename"
and configure the ReportFilename as the name of the file that u are e-mailing
I don't see any place to configure the ReportFilename. Can you provide more detail?
April 20, 2011 at 12:28 pm
Data-driven subscriptions are in the enterprise edition only (not the standard edition)...hence the SSIS package Doug provided if you're not using enterprise.
Leonard
Madison, WI
April 28, 2011 at 4:35 am
You can try it:
Search subscription id for your report
SELECT
A.SUBSCRIPTIONID
FROM REPORTSERVER.DBO.REPORTSCHEDULE A
JOIN MSDB.DBO.SYSJOBS B
ON CAST(A.SCHEDULEID AS VARCHAR(40)) = B.NAME
JOIN REPORTSERVER.DBO.REPORTSCHEDULE C
ON B.NAME = CAST(C.SCHEDULEID AS VARCHAR(40))
JOIN REPORTSERVER.DBO.SUBSCRIPTIONS D
ON CAST(C.SUBSCRIPTIONID AS VARCHAR(40)) = D.SUBSCRIPTIONID
JOIN REPORTSERVER.DBO.CATALOG E
ON D.REPORT_OID = E.ITEMID
WHERE E.NAME = 'Name of your report'
Now edit topic here:
UPDATE REPORTSERVER.DBO.SUBSCRIPTIONS SET ExtensionSettings = '<ParameterValues><ParameterValue><Name>TO</Name><Value>raporty.windykacyjne@provident.pl</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>EXCEL</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>@ReportName was executed at @ExecutionTime/Value></ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue><ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>'
WHERE SUBSCRIPTIONID = '8597C181-AE56-4ED7-B6E3-E9939B858C6E'
Now create job on a serwer and create step:
July 17, 2014 at 12:22 pm
In Windows File Share Delivery method a File Name can be changed but for EMail Delivery method this do not work. Any options to change the File Name in Email delivery method?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply