Transitioning to Automated Reporting: The Power of Microsoft's SQL & Visual Studio Ecosystem

A while back, a Linkedin connection asked me if I had experience helping a team / company transition from manual SQL / Excel reporting to automated processes "with dashboards as the end-product (or even just the preexisting Excel reports as output)". He went on to explain that he was interviewing for a role, where the team was utilizing a very labor intensive process to build reports. To be specific, they would manually run a set of SQL queries, then dump the results into Excel and work with those outputs to do the final reporting. As luck would have it, I encountered a similar situation in a recent role, in which I was able to use Microsoft SQL Server Reporting Services (SSRS) to address the issues he mentioned, so I shared some thoughts.

Since then, I've noticed a number of data analyst and / or data science roles, including SSRS as a requirement. In fact, a basic search using "SSRS jobs" yielded 7k+ results on Linkedin, and a national average salary of $94K+ on ZipRecruiter. Therefore, I thought it might be helpful to provide some insights from my experience and a walkthrough to produce a basic report. This is not meant to be a comprehensive tutorial on SSIS, SSMS and SSRS; rather, it is my hope that it will serve as potential guide / inspiration for those looking to migrate away from Excel Reports to more polished deliverables or dashboards but can't afford pricier options like Tableau. This article assumes you already have SQL Server installed or have access to it and are comfortable with SQL. If you don't have SQL installed, you can get a free copy using the following link:

You can also see Microsoft's complete SQL documentation here:

Note: Microsoft SQL Server Developer, Standard, and Enterprise editions all include SSRS as an install option. The free SQL Server Express includes a limited version. I am using the SQL Server Express edition and Visual Studio Community 2017.

Getting Started

We're going to be using the well known "SuperStore" dataset. If you'd like to follow along, you can access the data using the following link:

Note: The data I am working with has been modified to have different column names, etc.

The first thing we need to do is get the data into SQL Server. The process of extracting, trasforming and loading data is commonly referred to as ETL. We can do this with SSIS or SQL Server Integration Services.

1. Open Visual Studio - Click on "File" on the main menu and choose "New Project". You should see something like this:

2. Choose "Integration Services Project" -- You will see a list of options down the left under "SSIS Toolbox". Drag the "Data Flow Task" into the project pane as shown here:

3. "Double-click" on the "Data Flow Task" component. It should take you to the "Data Flow" pane as illustrated here:

4. Now, drag in "Source Assistant" from the "SSIS Toolbox" on the left. A dialog box like the one below should automatically appear.

5. Since the SuperStore file is in a .txt format, we need to choose "Flat File" from the "source type" list and "New" to the right under "Select connection managers". This will bring up a "Flat File Connection Manager Editor" with an empty "File name:" box and "Browse" to the right. Click on "Browse" and look for the file on your system.

6. Next, you'll want to go through a series of steps to validate and configure your columns. Make sure the columns appear the way you want them to (for sake of illustration, I'm using the original file and its column names.). To do this, refer to the list of options on the left (e.g., "General", "Columns", "Advanced", etc.).

As a best practice, I click the "Advanced" option and change the column width to "1000" for all the columns. This helps to avoid any issues with differing field lengths. After you're done, click on "OK". You should end up back at the "Data Flow" pane and see the "Flat File Source" component.

7. Once you've finished configuring the source, you need to drag "Data Conversion" from under the "Common" list of options on the left, within your "SSIS Toolbox". Next, drag the blue arrow at the bottom of the "Flat File Source" component, so it connects to the "Data Conversion" component. A "Destination Assistant" dialog box should automatically appear (see second image below); however, if it doesn't, click on "Destination Assistant" on the left, within your "SSIS Toolbox", and drag it onto the "Data Flow" pane. This should open the dialog box.

If this is the first time you've done this, you will see "New" under "Select connection managers"; in which case you will need to click on "New" and choose your SQL Server from the drop down box and select the database name you want to connect to in SQL Server. I've already done this, so I see my instance of SQL Server listed and click on it.

8. After you've set up the "Destination" connection, you should end up back at the "Data Flow" pane.

Right click on the "Destination" component and click on "New" to create a new table and choose a name (In the image here I have highlighted "SuperStoreUS"). Continue to "Mappings" when you are done to confirm original file column fields match destination fields.

9. When you are done and all components are connected / configured, click "Start" at the top (see green arrow pointing to the right before "Start"), and watch the magic happen.

Once the ETL process is done, you should see green checkmarks out to the right of each component with the number of rows loaded to SQL Server.

Important Note:  There is much much more to the ETL process, including ways to set up filters and other options to assist with data processing and cleaning, prior to importing the data into SQL Server.  Likewise, there are other ways to get data into SQL Server, but I have chosen to show this approach to give you some exposure to the basics of SSIS.

10. To see the table in SQL Server, open up SQL Server and right click on your tables under the appropriate database and choose "Refresh".

I can see that "SuperStoreUS" appears, and I can do a simple query looking at a sample of the dataset to make sure everything is in order, so we're ready to move to SSMS to create a SSRS "procedure" that will provide the data for our report in Visual Studio.

As mentioned previously, I went through some additional steps to modify my column names (e.g., data wrangling), so they are easier to work with.

Pulling the Data and Automating the Query

Now that we have our table in SQL Server Management Studio, we can create a query or "procedure" that will pull in the data we need for our report. I generally spend some time refining my code and making it more efficient before I actually create a procedure. When I'm ready, it just takes two lines of code to convert it to a procedure. In this case, I've decided I want to keep it simple and focus on SuperStore's top sales states and customers. Notice the two lines at the top. When I'm ready, I just hit "Execute" to create the procedure.

To view or modify, I can just go to my "Stored Procedures" under "Programmability" in my database, and hit refresh. The actual procedure I created is called "pr_super_store_customer_rankings". As a best practice, I usually start my procedure name with "pr", so I know it's a procedure.

Here is what the procedure looks like after it's created (I can right click on the name of the procedure above and choose "Modify", if I need to make any changes later. Be sure to keep a back-up of the original somewhere).

And that's really it. Of course, determining what data you will need and getting it into the write code format can take time, depending on the complexity of what you're trying to do, but I hope this demonstrates the work associated with creating the procedure is very straightforward. Now we can move on to creating our report.

Building the Report

So, you've loaded the data into SQL Server and created a procedure that will automatically execute a query to pull the data you need for your report, but you need to create the project and make sure Visual Studio can connect to the procedure to run the report when needed. In the past, you may have just copied / pasted the results of a query from SQL, into Excel, and then created the graphs and other visuals or tables needed there, to create a report. The problem with this is every time you want to send out the report, you need to go into Excel, potentially update data, make sure it's formatted appropriately for printing and then send it out to the intended recipients.

What if you could just make a few tweaks to the procedure and have a subscription set up in Visual Studio that automatically sends out the updated report, in whatever format you want, (Excel, PPT, PDF), on a regular basis? Disclaimer: I will often do a mock-up of a report in Excel to get it looking the way I want before spending the time to set it up in Visual Studio, because Visual Studio can be more complicated to work with than Excel [when initially setting up a report]. After the mock-up is blessed by key stakeholder(s), I move on to the final stage, which is actually building the report in Visual Studio. Assuming this is your first time to set up Visual Studio, I will walk through the steps to make sure your SQL Server instance is connected and you have configured it to use your procedure.

1. Open up Visual Studio and go to "File" and choose "Project".

2. Look for "Reporting Services" options under your "Installed" list of options at the left and choose "Report Server Project" in the middle of the page. You can also name your project at the bottom of the page.

After you click "OK", you should see something like this. On the right hand side of the Project is the "Solution Explorer". You should see your named project followed by three folders: "Shared Data Sources", "Shared Datasets", and "Reports". We will set up each of these to connect to the server and procedure, in addition to customizing the report name.

3. Right click on "Shared Data Sources" and choose the "Add New Data Source" option. You will see a place for the name of your data source, as well as the type of source. We are connecting to SQL Server, so make sure you choose that for "type". Next, click on "Build" out to the right, underneath, and fill in your "Server name" and the "database name" (Hint: Use the dropdowns, but if you don't see what you need, you can type in the name of your server and the database). You can leave "Credentials" set on "integrated security" or the default. Before moving on, it's a good idea to click on "Test Connection" at the bottom, too.

4. Set up "Shared Datasets" by right clicking on "Shared Datasets" and choosing "Add New Dataset". Again, you can name the soure, and then choose your procedure from the dropdown. Make sure "Stored Procedure" is chosen.

5. Now we need to create the report, itself. Right click on "Reports" and choose "Add", then "New Item" and "Report". Be sure to name the report at the bottom.

Now you should see a report pane appear in the middle of your screen, as shown here:

6. Almost done with setting up our report project. The next thing we need to do is configure the left hand side of the project pane. It should look something like this. If it doesn't, click somewhere in the report pane and choose "View" from the main menu and "Report Data" at the very bottom of the "View" menu. Right click on "Datasets" at the left, and confirm the fields from your procedure are showing up under "Fields". Click "OK".

7. Now that we have everything set up, we need to make sure we can also see the "Toolbox". Again, use the "View" option in the main menu if you don't see it in the left hand portion of the project pane. Here, you can toggle between "Report Data" and your "Toolbox" to leverage the different types of visualization tools available.

Important:  You need to be in "Design" mode to do your work. To see the results, you will need to click on "Preview" at the top of the report pane.

For example, if we want to add a chart to the report pane, we simply click on the "chart" icon and drag it over to the report pane. A number of chart choices will appear. Choose the one you want, and begin customizing it to your needs by right clicking on the different elements. In order to add data, you will need to fill out the "Chart Data" "Values", "Category Groups" and "Series Groups", if / when applicable. The second image below shows what I used to create a bar chart showing "SuperStore Top Sales States & Customers".

If I want to add or change the data, click on the existing data point or "+". A dialog box will appear that gives you a list of fields to choose from, as well as different operators and common functions.

Most of your time will be spent formatting in Visual Studio. It can be a little "cluegy" at first, but after you play around with it, you will get the hang of it, and there are a lot of good tutorials and documentation online to help you out.

Let's say I want to change the color of a bar, I can right click on it, and choose "Series Properties" or refer to the "Properties" pane in the lower right hand corner. Hint: Every element in Visual Studio has properties associated with it. You will want to spend some time familiarizing yourself with the different settings available.

Ok, we could spend hours and hours talking about Visual Studio and all of the different options you have available to build reports and do visualization(s), but I just wanted to give you a taste. Here is a basic chart and table I created, so you can get a feel for what's possible with just a small bit of data:

In a real-life scenario, I would probably continue building out the report / dashboard and create a nice header, footer and page number, etc. Further, if I'm going to want the report to automatically export to Excel, I will want to name the pages / sheets, etc. Again, we've just skimmed the surface of what's possible with SSRS / Visual Studio.

Bringing it all together

The process I've just gone through with you is one I have used to create several reports for my company. If we were in a true production environment, we would have additional steps to configure the project properties to "deploy" the project / report(s) to a report server. Unfortunately I am not able to show you that step here, as that is beyond the scope of this article and would require me to show you the configuration of my workplace report server, but this is something you can easily do. Again, documentation is available on line to walk you through it. Here is a link to get you started:

Once you are fully up and running with SSRS / Visual Studio and able to create a report in this manner, you will be able to automate delivery, no longer making it necessary to go into SQL to manually run queries and copy / paste results to Excel. Granted, it takes some time to learn, but the time it saves in the end, and the convenience afforded to you by subscriptions is a game changer. Executives will also love the professional look of the reports, when compared to Excel. For additional information on subscriptions, check out this link:

I hope this article empowers you to explore Microsoft's SQL and Visual Studio capabilities in more detail.