Microsoft SQL Server Reporting Services (SSRS)

Building reports in SSRS: Designing reports, implementing a report layout, and implementing interactivity in a report
4.4 (30 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
275 students enrolled
Instructed by Phillip Burton IT & Software / Other
$19
$20
5% off
Take This Course
  • Lectures 37
  • Length 3.5 hours
  • Skill Level Beginner Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 8/2015 English

Course Description

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.

What are the 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.

What am I going to get from this course?

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

What is the target audience?

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

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: Introduction, and download SSRS
02:18

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

03:08

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

11:16

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.

14:20

We'll locate and download SSRS, and installing it onto your computer - for free.

Section 2: Preparing to create reports
04:03

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

10:18

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.

02:09

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.

09:48

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.

Section 3: Creating our first report
08:03

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

06:04

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

08:40

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

08:53

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.

04:44

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

10:02

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

04:24

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

05:47

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.

Section 4: More advanced techniques
04:16

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

02:10

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

08:03

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.

02:49

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

05:15

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

02:30

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.

02:23

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.

04:07

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.

05:54

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

08:04

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.

06:54

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

05:41

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

05:39

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.

Section 5: Graphical reports
04:25

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

06:42

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

11:33

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

02:55

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.

02:34

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.

07:03

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

Section 6: Conclusion
05:55

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

01:36

We'll look back at what we have learned.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Phillip Burton, SQLIntro.com

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.

Ready to start learning?
Take This Course