SSRS stands for Sql Server Reporting Service. This is Microsoft’s BI reporting tool integrated into their Sql Server platform. SSRS allows you to create, deploy, and manage reports from a server platform.
SSRS comes as part of the SQL Server suite. It is not available as part of Express, but if you buy the developers edition, you will get SSRS (as well as SSIS and SSAS). You may need to download and install it separately. You’ll find it under the title, SQL Server Data Tools (SSDT).
SSDT download link
In this tutorial, I will walk you through creating your first SSRS report. In this example, I created two SQL Server tables from the following Excel files:
JobDataSet
JobDesc
If you aren’t sure how to upload an Excel file to SQL Server, you can go to my tutorial on how to do it. The SQL Server upload tutorial was actually created to support the SSRS lessons.
SQL Server: Importing Excel File to SQL Server
To create a new SSRS project, open Visual Studios (the platform SSDT runs on) and go to File->New->Project
Select Reporting Services -> Report Server Project
Name your project. I typically leave Create directory for solution checked.
Your new “solution” will open up. I still haven’t really figured out why MS changes the name from Project when creating and opening it to Solution once you are working on it. I am sure someone, somewhere had a reason for it. That person is probably retired now and won’t return any email requests as to why he decided on the wording choice. We’ll just have to chalk it up to another one of life’s mysteries. Like why is Regis Philbin famous?
But I digress…
Now our “solution” is open, we will see 3 sub folders. Shared Data Sources, Shared Datasets and Reports
To create a new report, right click on Reports -> Add -> New Item. Don’t click Add New Report unless you want to meet the world’s most unhelpful Wizard. I call him Gandalf the Drunk…
After clicking add new item, click Report and name your report.
Now your new report will open up.
Data Source
So, before we can actually report on anything, we are going to need data. And in order to get data, we are going to need a Data Source. Now you will notice you have Shared Data Sources on the right and Data Sources on the left. Shared Data Sources, once established, can be used in all reports you create. If you create an “embedded” data source in the report, you will only be able to use it in that report.
For this example, we will make a shared data source.
Go to the right under Solution Explorer and right click Shared Data Sources. This time you can click Add New Data Source.
This wizard is just Gandolf the Tipsy. While I harbor a general dislike for most wizards, this one isn’t completely useless at least.
First name your data set something you will remember later.
Select Type ->Microsoft SQL Server
Click Edit
Copy and paste your server name in the 2nd box. In this example, my SQL Server is locally installed on my computer, so I just used localhost as my server name. Next select the Database you want to work with from the drop down. I created a database call SSRSTraining for this example
Hit Test Connection, you should get a success message.
Click Okay, you’ll new see your Data Source in the Solution Explorer
Now go to the left and right click on Data Source for your report. Select Add New Data Source
Name your data source and click on the Use shared data source reference radio button.
Pick you data source from the drop down. There should only be one to choose from
Datasets:
Now click okay, go back to the left and right click on Dataset.
Select New Data Set,
Name the Data Set
Select Use a dataset embedded in my report. This is generally how I do things, as Data Sources are usually reusable, Datasets are more designed for specific reports, so I leave them embedded.
Select your Data source from the drop down
For Query type we are using Text in this example
I am using a simple select all statement from the dbo.JobDataSet table I created
If you click on fields in the upper right, you’ll now see the columns that will be feeding in from the query. You can rename the columns if you wish
For now, let’s just click Okay, now you will see your data set expanded on the left side of your screen.
Now to the far left, you should see the word Toolbox, click on that. This is a list of the tools we can work with within SSRS. Let’s start with a table
Click on the table and drag it into the design window in the middle of the screen
Now you can simply drag and drop columns from your dataset into your new table.
By default, a table comes with 3 columns. You can add columns to this table by dragging a field over to the end of the table (note you will see a blue bar indicator letting you know your mouse is in the right spot. The column will populate to the right of the blue bar).
This will add a new column to the end of your table. You can also use this method to insert a column in between existing table columns
Now click on Preview button above your table
When you do, you will get to see your table complete with data. Notice how the job column is too small for the job description.
Step 5: Formatting
To fix the job column, let’s go back to our Design screen. Do this by clicking on the Design tab in the upper left.
Now hover your mouse over the table until you get the double arrow icon seen below
Once you have that, simply click and drag the column over to make it wider
Since we are in the design window anyway, let us do a little more formatting. Click on the gray box to the left the header row to highlight the entire row. Now we can do things like Bold the font or change the background color
Go back to the preview window to check out your results.
There you have your very first SSRS Report from top to bottom.
