SSRS Dynamic Filenames for Email Subscriptions

Question

We are in the process of migrating our reports from Crystal Reports to SSRS. In Crystal Reports we use variables to dynamically generate our filenames so when the report gets sent out via email, the file has the report name and execution date. (e.g. MonthlyReport09-07-2012.xls).

Is this possible in SSRS? I don't see any straightforward approach to using variables in the filename when subscribing to a report. This could prove troublesome when sending multiple reports with the same filename to the same person because it would be difficult to discern which report is which.

Any help is greatly appreciated. Thank you SO.

Thermos

This can be done using data driven subscriptions (Enterprise SKU only - not counting Dev and Eval, obviously) – Calorifacient

Answer 1

There is no feature in SSRS as such but there is a work around for this. You have two options
Option 1:
Instead of emailing it directly first dump the file in fileshare location which can be something like \machine-name\ExportReports\ReportName\ then create a windows job which renames the file to the format you want and emails it in the next step.

Option 2:
Refer to this blog what you want starts from section "Generate a PDF output file programmatically" now you can use this in an assembly then have some scheduling mechanism which picks up the schedule. This then calls the DLL which generates the report and emails it.

Handiwork

Answer 2

You cannot specify the report filename in a standard subscription in Reporting Services.

If you have Enterprise edition (or SQL 2012 Business Intelligence edition) you can use the Data-Driven Subscriptions features that allows you to specify the report filename (and other properties) based on data retrieved from a table.

If you have Standard edition, then your options are either of the ones suggested by Bhupendra, or you could look at scripting the report generation using the "rs.exe" utility supplied with Reporting Services and use Database Mail and SQL Server Agent to handle the emailing and scheduling.

Ary

Answer 3

Use @timestamp in the name of the file and it will translate at run time.

Civet

@timestamp in the file name is only an option when creating a file-share subscription, not an email subscription. – Frambesia

@Chirs, can I format the timestamp to generate file name lime 07292015_MyFileName? That is, {mmddyy_MyFileName}? – Culture

Answer 4

This post looks pretty old , but better late then never ...

There are some tools on the market , which can run SSRS reports : CRD, R-Tag and RemiWare

These are Desktop tools but I guess you are not looking to replace SSRS , just to extend it.

I am not sure about the CRD and RemiWare , but R-Tag supports data driven reports and dynamic names. It also can be used with Standard license.

Sines

Answer 5

I was able to automate the emailing and change the file name by looping thru a table of accounts, invoices and emails, then setting the parameters and renaming the reportname and pathname in catalog be execution at the end of the after the execution I did a wait for 2 seconds then when to the next loop. I the end I set the path and name back to orginalname. Performed well.

Unblown

Answer 6

@timestamp works for Windows File Share as answered by Chris.

For Email deliveries, you could use:

@ReportName -specifies the name of the report.

@ExecutionTime - specifies when the report was executed.

For more details- MS Docs

Arteriovenous

Answer 7

You can do this by changing the filename directly from the table [ReportServer].[dbo].[Catalog]

Make sure to add a forward slash to the Path

For e.g. I created a SQL Job that ran every night before the report and added the date to the filename.

Sample Job (replace [ItemID] corresponding to your Report):

DECLARE @PREFIX CHAR(25) = 'REPORT_NAME_';
DECLARE @SUFFIX CHAR(8) = CONVERT(CHAR(8),GETDATE(),112);
DECLARE @VARNAME CHAR(33) = CONCAT(@PREFIX,@SUFFIX);
DECLARE @VARPATH CHAR(34) = CONCAT('/',@PREFIX,@SUFFIX);

UPDATE [ReportServer].[dbo].[Catalog] 
SET 
[Name] = @VARNAME,
[Path] = @VARPATH
WHERE [ItemID]='63D051EE-3139-4F50-ADBB-1C944F3D5D47';

Tidewater