Building Your First SSRS Report

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

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 brewster@knowlton-group.com, and we will answer your question as soon as we possible can!