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:

Modify Dataset

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”:

New Data-driven Subscription

Step 1

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.

Step 1

Press “Next” once complete.

Step 2

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.

Step 2

Press “Next” once complete.

Step 3

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:

Step 3

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

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:

Step 4

Press “Next” once you are complete.

Step 5

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:

Step 5

Press “Next” once complete.

Step 6

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”.

Step 6

Press “Next”.

Step 7

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.)

Step 7

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.

Pre Execution Status

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:

Post Execution

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.

Files in C Drive

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!

In our last post, we showed you how to create drill down SSRS reports so you can create incredibly dynamic reports. Drill down reports create dashboard-like functions right within SSRS. For those credit unions trying to maximize their BI investment, initially using SSRS over third-party BI visualization tools can minimize your upfront costs.

What is an SSRS Subscription?

This post will introduce you to creating SSRS subscriptions. But, what is an SSRS subscription? Think of any other subscription you have in your life to a blog, newsletter, or magazine. Your subscription allows you to receive something that you want at a specified time. SSRS subscriptions are very similar; you can subscribe to a particular report and have it delivered to you at a specified time and location.

SSRS allows you to receive reports in a few different ways. The most common delivery methods are to have the report sent to a windows file share or to your email. If you have Microsoft SharePoint in your organization, you can have SSRS reports delivered directly to SharePoint as well. In this particular post, we will show you to how to create a windows file share subscription for a single report. There are some very inventive ways to use SSRS subscriptions beyond what we will discuss in this post. Head over to Microsoft’s SSRS Subscription and Delivery page to learn more if you are interested!

Why use SSRS Subscriptions?

Suppose we have a key weekly lending report that is currently being created the old-fashioned way: manually through Excel. The person creating that report in Excel will complete the report and send it to three or four people. Those three or four people save the copy locally to their drive thus creating multiple copies of the same document. Along with having multiple copies of the same document stored on the shared drive, we are also using unnecessary space on the mail server. IT network and server staff deal with this dilemma regularly.

With SSRS, you could create that report and have the data dynamically retrieved from a SQL database like we have done in past examples. Then, we create an SSRS subscription that places the report output in a specified location at 7 AM every Monday morning. Now, those three or four individuals who used to receive the report in their inbox can simply head to the designated location to view the report. They never have to ask for the report and the person who used to develop the report never has to manually produce and send it again.

Are you that person stuck developing a report manually and then sending it out to various people who request it? Then this post is for you!

Setting up the Report Security

To create subscriptions, we need to first navigate to the report browser. This may be the first time you have done this. To do so, ask your IT/BI area for the SSRS URL. It typically has the form: http://[server name]/Reports. This is the URL that you use to view reports and manage subscriptions. Once you are there, you may need to upload the reports that we created if you haven’t deployed them already.

First, create a new folder on the home page (if you have permission to do so) named “Credit Union Reports”.

Create New Folder

Once the folder is created, click on it to be brought to the page for the “Credit Union Reports” folder. In the SSRS menu, there is an option title “Upload File”. Click that button, browse to where you saved your SSRS reports from previous posts, and select the .rdl files for each report. I will be uploading two reports: “Share Account by Branch” and “Share Accounts Opened”.

If you have done this correctly, the “Credit Union Reports” page of SSRS in your browser will look like the image below.

Folder with Reports

If you click on any of the reports, you should be able to view the output of those reports. This is similar to clicking the “Preview” button for a report in SSDT or BIDS.

Hover over the report named “Share Accounts by Branch”. You should see a dropdown arrow to the right of the report name. Click this arrow and choose “Manage”.

Manage Report

When the browser changes, select the “Data Sources” from the left side of the screen. If the radio button next to “A custom data source” is not selected, select it now. Under “Connect Using:” select the radio button next to “Credentials stored securely in the report server”. Subscriptions require that credentials be stored securely in the report properties. It is recommended that you have a dedicated username with the proper privileges – something like “Data_Warehouse_Reports”. That way, you don’t have to use your own personal credentials in the report data source along with having some other benefits.

Once you choose the radio button next to “Credentials stored securely in the report server”, enter either your personal credentials or the credentials for your dedicated report/data warehouse account (this is more of an IT thing so you may or may not have one). Click the check box next to “Use as Windows credentials when connected to the data source” (this assumes you are using Windows credentials through active directory – which is most common – instead of a SQL Authentication account created for the SQL instance).

Data Source Security setup

Press “Test Connection” towards the bottom of the screen to ensure that all properties and credentials have been entered correctly and that you have the necessary privileges. If everything was entered properly, green text under the “Test Connection” button will appear saying “Connection created successfully”.

Test Connection

Press “Apply” to save your changes.

Using the breadcrumb trail at the top of the browser page, head back to the “Credit Union Reports” page.

Creating the SSRS Subscription

Now that we have stored the credentials in the report, we can now create the subscription. From the “Credit Union Reports” page of the report server, hover over the “Share Accounts by Branch” report. When the dropdown arrow appears, click on it and then choose “Subscribe”. The subscription delivery options page should appear:

Blank Subscription

This screen allows you to specify how, where, and when you would like the report delivered. In the “Delivered by:” dropdown, choose “Windows File Share”. This method will allow us to send the report to a location on our network. When you choose “Windows File Share” from the menu, the screen should change significantly:

Blank Windows File Share

We can leave the “File Name” text box with the default value of the report’s name. The “Path” text box is where we specify the location that we would like the report to be placed. There is an important subtlety to note here: the path must be in UNC (Universal Naming Convention) form. If we wanted to place the report on our C drive, we couldn’t use the path “C:\”. Instead, we would have to type it with the form “\\[Computer or Server Name]\c$”

To keep things simple, let’s place this report on our C drive. In the “Path” text box, type “\\[Computer or Server Name]\c$” where the [Computer or Server Name] is replaced with the name of your computer or server you are working on. For example, if I wanted to place this file on a computer named “Brewster”, I would use the path “\\Brewster\c$”.

In the “Render Format:” dropdown, choose “Excel”. Before we enter credentials, click on “Select Schedule” towards the bottom of the screen.

Select Schedule Screen

This screen allows us to specify when we would like this report to run. We can be very specific with the scheduling process which gives us some nice, flexible options. We want to set this report to run in about five or ten minutes from whenever you are completing the subscription. So, if it is 2pm on a Wednesday when you are following along with this post, be sure to select the checkbox next to “Wed” and set the “Start Time” to about 2:05 pm.

Press “OK” once you have this set up. Again, we want this to run within the next few minutes (if you are following on), so be sure you have your times set up correctly.

Now, you should be brought back to the “Subscription” screen. Go ahead and enter your credentials in the “User Name:” and “Password:” text boxes. I am writing this post at approximately 1:20 pm, so I have scheduled the subscription to execute at 1:25 pm.

Completed Subscription Details

With everything completed as you need, press “OK”. As long as there were no errors immediately flagged by the report server, your SSRS subscription should be scheduled. You should be returned to the “Credit Union Reports” page in SSRS.

Now, wait the few minutes left until the subscription should be executed. In my case, this means waiting until 1:25 pm. While you wait, you can click the “My Subscriptions” hyperlink at the top of the page.

My Subscriptions

When you click that hyperlink, you will be brought to a screen that contains all of your subscription details. Since this is the first subscription we have set up, you should see just a single row with the “Status” field set to “New Subscription”.

My Subscriptions Details

Once the subscription executes, this screen will be updated with the results of the last subscription execution. I have waited a few minutes, so it is past 1:25 pm which is when I had the subscription scheduled to execute. I am going to refresh the “My Subscriptions” page.

Successful Subscription

If you look at the “Status” column now, you will see that the value has changed to reflect the status of the last execution. Since this subscription executed without issue, it tells me that “The file ‘Share Accounts by Branch.xlsx’ has been saved to the “\\XXXXX\c$” file share.”.

To be sure, let’s navigate to the C drive where we told SSRS to place the report.

Windows Explorer Proof of Subscription

We can see that the “Share Accounts by Branch” report has been placed to the C drive in an Excel format just like we expected!

SSRS subscriptions can be incredibly helpful when we need to automate the delivery of reports that we have created. One of the key components of business intelligence is automating and improving the efficiency of our reporting operations. By taking advantage of the inherent features of SSRS, we can drastically reduce the manual effort involved in completing and submitting reports throughout our credit unions.

Our previous post showed you how to create an SSRS report with multiple parameter data types. By doing that, we created even more dynamic reporting opportunities.

An incredibly invaluable function of SSRS is the ability to “drill down” to other reports. The concept of “drilling down” is where you can click on a specific item in the report to get more detail. For example, suppose you have a table showing the count of all members grouped by the state in which they reside. We could create drill down capabilities so that if you clicked on the name of the state, a new report would appear showing the name and address for all members who reside in the state you selected. Effective dashboards frequently leverage the concept of drill downs; we are going to apply this concept to our SSRS reports.

Setting Up our Summary and Detail Reports

When creating drill down SSRS reports, you must have at least two reports: a summary report and a detail report. The summary report will be the report that user will interact with to drill down to additional details. The detail report will contain all of the details we want to display after a user clicks on a specific row or item in our summary report.

In our initial post on building your first SSRS report, we created a report that showed the number of share accounts opened at each branch and the total current balance of those shares. For this post, we will use the report created in our first lesson as our summary report.

Since we already have our summary report, we need to now build a detail report. In this detail report, we want to show details for each account opened for a particular branch. Our goal is to have users be able to click on a branch name from the summary report and then be brought to the detail report for that specific branch.

To start, open up our existing project in SSDT or BIDS if it is not already. In the “Solution Explorer” on the right side of your screen, right-click on the “Reports” folder, hover over “Add”, and select “New Item”. Make sure that “Report” is selected on the window that appears, and give this report the name “Share Account Details by Branch.rdl”. Press “OK” once complete.

Add Detail Report Item Screen

Create the “Data Source” to whatever database in which your data resides. Name this data source “CreditUnion” (see the building your first SSRS report post to review how to do this).

Once the data source is created, right-click on the “Datasets” folder and a new dataset. Using the “CreditUnion” data source we just created, we need to define the SQL query that will populate this report. We are going to include many columns including: member number, account open and close dates, current balance, etc. One of the most critical aspects of the query you place in the new dataset, which we will call “ShareAccountDetails”, is that it includes a WHERE clause filtering on the branch:

ShareAccountDetails Dataset

This parameter is critical to our ability to create the drill down function. Press “OK” to save your dataset if you have not done so already.

You should see the “Branch_Name” parameter in your “Parameters” folder in the “Report Data” panel on the left side of your screen. If you remember back to our last post on creating an SSRS report with multiple parameters, we dynamically defined the available options for the “Branch_Name” parameter using a dataset. That dataset included a distinct list of all branch names from our database. Using the previous post as a guide if you need to review, create a dataset that contains a list of distinct branches. Then, modify the “Branch_Name” parameter so that its available values is defined by the branch list dataset. Unlike the last post, set the “Branch_Name” parameter to only accept a single value instead of multiple values. The “Available Values” section of the “Report Parameter Properties” window should similar to the image below:

Branch_Name Parameter

Press “OK” to save the parameter options if you haven’t done so already.

Next, we need to create a table in our report for the data to reside in once rendered. Either drag a blank table from the toolbox on the left side of your screen, or right-click on the blank report, hover over “Insert”, and select “Table”. Move the table to the top left corner of the report window.

Now, we need to add columns from our “ShareAccountDetails” to our table. Remember to hover in a cell of the data row (bottom row) of the table and wait for the table icon to appear. When we completed this when creating our first SSRS report, we only had one dataset. Since we have multiple datasets in this report, you must choose both the data source and the dataset from which you want to retrieve data:

Add Dataset Columns to Table

That was a subtle difference, but one that is certainly worth mentioning. To keep things easy, add each column of the “ShareAccountDetails” dataset to the table in our report.

Just like we have done before, make the header row bold and resize each column’s width to fit. In our previous posts, we formatted the column’s data type by right-clicked on a cell and selecting “Text Box Properties”. Make sure to format the “Account Opened Date”, “Account Closed Date”, and “Current Balance” columns appropriately.

Before previewing your report, it should look similar to the image below:

Pre Preview Table

Go ahead and preview this report now.

Detail Report Preview

This report is pretty basic, but it accomplishes what we need it to for the time being. At this point, we can say we have completed our “detail report”.

Creating the Drill Down Function

At this point, we have created both a summary report and a detail report. Now, we need to define the drill down action to tell SSRS what we want to happen when users click on the branch name from the summary report.

First, open up the summary report named “Share Accounts by Branch”. You can do this by double-clicking on the report in the “Solution Explorer” on the right side of your screen. Below is a reminder of what this report looks like:

Share Accounts by Branch

To begin creating the drill down action, right click on the “Branch_Name” cell in the data row (bottom row) of the table. Select “Text Box Properties”.

Branch Name Right Click

Select the “Action” option from the left side of the “Text Box Properties” window. This section of the “Text Box Properties” window allows you to define actions that occur when you click on the values in this cell or column. Choose the radio button next to “Go to Report”.

At this point, we are telling SSRS that we want to go to some report when we click on the values in this column of our report. Next, we need to define for SSRS what report we want to view and pass any necessary parameters to the detail report.

In the dropdown menu beneath “Specify a report”, choose the “Share Account Details by Branch” report that we created earlier in this post. Below that, press the “Add” button. If you remember in our detail report, we had a parameter named “Branch_Name”. We want this parameter to be automatically defined based on whatever branch we click on in our summary report.

In the “Name” dropdown, select “Branch_Name”. This dropdown contains a list of parameters from the “Share Account Details by Branch” report. Next, in the “Value” dropdown, choose “[Branch_Name]”. The “Value” dropdown indicates what value from the current report do we want to pass to the detail report. In this case, we want to pass the name of the branch that we select. The “Text Box Properties” window should look like the image below:

Branch Name Text Box Properties

Press “OK” once you have made the necessary updates to the “Action” section of the “Text Box Properties” window.

Go ahead and preview your report now. Try hovering over one of the branch names in the table; you will notice the cursor changes. Choose one of the branch names and click on its name. If everything was set up correctly, you will be brought the detail report with the “Branch_Name” parameter automatically populated based on whatever branch you initially clicked.

That’s it! That is all that it takes to create a drill down SSRS report. It all comes down to a few simple questions:

  • What report do I start with (our summary report)?
  • What report do I want to drill down to (the detail report)?
  • What do I want to click on to be sent to the detail report?
  • What values do I want to pass from my summary report to parameters in the detail report?

Those basic questions sum up the entire process of creating a drill down report within SSRS.

If you aren’t familiar with SQL Server Reporting Services (SSRS), please take a moment and read this articles on SQL Server Reporting Services (SSRS) from Microsoft. This post will give briefly introduce you to SSRS and describe some of its capabilities. One of the most important benefits of SSRS is that it is included in nearly every SQL Server installation. That means you don’t need to purchase any additional business intelligence or visualization software. If utilized correctly, SSRS can provide a significant cost savings to organizations that don’t necessarily want to spend money on other visualization and dashboard solutions.

Why build an SSRS report?

The vast majority of credit unions, community banks, and regional banks tend to use Microsoft SQL Server for most of their databases. While you will certainly see instances of Oracle or IBM databases, Microsoft is going to be the most prevalent database employed. One of Microsoft SQL Server’s built-in functionalities is SQL Server Report Services or SSRS. SSRS’s browser-based report viewing and management interface has a tremendous amount of reporting features that can be leveraged by your organization.

SSRS enables us to automate the rendering and creation of reports that we frequently generate. We can create a report that will automatically populate a table based on the SQL query we are using behind the scenes to retrieve results and return data. An SSRS report also allows you to specify parameters and variables at runtime to change the results of the report based on drop down lists, date selections, or other data types that we will discussed in later posts.

Think of your lending reports for a moment. Perhaps you have a report for pending loan applications, approved loan applications, declined loan applications, and funded loan applications. The data elements in each of those reports might be identical, yet you are stuck running four separate reports independently. With SSRS, you could create one report and simply have the data be filtered by a “Loan Status” dropdown menu. Report consolidations like this are one of the most tangible benefits of using SSRS.

An SSRS report has many benefits and uses beyond what we briefly introduced. As this series unfolds, we will discuss the various ways you can use SSRS to improve reporting, reduce manual time and effort, and deliver the results faster and more accurately to your report recipients.

Creating Our First Report

Let’s finally start creating our first report. Your organization should be able to set you up with access to SQL or with either SQL Server Data Tools (SSDT) or Business Intelligence Development Studio (BIDS). SSDT or BIDS will be the program we use to create our reports. Whether you use SSDT or BIDS depends on your version of SQL Server (SQL Server 2008R2 and below uses BIDS while newer versions use SSDT). These programs are very similar. I will be using SSDT for these posts, but you should be able to follow along if you will be using BIDS.

First, open SSDT (or BIDS) which can be found in your “Start Menu”. The application should open up and show the “Start Page” on the screen.


With the “Start Page” open, click the “New Project” hyperlink just above the “Recent Projects” header on the left side of the “Start Page”. The “New Project” screen will appear. Looking at the “Installed Templates” section of the left sidebar, ensure that “Business Intelligence” is selected. From there, select “Report Server Project” from the center panel. Towards the bottom of the window, enter the name “Credit Union Reports” in the “Name:” text box.


You can change where this project will be saved if you wish, but I will leave the default location set. Once this has been done, press “OK”.

Creating an SSRS Report Item

When the “New Project” window disappears, right click on the “Reports” folder on the right sidebar, hover over “Add”, and then select “New Item”.


If you select “Add New Report” instead of hovering over “Add”, the “Report Wizard” screen will appear. I prefer to avoid using the wizard (especially when showing new users SSRS report potential) when possible.

When the “Add New Item” window appears, make sure that “Report” is selected in the center panel. In the “Name” text box towards the bottom of the window, we will call the report “Share Accounts by Branch”. The “.rdl” usually will automatically appear – this is the file extension for any SSRS report.


Your screen will now look slightly different than before since a blank report form will appear.


Creating a Data Source and Dataset

When we create any report, there are two basic components we need to consider: the data and the report items. The data consists of the various data sources and datasets that we will use to populate our reports. Report items include the various ways we can visualize our data; these items include tables, matrices, maps, charts, and others. Every report needs data to be visualized and then report items in which the data is rendered.

Going back to our example, we will create a data source. This will tell our dataset (which will we create next) from which SQL Server instance and database will we retrieve data. To do this, right click on the “Data Sources” folder on the left “Report Data” sidebar, and then select “Add Data Source”. The “Data Source Properties” window will appear on your screen.


Change the name of the data source, in the “Name” text box, to “CU Database”. Next, press “Edit” to the right of the “Connection String” text box. When you do this, the “Connection Properties” window will appear on your screen. In this screen, we will define the SQL Server Instance in the “Server Name” drop down along with the database to which will be connecting. In the “Server Name” box, type “localhost”. You can also type a period in this box. This will tell SSDT to find the SQL Server instance running on the machine that you are currently on. If your SQL Server Instance is on another server on the network, you will need to get the server name from your IT or networks team.

Once you enter “localhost” or “.” in the “Server Name” box, click the arrow in the “Select or enter a database name” box. This dropdown box will contain a list of databases on the server to which you have been granted access. I will be connecting to the database named “CreditUnion”.


Once you have selected the database, press “OK”.

The “Data Source Properties” window will now look a little different since the connection properties have been filled in.


Press “OK” to create the data source.

Now that we have created a data source, we need to create a dataset. A dataset is where we define our SQL code that will retrieve our data from the data source we just defined. To create the dataset, right click on the “Datasets” folder and then select “Add Dataset”. The “Dataset Properties” window should now appear on your screen.


In the name text box, change the name of this dataset to “ShareAccounts_by_Branch”. Below, select the “Use a dataset embedded in my report” radio button (as opposed to the default “use a shared dataset”). In the “Data Source” dropdown box that appear, select “CU Database” which is the name of the data source we just finished creating.

In the “Query” text box, enter the SQL code that will define the data that you retrieve. My query will return a row for each branch and three columns: the branch name, the number of share accounts opened at that branch, and the current balance of all shares that were opened at that branch. As mentioned in the credit union business intelligence series introduction, this data has been generated randomly by a script I created. There is NO real member data here and all accounts, balances, social security numbers, names, address, and all other related data attributes have been randomly generated via SQL or through random name generators online.


With the query defined, press “OK” to save the dataset.

If you look at the “Report Data” panel on the left side of the screen, you will notice that the data source and dataset we have created now populate their respective folders. With the data source and dataset now defined, we need to create a report item to visualize the data we wish to retrieve.

Adding a Table to our SSRS Report

Report items can be added a couple different ways. First, you can hover over the “Toolbox” tab on the left sidebar and then drag an item from the toolbar onto the blank row. The other method, which I prefer, is to right click on white space in the report, hover over “Insert”, and choose the item I wish to add to my report. For this example, we are simply going to add a table to our report. So, using one of the two methods described, add a blank table to the report.


Let’s move the newly created table to the upper-left corner of the report. To select the table either click on one of the outer edges of the table, or select a cell within the table and then press the small box in the upper left corner that appears (see image below):


Once you have selected the table, click on the move table icon Move_Table_Icon and drag the table to the upper left corner of the report.


Now that we have the table created, we need to tell the table what data to display when we run the report. To do this, we need to specify which column from our dataset will appear in what column from the table. Simply move your cursor over one of the cells in the “Data” row of the table until the small table icon appears on the top right corner of the cell. Table_Icon.

Click on the small table icon once it appears. A list of all columns from the dataset will appear in a menu. Choose what dataset column you would like to appear in the table column that you initially hovered over. In this example, I will put the “Branch_Name” column in the first name column, the “Count_of_Shares” column in the second column, and the “Current_Share_Balance” column in the third column.


You will notice a few things immediately. First, the column header is automatically added to the table. Second, SSDT and BIDS will automatically interpret an underscore as a space and adjust the name of the column accordingly. Before we actually run this report for the first time, let’s adjust the size of columns and make the column headers bold. To adjust the column width, hover your cursor over the edge of the grey boxes that appear above column header row until the resize cursor appears. Drag the columns to the appropriate width. To make the column headers bold, click on the top left column header cell (“Branch Name” in my example), hold the shift key, and then click on the top right column head cell (“Current Share Balance”).


Once highlighted, press the bold symbol in the top toolbar section of SSDT or BIDS that typically is to the right of the font selection dropdown menu.


At this point, we can run the report to see what it looks like. Just above the report form are two tabs: “Design” and “Preview”. We can edit the report when the “Design” tab is selected, and we can preview what the report will look like by selecting the “Preview” tab. Select the “Preview” tab now.


This is our first preview of the report! As you can see, the data from our dataset has been placed in the appropriate columns of our table. We will do one last formatting edit before we wrap up this post. If you look at the “Count of Shares” and “Current Share Balance” columns, neither of them are formatted properly with commas and/or dollar signs where necessary.

To leave the report preview, click the “Design” tab.

To make commas appear properly in the “Count of Shares” column, right click on the column in the “Data” row (the bottom row), and then select “Text Box Properties”.


When the “Text Box Properties” window appears, select the “Number” option on the left sidebar. In the “Category” list that appears in the main part of the window, choose “Number” instead of “Default”. When the formatting options appear, click the checkbox that says “Use 1000 separator”.


Press “OK” once this has been completed.

To format the “Current Share Balance” column, we need to follow a similar process. Right click in the text box, select “Text Box Properties” and then choose the “Number” option from the left toolbar of the “Text Box Properties” window that appears. This time, choose “Currency” instead of “Number”. Be sure to click in the checkbox next to “Use 1000 separator”. Press “OK” once completed.


Select the “Preview” tab just above the report editor to view the formatting changes we have made.


Notice how the numbers are properly formatted as we defined them in the “text box properties” for each column!

This concludes our first credit union business intelligence series post! This lesson has introduced you to SSRS and taught you how to create a very basic SSRS report. As the lessons develop over the coming weeks, our SSRS reports will become more complicated and include features like parameters, dynamic filtering, and drill-downs.

Your comments and questions are always appreciated. If you have any questions at all, please email [email protected], and we will answer your question as soon as we possible can!