Microsoft SQL Server Reporting Services (SSRS)
4.5 (1,640 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,750 students enrolled

Microsoft SQL Server Reporting Services (SSRS)

Build reports in SQL Server Reporting Services (SSRS): Implement report layouts, add interactivity. Exam 70-466
Bestseller
4.4 (1,640 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
9,750 students enrolled
Created by Phillip Burton
Last updated 6/2020
English
English [Auto], Italian [Auto]
Current price: $69.99 Original price: $99.99 Discount: 30% off
5 hours left at this price!
30-Day Money-Back Guarantee
This course includes
  • 6 hours on-demand video
  • 12 articles
  • 6 downloadable resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Create reports in SQL Server Reporting Services (SSRS).
  • We will develop your skills, bit by bit.
  • You can also download a free version of SQL Server which will allow you to practice creating reports on your own.
  • We will design a report, implementing report layouts and add interactivity into your reports.
Requirements
  • You don't need any prior knowledge prior to taking this course.
  • A couple of the bonus lectures will require some knowledge of T-SQL, but these lectures are optional.
  • You don't need any form of SQL Server to already be installed on your computer; I will show you have to download it and install it - for free.
  • It is not necessary to have SharePoint on your computer.
  • If you want to know enough T-SQL to assist you with your SSRS work, please have a look at the first two sessions of my 70-461 courses.
Description

Reviews:

"Brilliant! This tutorial will definitely save you hours of figuring out things yourself." -- Koos ten Bras

"Excellent course, well presented and clear examples." - Peter Cawthorne

This course is all about creating reports in SQL Server Reporting Services (SSRS). No prior knowledge is needed, but some knowledge of T-SQL would be useful.

We will download a version of SQL Server which includes SSRS - for free. We'll also download a database called AdventureWorks, which we will use in our reports.

We'll create various reports, developing our skills bit by bit. As part of designing a report, we will:

  • Select report components (matrix, table, chart and other data visualisation component),
  • Identify the data source and parameters,
  • Design a grouping structure,
  • Create drilldown reports and drillthrough reports.

As part of implementing a report layout, we will:

  • Find out how to format, including formatting colours, dates and numbers;
  • Configure the page;
  • Implement headers and footers;
  • Implement matrices, tables, chart, images, indicators, maps and groupings in reports;
  • Create a range of reports using different data regions;
  • Define custom fields;
  • Implement global collections;
  • Define expressions; and
  • Implement data visualisation components.

As part of implementing interactivity in a report, we will:

  • Create drilldown reports;
  • Create drillthrough reports;
  • Add interactive sorting;
  • Investigate parameters, including multi-value parameters;
  • Create dynamic reports in SSRS using parameters;
  • Implement show/hide property;
  • Add actions (jump to report and URL);
  • Add Fixed headers and a document map.

By the end of this course, you should be confident in creating your own reports in SSRS.

It will assist with the "Build a report with SQL Server Reporting Services (SSRS)" section of Microsoft exam 70-766 "Implementing Data Models and Reports with Microsoft SQL Server".

Who this course is for:
  • This course is for you if you want to know how to create reports in SSRS.
  • No prior experience with T-SQL is required, although the more knowledge you have it, the better.
  • Having SharePoint on your computer is not necessary; we will use the stand-alone version of SQL Server. However, I will show the differences and similarities between Report Builder (SharePoint) and SSDT (the stand-alone version).
  • In this course we will cover several aspects which are tested in Microsoft's 70-466 exam "Implementing Data Models and Reports with Microsoft SQL Server".
  • This course is not for you if you only want to know about managing your existing SSRS reports in SharePoint.
Course content
Expand all 75 lectures 06:08:39
+ Introduction, and download SSRS
6 lectures 09:54

Welcome to the course. I'll briefly describe what you will learn in this course.

Preview 02:18
Welcome to Udemy
00:35
Introduction to Udemy
02:00
Should I install Visual Studio? If so, what version?
01:25

We'll have a look at what we are going to cover in this course.

Preview 03:08
Do you need to install SSRS?
00:28
+ Preparing to create reports
5 lectures 27:38

We'll find out how we can open SSRS - whether it be called BIDS, SSDT or Visual Studio - and create a new project.

Opening SSRS
04:03
Where is AdventureWorks?
00:11

We'll locate a database called AdventureWorks, download it, and install into SQL Server. We'll then create a data source based on it, and find that sometimes SSRS cannot see our SQL Server database, and how to overcome it.

Downloading and Installing AdventureWorks
08:47

We'll go back into the data source that we just created, to see additional sources of data we can use, where we can add credentials, and we'll rename our data source.

Exploring data source
02:09

Without writing any SQL, we'll use the Query Assistant to create a dataset that we'll focus in on the data we will use in our first report.

Creating shared dataset
12:28
+ Creating our first report
16 lectures 01:14:53
Problems with Report Wizard?
00:13

We'll create our very first report, using the Report Wizard. Note that we can't use shared datasets directly in the Report Wizard.

Creating a report using Report Wizard
08:03

I had a problem when setting up a report on a different computer - let's see what the problem is and how to resolve it.

Troubleshooting your display
01:05

Let's see how much you remember. It's your turn to create a report.

Practice Activity Number 1
00:24

How did you get on? Here's my answer to this Practice Activity.

Practice Activity Number 1 - The Solution
04:14

We'll start from scratch recreating our report, but this time not using the Report Wizard. This gives us more flexibility.

Recreating our report, not using the Report Wizard
06:04

We'll find out where the Properties pane is hiding, and I'll introduce how important it is to SSRS.

Preview 08:40

Let's create another report.

Practice Activity Number 2
00:32

How did you get on? Here's my answer to this Practice Activity.

Practice Activity Number 2 - The Solution
05:35

Unfortunately the Help system is fairly poor about number and date formatting. We'll learn some of the standard number formatting, using the spreadsheet which is in the Resources to this lecture.

Standard Number Formatting
08:53

Sometimes you just want more control of your formatting. We'll have a look at cystom number formatting.

Custom Number Formatting
04:44

Dates can also be formatted in a variety of ways. We'll have a look at standard and custom date formatting.

Date Formatting
10:02

We'll override the sort from the dataset, and we'll also group similar records together and add a group header.

Sort and Group
04:24

We'll hide the detail from our report, so we start off with the summary and show the detail whenever we want it, and we'll keep the table headers on subsequent pages.

Visibility and Table Headers
05:47

Let's practice formatting, sorting and grouping.

Practice Activity Number 3
00:22

How did you get on? Here's my answer to this Practice Activity.

Practice Activity Number 3 - The Solution
05:49
+ More advanced techniques
19 lectures 01:26:42

We'll use a more complex query to create a report of the categories and subcategories.

Creating a Categorised report
04:16

We'll create a hyperlink from our new report to our old report, and find that it opens the entirety of the old report.

Creating hyperlinks
02:10

We'll add a parameter to our Product report to create a smaller version of it, and we'll use that in the Categorised report to open the relevant entries in the smaller Product report.

Adding a parameter
12:17

Let's see if you can create two reports, linked together by a parameter.

Practice Activity Number 4
00:42

How did you get on? Here's my answer to this Practice Activity.

Practice Activity Number 4 - The Solution
07:52

We'll introduce the Product report as a subreport in the Categorised report.

Adding a subreport
02:49

We'll create a page header with a Text Box, Image and rectangle, and a page footer with Page Number and Total Pages.

Page Headers and Footers
05:15

At the moment we have to manually enter a number as a parameters. Let's convert this to a drop-down text list for a better user experience.

Preview 02:30

It might not be enough to select a single value for a parameters. Let's allow multiple values to be selected, which means updating our SQL query.

Allow multiple values to select in parameters
02:23

Let's have a look at what you have learned.

Practice Activity Number 5
00:23

Here's my answer to this Practice Activity.

Practice Activity Number 5 - Solution
05:34

Our drop-down list does not allow NULLs to be selected. Let's alter the drop-down list to add a NULL category. Note: This does require some knowledge of T-SQL.

Bonus: Allowing NULLs in parameters
04:07

We can alter the fore- and back-colors depending on the data, just like in Excel. Let's find out how.

Conditional Formatting
05:54

We'll have a look at various ways to indicate that a number is in a certain range. We'll look at text formulas, indicators, and bars.

Indicators
08:04

We'll look at one of the most graphical indications of a number, which also shows the overall context - the gauge.

Gauges
06:54

If you summarise data, you will probably want to add totals. Let's find out how.

Adding totals
05:41

We'll allow the end user to change the sorting in a report, and create a document map so that we can click on various headings to get into that part of the report.

Preview 05:39

Let's develop our report from our first Practice Activity.

Practice Activity Number 6
00:13

How did you do? Here's my answer.

Practice Activity Number 6 - Solution
03:59
+ Graphical reports
6 lectures 35:12

We'll create a new dataset, and create a pie chart based on it.

Creating a pie chart
04:25

We'll look at the various options which we can use to expand and customise our pie chart.

Expanding the pie chart
06:42

We'll create a new query, with dates and locations, and create a bar chart by location.

Creating a bar chart
11:33

We'll create a calculated field which shows the year that the address was created, and convert the chart to a stacked bar chart to separate the bar by year.

Expanding the chart
02:55

The bar chart is good, but what if we need some sort of numerical legend, showing the number of addresses per country per year. This calls for a Matrix, which is the SSRS version of a PivotTable.

Preview 02:34

We'll find out how to plot information onto a map - in this case, of the United States.

Mapping data
07:03
+ Object Properties
6 lectures 41:13
Creating a new Employee Report
04:32
Alignment Properties
06:07
Border and Fill Properties
06:11
Other Text Box Properties
10:11
Group and Tablix Properties
08:36
Chart Properties
05:36
+ Functions
4 lectures 32:32
Program Flow Functions (IIF, CHOOSE, SWITCH)
08:45
Aggregate functions
09:53
Math functions
05:28
Text functions
08:26
+ Conclusion
2 lectures 06:57

Well done for completing this course. Let me give you a little "thank you".

Bonus Lecture
05:21

We'll look back at what we have learned.

Recap
01:36
+ Bonus - Installing SQL Server 2017 Developer Edition
11 lectures 53:36

It used to be that we had to install a cut-down version of SQL Server. Instead, let's now install a version with the full functionality of the Enterprise edition - for personal use only, though.

Downloading SQL Server back engine - the Developer edition - for free!
06:09

Now let's go through the process of installing SQL Server. I'll also go through the various editions of SQL Server (e;g. 2008, 2012).

Installing SQL Server back engine
09:45

Now the back engine has been installed, it would be good to install SSMS (SQL Server Management Studio). We also install Visual Studio and SSDL (SQL Server Data Tools) to use SSRS. It takes around 30 minutes, but here's the edited version.

Installing SQL Server Front Engine
05:16
Installing Visual Studio 2019
04:52
Installing SSRS in Visual Studio 2019
03:00
Is your Visual Studio a trial version? No!
01:36

You've now installed SQL Server 2017 Developer Edition - what's next?

The next step
00:06

There are various forms of user interfaces for SSRS, including Visual Studio, SQL Server Data Tools, and BIDS. In this lecture, we will compare these with a more corporate edition using SharePoint called Report Builder. We'll look at the differences between them and the similarities.

Preview 11:16
Installing SSRS in Visual Studio 2017
03:12
Installing Visual Studio and SSDT (SQL Server Data Tools)
06:59
Is your VIsual Studio a trial version? No!
01:25