In our last lesson, we explored how to create a basic SSRS subscription. This lesson is going to add some dynamic capabilities to the standard SSRS subscription to create a “data-driven SSRS subscription”.
We are going to set up a data-driven SSRS subscription that, on a pre-defined schedule, outputs a report for share accounts opened for each branch in our credit union. So, we will have one report automatically sent to a location on our network with the appropriate details for each branch. Instead of creating and managing eleven separate standard SSRS subscriptions, we can accomplish this with a single data-driven subscription.
What is a data-driven SSRS subscription?
A data-driven SSRS subscription is defined by Microsoft as “a data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time”. Where a standard SSRS subscription has the parameters, rendering method, and file path statically pre-defined, a data-driven SSRS subscription allows you to define all of the parameters and options at run-time. These parameters and options are defined by data from some data source – like the results of a query or data contained in a table. We will walk through one example of how you can create and use a data-driven SSRS subscription.
Modify One of Our Existing Reports
Open up SSDT or BIDS to the project “Credit Union Reports” if you haven’t already. There should be a report named “Share Account Details by Branch”. This was the detail report that we used for our drill-down report example. Right click on the report’s name in the “Solution Explorer” and click “Copy”. Then select the “Reports” folder in the “Solution Explorer” – so it is highlighted – and then press “Control” and “V” on your computer to make a copy of the report.
A new report named “Copy of Share Account Details by Branch” should appear. Rename this report “Share Account Details by Branch with Dates.rdl” by right clicking on the report and selecting “Rename”. Double click the newly renamed report to open it up.
Once the report is opened, double click on the “ShareAccountDetails” dataset in the “Report Data” panel on the left side of the screen. The “Dataset Properties” window will appear on your screen. Currently, this report has only one parameter: the branch name. We want to edit the query so that we can also filter based on the date in which the account was opened. See the highlighted line in the image below to see how to modify the query:
Once you have added the additional criteria to the WHERE clause of the query, press “OK”. Edit the “StartDate” and “EndDate” parameters from the “Parameters” folder of the “Report Data” panel so that each parameter’s data type is set to “Date/Time”. Preview your report to ensure that the parameters are working as intended.
Save the report and the entire solution. Upload the new report to the report server like we have done in the past if you haven’t already.
Creating the Data-Driven SSRS Subscription
Navigate to your report server in your browser. Hover over the report that we just uploaded named “Share Account Details by Branch with Dates”, choose the drop down arrow that appears, and select “Manage”. Click the “Data Sources” option on the left navigation panel once the report properties page appears. Just like we did when creating a basic SSRS subscription, we need to store our credentials securely in the report itself. Review the previous post if you need to review how to complete this step.
Once you have entered and saved the credentials for the data source, choose “Subscriptions” from the left navigation panel on the screen. On the top menu, choose “New Data-driven Subscription”:
The page that appears will have the header “Step 1 – Create a data-driven subscription: Share Account Details by Branch with Dates”. In the “Description” text box, enter a name for this subscription that will make it clear what the subscription is used for. I’m simply going to enter “Share Details for each Branch”. In the “Specify how recipients are notified:” dropdown, choose “Windows File Share”. If the radio button next to “Specify for this subscription only” is not selected, do so now.
Press “Next” once complete.
Step 2 of the process is where we define in which data source our data-driven data is going to reside. We will be using a branch list that is contained in the “CreditUnion” SQL Server database on the local machine. In the “Connection string:” box, type “data source=localhost; initial catalog=CreditUnion”. You may replace “localhost” with your SQL Server instance name and “CreditUnion” with your specific database name.
Under “Connect using:” add your Windows credentials is the username and password fields. Check the box next to “Use as Windows credentials when connecting to the data source”. Note: if you have been accessing the database using SQL authentication instead of Windows authentication, do not check the box.
Press “Next” once complete.
In this step, we define the query that we will use to retrieve the data for the data-driven SSRS subscription. I am going to use the query that you see on the screen in the image below:
I am returning two columns from a table named “Branch”: the “Branch_Name” column and a concatenated string expression that returns the output “2010 Accounts Opened for [Branch_Name]”. This expression, named FileName, will be used to name each branch’s file that gets output to the network location. We will see this used shortly.
For now, simply press the “Validate” button towards the bottom of the screen to ensure that your SQL syntax is correct. Once validated, press “Next”.
Step 4 is where we get to the real details of the data-driven subscription. Here is where start to define all of the dynamic or static options we will be using for our subscription. Under “File name” select the radio button next to “Get the value from the database”. From the dropdown, choose the column name “FileName”. This is the concatenated string expression we defined in step 3. This tells SSRS how to name the report for each branch.
Under “Path” leave “Specify a static value” selected. I am going to send the files to my C drive, so I will use the path “\\localhost\c$”. Under “Render Format”, select “Excel” from the static value list dropdown. For “Write Mode”, choose the static value “Overwrite”. Similarly, for the “File Extension” section, choose “True” from the static dropdown list. Add your Windows credentials for the “User name” and “Password” sections just like we have done in the past. Your page for step 4 should look like the image below:
Press “Next” once you are complete.
In step 5, we are able to specify the values for our report parameters. Under “Branch Name” choose “Get value from the database” and select “Branch_Name” from the dropdown. For the “Start Date” and “End Date” parameters, you can statically enter a date. I am going to enter “1/1/2010” and “12/31/2010” based on the data for this fake credit union.
See the image below to ensure each parameter was defined correctly:
Press “Next” once complete.
Step 6 is where we define how the subscription will be executed and scheduled. In our scenario, we want the report to be schedule to run at a specific time. To do this, select the radio button next to “On a schedule created for this subscription”.
In step 7, we are going to create the schedule for the data-driven SSRS subscription to execute. We have done this before when scheduling a basic SSRS subscription. I am creating this post on a Monday at 11:20 AM. So, I am going to schedule the subscription to execute ten minutes from my current time, or 11:30 AM. To ensure that the subscription only executes once, I am going to choose the radio button next to “Repeat after this number of days” and make sure that a “1” is in the text box. Adjust the “Start time” to about five or ten minutes from whenever you are making these changes (i.e. so if it is 1PM for you, set the “Start time” value to 1:10PM.)
Press “Finish” once you have completed the schedule step.
Monitoring the Execution
Once you pressed “Finish” in the last step, you are brought back to the “Subscriptions” page for the “Share Account Details with Dates” report. If you look at the image below, you will see the subscription clearly exists with a status of “New Subscription” meaning that it has not yet been executed.
When the time comes for the report to execute, this status will change. In my case, since I told SSRS to execute the subscription at 11:30 AM, I will wait until 11:30 AM and then refresh this page. You will notice that the row is slightly different now:
Since I have eleven branches in this fake credit union, eleven files are created – one for each branch. If I navigate to my C drive, you can see that all of the files are created properly. Notice that the file name is exactly as we wanted it to appear in the SQL query that we created in step 3.
We have successfully created our first data-driven SSRS subscription! Before using a data-driven SSRS subscription, we would have needed eleven separate SSRS subscriptions. Each would have to be manually maintained and managed. Data-driven SSRS subscriptions allow us to create dynamic and efficient subscriptions with significantly less administrative overhead involved with managing them.
There are some very inventive and creative ways in which data-driven SSRS subscriptions have been used in the past. Spend a few minutes searching Google to learn more!