This blog explains how to generate dynamic filenames using the SQL Server report subscription when there is an SSRS report that needs to be generated on a scheduled basis with a specific filename.
The filename should conform to the following requirements:
- Contain a report code/prefix, e.g., “AdvSales_2015_01,” where “AdvSales” is the prefix.
- The second part of filename is a four digit year.
- Third part has the month separated by an underscore (_).
This diagram below illustrates the process we are using with this workaround.
We will be using the AdventureWorks database and an existing AdventureWorks sales report.
Report Configuration (Metadata)
I have created a table in the database to define the report configuration. The image below shows the fields and data.
- Report Code: This column contains the report code or prefix which will be used in first part of filename.
- Report Name: This is a descriptive column in the table. However, stored procedures (discussed below) will return this column as the filename, which includes:
ReportCode + ‘_’ + CAST(Year(GetDate()) as varchar(4)) + ‘_’ + CAST(Month(GetDate()) as varchar(2))
The filename should not contain an extension.
- Report Path: This field contain the destination on which exported file will be generated.
GetFilename Store Procedure
We need to write a stored procedure which the subscription can call on to generate filename and report parameters. Data driven subscription have ability to call stored procedures. The result set of the stored procedure can be used to configure filename and report parameters. The stored procedure below is returning FromDate and ToDate by calculating it from GetDate(). It also return the ReportName and Report location.
This section describes how to create a data driven subscription. The subscription can be created in the SSRS report manager.
Before creating a subscription we need to create a share schedule on which the subscription will run.
From Report Manager Home Page select “Site Settings“(Top-Right link) and Select “Schedules” and click “New Schedule.”
Define a new schedule. This schedule will be attached to the report subscription.
We can now create a subscription. Right-click the report and select “Manage.”
Click “Subscription” from the left navigation menu and select “New Data-driven subscription”.
The Subscription Definition will require a subscription name and how recipients will be notified. There are two options: Windows File Share/Network Share, or Email. We will select Windows File Share.
Click the “Next” button to proceed.
Select the Data Source that has the GetFilename stored procedure to get filename and report parameters.
Click “Next” and enter a command for executing the stored procedure, as in the example below. Click the “Next” button to proceed.
SET the filename and report location returned by the stored procedure. The dropdowns will include the list of fields returned by the stored procedure result set. Provide the credentials and click “Next.”
Select the report parameters from the dataset return stored by the procedure. Click “Next.”
Select “Schedule” and click “Finish” to complete the subscription creation.
This subscription will be invoked by the scheduler selected at the last step of the subscription creation. After the subscription is executed, the report exported file with name “AdvSales01_2015_3.xlsx” can be found in the destination network shared folder.