4.37 out of 5
4.37
800 reviews on Udemy

Microsoft SQL Server Reporting Services (SSRS)

Build reports in SQL Server Reporting Services (SSRS): Implement report layouts, add interactivity. Exam 70-466
Instructor:
Phillip Burton
5,105 students enrolled
English [Auto-generated] More
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.

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

Introduction, and download SSRS

1
Introduction

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

2
Introduction to Udemy
3
Breaking news - 12 April 2019
4
Objectives

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

5
Do you need to install SSRS?

Preparing to create reports

1
Opening SSRS

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

2
Downloading and Installing AdventureWorks

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.

3
Exploring data source

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.

4
Creating shared dataset

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 our first report

1
Creating a report using Report Wizard

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

2
Troubleshooting your display

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.

3
Practice Activity Number 1

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

4
Practice Activity Number 1 - The Solution

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

5
Recreating our report, not using the Report Wizard

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

6
Properties pane

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

7
Practice Activity Number 2

Let's create another report.

8
Practice Activity Number 2 - The Solution

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

9
Standard Number Formatting

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.

10
Custom Number Formatting

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

11
Date Formatting

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

12
Sort and Group

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

13
Visibility and Table Headers

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.

14
Practice Activity Number 3

Let's practice formatting, sorting and grouping.

15
Practice Activity Number 3 - The Solution

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

More advanced techniques

1
Creating a Categorised report

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

2
Creating hyperlinks

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

3
Adding a parameter

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.

4
Practice Activity Number 4

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

5
Practice Activity Number 4 - The Solution

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

6
Adding a subreport

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

7
Page Headers and Footers

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

8
Adding drop-down list to parameters

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.

9
Allow multiple values to select in parameters

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.

10
Practice Activity Number 5

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

11
Practice Activity Number 5 - Solution

Here's my answer to this Practice Activity.

12
Bonus: Allowing NULLs in parameters

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.

13
Conditional Formatting

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

14
Indicators

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.

15
Gauges

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

16
Adding totals

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

17
Interactive Sorting and Document Map

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.

18
Practice Activity Number 6

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

19
Practice Activity Number 6 - Solution

How did you do? Here's my answer.

Graphical reports

1
Creating a pie chart

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

2
Expanding the pie chart

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

3
Creating a bar chart

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

4
Expanding the chart

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.

5
Enter the Matrix

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.

6
Mapping data

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

Object Properties

1
Creating a new Employee Report
2
Alignment Properties
3
Border and Fill Properties
4
Other Text Box Properties
5
Group and Tablix Properties
6
Chart Properties

Functions

1
Program Flow Functions (IIF, CHOOSE, SWITCH)
2
Aggregate functions
3
Math functions
4
Text functions

Conclusion

1
Bonus Lecture

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

2
Recap

We'll look back at what we have learned.

Bonus - Installing SQL Server 2017 Developer Edition

1
Downloading SQL Server back engine - the Developer edition - for free!

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.

2
Installing SQL Server back engine

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

3
Installing SQL Server Front Engine

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.

4
Installing SSRS
5
Installing Visual Studio and SSDT (SQL Server Data Tools)
6
Is your VIsual Basic a trial version? No!
7
The next step

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

8
Different Types of SSRS, including Report Builder's integration with SharePoint.

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.

9
Installing Visual Studio 2019
10
Installing SSRS in Visual Studio 2019
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4.4
4.4 out of 5
800 Ratings

Detailed Rating

Stars 5
325
Stars 4
314
Stars 3
123
Stars 2
23
Stars 1
15
ac7fe4a9f85b8d7da4189d20643ebc2c
30-Day Money-Back Guarantee

Includes

6 hours on-demand video
9 articles
Full lifetime access
Access on mobile and TV
Certificate of Completion