SSRS in Real World: Key Skills to Master SSRS Reporting at Work | Andrea Sabatino | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

SSRS in Real World: Key Skills to Master SSRS Reporting at Work

teacher avatar Andrea Sabatino, Solution Architect and Software Engineer

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

62 Lessons (2h 19m)
    • 1. Welcome!

    • 2. Course Introduction

    • 3. Install - Intro

    • 4. Install - SQL Server Express

    • 5. Install - SSMS (Management Studio)

    • 6. Install - Adventure Works

    • 7. Install - Visual Studio 2019

    • 8. Install - SSRS 2017

    • 9. Install - SSRS Configuration

    • 10. Install - Key Points

    • 11. IDE - Intro

    • 12. IDE - Configuration

    • 13. IDE - Quick Overview

    • 14. IDE - Key Points

    • 15. Your First Report - Intro

    • 16. Create a Datasource

    • 17. Create a Dataset

    • 18. Create a Report Object

    • 19. Report Under The Hood

    • 20. First Report Design

    • 21. Visualize Data

    • 22. SQL Resources

    • 23. Report Deploy

    • 24. Your First Report - Key Points

    • 25. Report Design - Intro

    • 26. Create Report Parameters

    • 27. Use Report Parameters as Stored Procedure input

    • 28. Use Report Parameters as Drop Down List

    • 29. Expressions

    • 30. Header and Footer

    • 31. Report Link & Recursion

    • 32. Filtering

    • 33. Sorting

    • 34. Interactive Sorting

    • 35. Subreports

    • 36. Charts

    • 37. Charts Customization

    • 38. Report Design - Key Points

    • 39. Report Desing in Real World - Intro

    • 40. Time Estimation

    • 41. Understand - Functional Requirements

    • 42. Understand - Non Functional Requirements

    • 43. Design - Report Input and Ouput

    • 44. Design - Report Implementation

    • 45. Ask

    • 46. Implement - Data Formatting

    • 47. Implement - Stored Procedure - Homework

    • 48. Implement - Stored Procedure - Solution

    • 49. Implement - Fat SP & Multiple Resultsets

    • 50. Implement - FrontEnd - Homework

    • 51. Implement - FrontEnd - Solution

    • 52. Implement - FrontEnd - Layout By Table

    • 53. Test - Targets

    • 54. Test - Test Book

    • 55. Test - Automation with Selenium (Part 1)

    • 56. Test - Automation with Selenium (Part 2)

    • 57. Improve - Performance

    • 58. Improve - Report Variables

    • 59. Improve - Report Shared Variables

    • 60. Improve - Report Shared Graphics

    • 61. Maintenance

    • 62. Report Desing in Real World - Key Points

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Do you know what is the most valuable part of an Application for your Customers?

SQL Server Reporting Services is widely used in all companies which want to extract valuable knowledge from their Data, to make strategic decisions in their business, at the right time.

In this course you will learn how to extract and present Data from one of the most popular Database Engine, Microsoft SQL Server. And, most important, how to do it when you are in office, with Technicians, Architects, Project Managers and Customers.

At the beginning, You will learn how to create your first "Hello World" Report from scratch. After, You will enhance it with several additional features.

Then, I will introduce you to "Real World" aspects, like Report Requirements Analysis, Effort Estimation, Mockup Definition, Team Interactions, Practical Implementation Strategies, Quality Control and Performance Checking.

You will find three projects included, check related section!

After this course, You will be able to transform any Report request in a Successful Delivery!

Are you ready?

Let's start!

Detailed Introduction

Meet Your Teacher

Teacher Profile Image

Andrea Sabatino

Solution Architect and Software Engineer


Hi! I'm Andrea. I'm currently employed as a Solution Architect and Software Engineer in an Italian company operating in Industry 4.0. I made internal training course for my colleagues, and I follow external ones related to my activities. I love teach and learn, so I'm here!

What I did as a Solution Architect:

Business requirements analyst for smart manufacturing. Design of tailored and integrated software solutions. Support to pre-sales activites, by solution quotation, presentation and demo. Collaboration with technological partners.

What I did as a Software Engineer:

SSRS Sr. designer and developer for production data reporting purpose. Web app developer based on technologies such as HTML5, Angular.js, Node.js and SQL Server for support and tracea... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Welcome!: Welcome to the SSRS in Real World course, where you will learn how to create professional reports from scratch, considering business aspects that may affect your work. My name is Andrea. I am a Solution Architect and Software Engineer. May experience is based on think, design and realized several reports from national and international customers. My interesting in data presentation and solution design was the right recipe to do so. After this course, you will be able to answer confidently to any report request. How? By learning from direct hands-on experience in a competitive business context. You will make your customers satisfied and your colleagues amazed. Major components you will find in this course are installation IDE overview, Your first Report, Report Design. And at the end, Report Design In Real World. I designed this course for reporting beginner developers are going to build professional reports for their new or actual company. Keeping in mind that theory and practice sometimes are not enough without a real world experience. Feel free to take a look at the course's description and explore more if you like it. 2. Course Introduction: Welcome to the SSRIs in real-world the course. And thanks for joining this course introduction. I am a Solution Architect and software engineer for an Italian company operating in industry for dot 0. I developed several reports for several customers. And my target is to transfer here, I mix two, reporting service knowledge and direct than some experience. This course is for you if you want to start exploring SSRS feature from 0. If you are going to use the reporting service in a new or actually the company. Or if you were to go beyond just learning a flat list of SSRS features. This course is based on video lectures, which are the main core small current, the slides that you can download your own article practice. Some tweets with solutions and internal and external resources. This course is structured in six sections. The first one is an introduction to the course. The second one is the installation of all necessary software from scratch. The first one is about the ID configuration and look up. The fourth one is your first report. Where do we learn how to create the first l World Report? Number 5 is the report design, where we will announce the report by using all the new features of Reporting Services. And the end. In the last section, you will use all knowledge to design a report in a real-world context. My understanding how to approach the entire lifecycle of a report. Each section has an opening that introduces you to the main topic about it. Maybe some tweets and a closing section to underline section key points. Use assessor S because it's a standard in reporting and is widely adopted in companies. It's a web-based final customer. Don't need to install anything on their systems. And at the end, SSRIs as unestablished expert system in the main formats like Excel, PDF and so on. So if this course can help you, we are ready. Let's start. 3. Install - Intro: Welcome to this deletion section. We are going to learn how to install all necessary software from scratch to build your professional reports. Will choose and components are freely with a rich set of functionality. Detail for learning should already have the sum of them is told or your experience, just skip the lecture. In this section, you will need some time as you need to perform some downloads and installations. Timing depend also on your connection speed and computer performance. So take your time, put videos, samples if needed, and have a good work. 4. Install - SQL Server Express: Let's start our installation process. The first thing we have to install, SQL Server 2017. Follow the link. We are now connecting to the option-like side. Scroll down the page, reached the Download button and click on it. The exe file is saved here. Click on it. Here we have to choose the installation type of the SQL Server engine. We can choose the best one because it provides some full configuration. Click on it. Now we have to accept the license. And here we can choose the default location is good enough for us. Click on Install. Now they download the installation package starting. We are now among reaching the end of the download. Now start the installation of the downloads package. At the end that we can see the screen in which we can set some parameters like connection string, like Folder, menu, folder, source folder. Now we leave the default and we can click on the Close button to another process. Press on Yes to confirm. 5. Install - SSMS (Management Studio): As a second step of the distillation process, we are going to install the management studio. Follow this link on this page, scroll down and reach in the download section. Then click the link below. Once the download is finished, click on the file. Now this is the wizard to install the management studio and press. Okay, at this point we are among the finish the installation process. And now this deletion is finished is required in order to complete the setup. So restarting the computer. And after we verify the installation, a made the restart of the computer. So let's go start minimal windows. Search for SQL. We can find that the management studio Stalin before here. Click on it. Server is here. We can select the database engine. Because we want to connect to the SQL Server extra server. The server name can be browser here. And go to database engine plus, plus and select the note below. Click on, okay. We use the default or Windows authentication. And we can now click on Connect. The connection is successful. We can see on the left the open-end connection with databases, security objects, replication and so on. Exploring the database known that we see in our solar system that the basis node and then not. So the configuration and installation of the Management Studio is complete. And the next step is to try a basic query with the database AdventureWorks. 6. Install - Adventure Works: As the first step of distillation process, we're going to install the AdventureWorks sample database. Follow this link here, scroll down the page until the download files and pick up the mantle works doesn't mean 17 the cup. Click on it and saved on the desktop. So inventor works isn't a loaded and it saved the hearing a desktop. Just to find the more rapidly and a copy to my bus, just under the earth. It's here. Now. We can go to the Management Studio, go under the open connection, right-click on that basis. And then on a restore database. As a source, click on device. Browse here. For the Adventure Works file. Click on Add, find it. Go under C. And click on rental works 2017, a cup. Click on Okay. Here. Then. Okay. Again. So we have a database here. We haven't they pick up to be restored. Record here into all its properties. We can now click OK here. The progress of their stories on top of the page is now running. At the end, we expect to see the restore the database under the database is node. We want to make a first query to be sure that everything is working properly. At the end of the restorer, a message appears at the base Adventure Works, doesn't 17 or store the successfully press. Okay, to confirm. And as mentioned before, we can see the database and the database is node. Let's click on Plus. We have our old tables, view and programmability and so on, on the database. Let's see some table. Right-click for example, on person that person table. Select TOP 100 rows of the table. 7. Install - Visual Studio 2019: As the fourth step of distillation process, we are going to install Visual Studio 2019 and SQL Server Data Tools. Follow this link. In this page. Scroll down until this section. If you have already installed Visual Studio, you can just use the installer. For our explanation. We are going to use the free edition here, Visual Studio 2019 community reconnect. Here we choose the community edition. Click on Free Download. Now on the Download, starting. This is the width or click on Continue. Now, download the installation package is starting. Let's take a moment. After the installation process. You can choose their workloads in charge of Visual Studio. Here, the only thing we need to choose data storage and processing panel. Click. And on the right side of the page, we can choose the components that we stop. For now. We take only the SQL Server Data Tools. In this third, we can now click on, not now, maybe later. Personally, I prefer the dark theme. I choose this. And click on start. The first thing you can take some minutes. Let's wait. And now we're reaching the end. Visual Studio 2019 is started on your machine. 8. Install - SSRS 2017: Well, the last but not least step of the installation process is to install SQL Server Reporting Services. Follow this link. In this page, scroll down until the Download button and press it. To download this. Now, starting when you reach the end, click on the X phi appears, click on install Reporting Services. We have to choose an addition type. We can use the Express Edition. Click on Next, read it, and accept the license terms. Here. Says that we need an SQL server database engine already told we have it. Click on Next, leave the default location and click on install. The setup is now completed. We need to restart the computer. So do it. And after you can check the installation is done by finding the SQL Server 2017 reporting service element in your start menu. 9. Install - SSRS Configuration: At this point that you should have all the components installed on your machine. You still miss one step to configure the report server. So let's start and search for reports. We can type it in the Windows bar without the S and click on the Report Server Configuration Manager. For the connection, we can leave the default parameters. This is the server name on top and below the report server instance, connect. So this is the utility provided by reporting services, which we can configure the report server and the web portal. You must set up the service account. You can leave the built-in account. Click on Apply, then forget it. We must run the report server before. We can do it by clicking Okay, below we can see results and that task successfully completed. Now we can go to the web service URL. Here we can configure them to browse by Chrome, for example, our reports. We can ignore that warning and leave all the default settings. So we will connect to the port 80. Security remained the default and that is the URL. Click on Apply. And what should their results. Also, in this case, the task is completed successfully. Now, we need to go to the database panel and set the database. Now. You can see that it's empty. So we need to change here the database. In this case, it's not a change. And new creation click on Next. In the first field is the server name. We need to specify also the instance name. In this case is SQL Express. Written as you can see here. Yet indication type is still the default one. And we can test the connection. Connection, connection is succeeded. And we can click on Next. We can leave all the default here. Database name, template, database name, English as a language. And the mode click on Next. Leave the full credential type. Click Next again. You can ignore this morning is not impacting our installation process. So we can click on the Okay button and click on Next. Now, the Configuration Manager is creating the report that database on our SQL experts database engine. We consider progress below the progress bar. Everything is succeeded. So we can click on finish. Task is succeeding at all. And the last settings are about the web portal URL. This is the virtual directory reports. And we can found annual report for that we will develop in future. This is the link that we'd be deployed after the Apply button. Pascal succeeded. So now the link is enabled. And we can click on it to browse the root directory. That in this case is empty. 10. Install - Key Points: You reach the end of this deletion section. K points here where installation components, I'll twist them and how to configure them. Next section is ID overview. 11. IDE - Intro: Welcome back. You're now at the first section of this course. You're going to learn the main structure of the development environment and how it needs to be configured to work with Reporting Services and SQL Server. Enjoy it. 12. IDE - Configuration: Now open Visual Studio and click on Create a new project. Here we have all the types of projects that we can create. But as you can see, we can find our reporting service project. We needed to manage these with an extension. Click on the extension, then manage extensions. Here. We have to search and reporting salaries extension project. You can search for a report and then install the Microsoft reporting service projects extension. Once the download is finished, a warning from few that you needed to close Visual Studio to apply the modification. So let's close Visual Studio and reopen it again. So we need to now to apply the extension no loaded, click on modifying. Wait for the extension installation, and then restart again. Visual Studio. Create a new project. And as you can see on top, we have a report server project tweet third, and report summer project. Click on this one. Press Next. Here we have some settings like project name and so on, up to we can leave the default ones. Can create to-dos now creating all the files necessary for the project. And at the end, we have the extension installed. 13. IDE - Quick Overview: Visual Studio looks like after the installation of the extension. On the right side, we have two planets and properties. The first one, we have all the solutions. We have now a single solution, one single project. Each project is described by the sources, datasets, reports, reports, develop it until now. Now, of course, it's happened. Here. Our older datasets. You can think of it as a query output like a table. And they are shared because they can be used in all of the reports to the final data sources. The bridge connection between our reports and the database. Also this can be sharp. The property panel below all the properties of the selected element in the designer. Now, we haven't tenure reports, so we can see the designer on the center. On the left side of the stem, we have two types. The first one, the solder explorer, is not needed for now. Click Close. The toolbox instead. Contain all the widget that we can drag and drop in the central container. For now, we can see anything because we don't have a report open-end here. But if we click, we can see all kinds of widget. In particular, we can look to their report items that will be enabled once we create. 14. IDE - Key Points: You've reached the end of the ID overview section. K points here where, where to find tools in ID and fun I put opposes. The next section is your first report. 15. Your First Report - Intro: Great. You reached out and the first section of this course, real practice starts here. You're going to learn how to create your first report from scratch using the fundamental elements called data source that the set and publics. After this lesson, you will be able to visualize any SQL Server data as a table and export it in Excel or PDF format. The main suggestion here is only one. Practice. 16. Create a Datasource: Let's create our first trapper. Before beginning. A data source is based on data and data. In our database. We have the data in the SQL Server database, in particular with the AdventureWorks sample. So we need to attach our first fret board to that data. And how can we do that? We don't get the source. So select shared data sources. Right-click. And then we can configure here the name. You can leave it. As it fooled. The type is SQL Server. And here we can type in the connection string. If we don't have it, Let's click on the name must be put here. We can take heat from the Management Studio. This one. Let's copy neck again to Israel studio. And vast it is Windows authentication. For the connection to the database. We can enter here the database name by drop-down button here, and select the inventor works. You can click on Test Connection to test if everything is set properly. And briefing, it's okay. And press okay. We have here over the connection string for us. Credential, as said before, are based on Windows authentication. Click on Okay. So now we have our source here. 17. Create a Dataset: In this lesson, we're going to create and up until now as our cup, we created a single project. And the data source map, the sample database. And data set, as said before, is like a table. Let's understand it by opening the Management Studio. Here we have a query on the person table. Here we have an output. This is a data set for reporting services. Fundamental property. Because let me see, view. The front end. For example, using the set can be linked in a table and that table. So let's start the creation. Click on new data set. We have a name. Here, we have the source and the one we created before. To design the query editor here. You can best required before. I suggest you to use a stored procedure instead. This is because stored procedure can handle more complexity and variables that normal query. So back to the Management Studio. Click on programmability stored procedure template. To create a new store procedure. We don't want any parameter here. And we can pass to our statement. Here. We can press F5. We have a syntax error because I didn't create the name. Of course. Your first F5 succeeded. Click on refresh. And we should find the SBI. You first the report procedure. Here we have the stored procedure created. Let's try to execute it. Press and execute. That's nice. We have the expected output here. So back to the Visual Studio. We substitute, this is the procedure before. We can choose it from this drop-down list. And then press OK. 18. Create a Report Object: Now let's see how to create a report. The first way to create a report by the reports, we right-click on reports and click on the new report. Here, we have to select the data source. We can use the default one we created before. And click on Next. Here we ask for designing the query. We are not interested in using the query string. We have to provide it to reach the finish of the wizard. Just a simple query. We wanted a tabular report. And we don't want to enter in the face during their equal to either because we want to design a report in the classical way. So press on Finish. Select the report name. I'm going to leave the default one. So we have created the designer in the central panel is open. And we have the configuration report data panel on the left side. Let's explore them in order. 19. Report Under The Hood: Now, before looking at the report data panel and design panel, I would like to take a look of what is a report under the hood. So if you select that I put one. You can look at the properties below. There's a property called footpath, copied and passed it into the File Explorer and raise their filename and explored folder. Here we can see the elements composing our report. In particular, we can see that not the set source report. And the report itself. This report has an extinction format called LDL, which means report definition language. As you can see, the report definition language is based on XML. We can see here that the sources section and reports parameters. For example, you can expand each node. See name and other properties we specified before in the development environment. 20. First Report Design: Now let's look on the panel. We have some nodes here. Let's look at the data source and data sets. If we expand the data source, we can see we have already provided this is because before we created a shared data source. And as a shared data source, this one is provided in any new report that we are going to create. The look at the data set now. We have already one. The set is not this one. Because this one is the one we created with the weeds. In fact, you can see the test column we provided before. Query builder. We don't need it. So let's press on Delete, Confirm. And now it's empty. To create a new data set available to the report. Click on it. We can just use the shared one. Click on Refresh fields and look on the fields. And now we would like to visualize this data set. We can do it by drag and drop a table. This is a Widget. Company X. We can define the shadow here and beta here. So as data, we would like to use these columns. So right-click in the top left corner and select properties. Here we can change some properties like name not the set. Would like to change the data set. We can click on Okay to confirm. And now column by column, we can choose the right field. In this case, we would like to map the business entity ID. The firstName, and lastName. 21. Visualize Data: Now, first of all, remove with the unuseful widgets before by the weeds up and press Cancel. Now, we would like to put a title at this report on top of the table. To this box. Here you can show only the available items by removing the check here and drag and drop it here. And use the guidelines that the PRC tooling. The element. Title centered text expanded the element just to the edges. And you can make a fairly safe. You can put also some side here. For example, a central texts, a font bigger, same for the second row. And now we can look at the page form. Right-click on the black pen here we can set the paper size landscape, for example. Margins. Save again, and click on Preview. This is our first render the report. You can click on this button. Yes. 22. SQL Resources: In this course is not required to have a deep knowledge of SQL language. But if you want to go deeper in this topic, you have two online resources. You can check. The first one is NAPQI of free school. Here you can check the general SQL language divided into topics. For example, if you look for SQL, select here some information. And an interesting thing is that you can try it yourself by an online editor with the real database behind that, you can run by clicking on Run SQL button. As you can see here the results. The other rest is the transact SQL language link. This is provided by the tutorials And it provides an overview. Also in this case, divided into topics. 23. Report Deploy: Now the question is, how can we deploy this report for our customers? Let's look on the Solution Explorer button up here. That depends on one hand, on the other hand, depends on not the right order and deploying it is from the source data set. Right-click on that and click Deploy. Output console will appear. And as you can see, deploy and succeeded. Let's do the same for. Here we have an error. It says that one is not supported on SQL Server X plus a standard version of SQL Server. You can deploy, Of course, these kinds of datasets. We cannot. So for our testing portfolio, let's create a local data set identical to this one. Here we can choose the source is the same procedure. And select it. Click on Okay. Okay. We can, we can change the name here. Press OK, Save. And then we can keep a diploid to haploid now contains two datasets, one local and one sharp. To deploy SQL Server Express, we needed to remove the shadowed one. Okay? Now we can deploy it. As you can see, succeeded. You can go to the Project 1. And this is how our report looks into what page. In the next section, we're going to talk about report parameters. 24. Your First Report - Key Points: You've reached the end of your first report, section. K points here where how to create a data source. Create an opposite in the store procedure is utilized and datasets, WX widgets, run a report preview, exploratory in PDF or Excel format. And at the end of the closure report. The next section is report design. 25. Report Design - Intro: In this section, we will explore all the main features of Reporting Services. We are going to talk about report parameters, expressions, header and footer, Report Links, filtering and sorting some reports and charts. My suggestion here is to replicate activities explained in each lecture in order to consolidate in your mind how to find and use its functionalities. As of course, attachment, you will find that the report development during this section. 26. Create Report Parameters: We would like to dynamically render on this table. For example, by filtering the output by the first letter of the last name. We can do it by adding a new parameter. Right-click here, click on Add Parameter and provide the same information. Parameter. Lastname. This is the technical name, so it must be like this. And this is the label name available for the user. Text type. User can also leave it blank. And the parameter is visible. Click on Okay. Now, in this section parameter here, we have the last name parameter available for the user. As you can see, Section parameter is like a table. We can delete unwanted columns and unwanted rules. And if you want to entity or per meter, just to double-click here and edit whatever we want our new parameter. You can also click on the empty space in the table. Now how can we use this parameter? We can use it as a stored procedure parameter. 27. Use Report Parameters as Stored Procedure input: We would like to use the last name parameter. The first thing to do is create the corresponding parameter in our stored procedure name parameter. Now we have to use it somewhere. We can use it as mentioned before. Lastname, firstname, plus percent. Press F5. Now we can see here and choose the report parameter. Okay? So start for example with the letter M. And now we have the last name, starts with an M. 28. Use Report Parameters as Drop Down List: Now we would like to push forward the last name parameter. The parameter values specify that is, you can add each element of the drop-down list. We put just free, save it and preview. You can choose the letter B, for example. As you can see, each last name starts with a B. Something will click again on the last name. And if you look at the last option in the drop-down list, elements can be retrieved from a stored procedure. I prepared before. Retry thick. And the distinct keyword. To choose the last option. Now, we have to create the data set will be selected here. So do it from the same subject here, anyone name for meter. Remove. And take it to from our New and click. Save. As you can see, we have available here. As you can see on the last name starts with an M. The next topic. 29. Expressions: Now we would like to remove something like search for letter and concatenate. Say you select the select expression. You have several fields. You can use. Hello expression. Now, episode on expression. 30. Header and Footer: At this point, we would like to add to a page header and footer section already senior leader. In this area. You can drag and drop fields like execution time and page number. For example, on the left side, go to the main folder, right-click on the major ones, the major here, you can drag and drop it into here. In the center. We can put some labels, one and another one. For the company name. To be more precise. In adjusting the alignment of the abdomen. You can also use the R0. To avoid the guidelines and the company name. We can put the page number plus STR function. You can find the STR function With an example of your save and preview. As you can see, you have our heater here. If we go to the next page, page number changes. Now, let's highlight it. Corner. Click on the next side, and click on the Start. Here. Are the small triangle here. And press scroll down the properties until they repeat on new page property. On each page. 31. Report Link & Recursion: Let's look at this example and just modify the report one by adding a link in the first column. For example, if we click on J Adams, we are going to be redirected to another report which lists all the managers of the current person. As you can see, J has a free manager. Let's look under Backend. As you can see. The first column, we love blue-collar and an underlying click on textbox properties. Now and go to action. And this is the panel in which we manage the link, select, multiple record, specify, and destination report. They report to that I just created to list all the managers and provide the here all the parameters needed to the target report by adding that. Now let's go deeper. Report that lists all the managers is a simple table with a data set called managers. Based on the USB. Get employee managers that these are standards store procedure of the AdventureWorks sample database. So the structure of this table is not flat about the hierarchical. We have, our person, the first-level manager, the manager, the manager, and so on. Let's go back, preview, search again for J and look at these managers. So John is the direct manager of J. Peter is the direct manager of John, and James is the direct manager of Peter. We have a free level of managers. At this point. We can look at the search manager link, which is a link to the report itself. It sort of caution. For example, if we click here to see all the manager of jom, we would like to have only Peter and James fluid. They can go recalculate on John ID. And as we want, we have only Peter and James. Let's look at the backend reports to textbox properties. Action as before. And look here, we have an action that triggered task to another report. A report is the same. So we have reports shoe as a source and a target. 32. Filtering: Now look at this table again. Data here is filtered and the query level. I mean, before the local data set this field, we have another chance to, after the local data set this field. To do this, right click on the public's properties and reach the filter. Click Command. And as you can see, the set of fields let us to filter the data set and just before the rendering. But after Israel tribal from the query. For example, we can filter all the people that have the letter of the firstName and lastName. To do this, let's choose the firstName. Thank you. Here we have the disorder. 33. Sorting: And this point, our output is filter, it, not shorted. We would like to sort data by firstName and lastName. Go back to the design, select the table, right-click here, and go to the Properties. Then go to sorting. Here. We cannot do all the columns to be sorted. We want to shorten by first-name in this order. And then we want to sort by the lastName. Click on OK. Save. As you can see, our output is now sorted by firstName and lastName. 34. Interactive Sorting: Users want to be able to sort all the columns of a table by themself. We can let this behavior by clicking on the desired column, either MOOC today textbooks properties. Then interruptive sorting banner, and then check the flag enabling dark the sorting on these textbooks. Choose them. These are the column. And press. Okay. Do the same for class name. My last name. Click on Okay. Before are running the preview, I would like to set a default for this parameter. Click on the full specify values. Specify. For example. Ok. Save, and preview. As you can see, once the report hazard falter and report these random immediately without clicking there, we report the bottom. As you can see. In the firstName and lastName columns, we have a small arrows to sort the table by this column. By the other one. 35. Subreports: Now, in the course, I created a report, the report containing the people. And the second convening. In real-world, you needed to create a new section that is in common between reports and not replicate it across. To do it. Just reporting suddenly switches. Like the summary part. I already created. A common section that I would like to share the report one reports. Let's look at it. Is the common section, our summary report, containing textbooks, some lines, and an image. How can we show this report one or two. Let's do it. Let's specify here, Save, copy and past it. And the secondary port. Save. And preview. The first one at the end of the page. So here we have this section again. 36. Charts: In this lesson, we're going to talk about the charts. Just to create a new report here. As a copy of our report on. We would like to have a chart like this to understand how many people start with each letter. To do so, the first thing to do is to go to the Management Studio and add into our store your report. And you feel that the first letter, Visual Studio. The first thing we need to do is put a fresh as we added a new field. And you feel it. Here, we denote what lastName because we want the entire data set. And also from here, starting from 0, we need to add from the toolbox, I charged them. Take the first one and then edit. We can define the frontal axis here. January. We need to group on the first letter and define the count on the vertical axis. My counting each different person. Then you can resize it. And click on Preview. As we can see here, only some letters is because you have to make sort of tree. Right-click here. Horizontal axis, properties, axis and range intersection. And put here one. Click. Okay, save and preview again. Now we have our charter. And as we can see, this is the one. 37. Charts Customization: On charts, you can do a lot of customization. In each charter, you have several IRS title, legend, chart, area, and so on. You can customize each area by right-clicking on the Arab and selecting the relative properties. On each area you can set general position. Conditional visibility also be on an expression, an action to click on it and go to other report for external URL and other customization on like font, border and shadow. If you click on the main chart area, you can right-click on a single bar. Uncheck, show data labels to see the relative number. You have also a lot of properties for each categorization group. Like filtering all the values we want, quartering them and using other variables. Interesting thing you can do is convert this visualized dots it into another one without changing anything. In the charter definition. You can do this by right-clicking here angle to change the chart type. For example, we can convert it in a pie chart. Click Okay. We can go back. Change again. And again. As you can see, we have chosen the best option for your specific chart. You can also drag and drop. You can move it here or whatever you want. 38. Report Design - Key Points: You've reached the end of the report design section. K points here where helped to create a user report parameters, use expressions, use header and footer, link reports, filter and sort, and that's it. How to use some reports and draw charts. Next section is report design in real world. 39. Report Desing in Real World - Intro: In this section, we are going to learn how to build the reports in an organizational context. In a company, you are not alone. During report building. You will interface with other people, mainly of four different roles. The customer is the main reference of the commissioning company. He provides all the requirements and additional information needed to define the expected outputs. He will also test the defined reports at the end of the delivery process. The project manager takes care of timing in general. He organizes kickoff meetings and regular state of work sessions to check the time used. Again, the estimated time. The architect or tech leader can give you some general guidelines to design reports, the backend and less often report front end and the end. Other developers can have in charge of a piece of broken or help you to find the report data across databases. In this real world context. The best approach you can adopt to develop a report is a waterfall model. Above on this identified seven phases. You start the from understanding the problem, may reading and analyzing the text specification provided from the customer. After this, you need to design report parameters and report the mockup that needs to be upgraded by you and the customer. Do this, you need to ask if the proposal is okay. After that, you can implement the agreed proposal and then test it. After that, you can improve some reports aspects like performance and UI design. At the end, once so the report is delivered. The maintenance phase starts. 40. Time Estimation: As you can see, the implementation phase is an Alpha of the entire time. And other phases are like 10 percent of the total. Moreover, if you need it to develop a report to set your RPM, may ask you how much time you need to develop a single report. To answer to this question, Let's take a look to this graph. Your first report can take long time to be developed. But the second one can take less time than the first one. And the trend is the same for the other. So a good estimation to build a single report is to take the Mintel report of your entire report set. As you can see, this may correspond at your medium time to develop personal report. So in summary, to answer three rpm, just think and not to the first one, not the thing. To the third or fourth one. 41. Understand - Functional Requirements: Well, now let's analyze each phase of the waterfall model. Starting from understand. Here we have a sample made receiving that from a European to you with object reports in scope for the strange project. So let's read them the requirement of our customers. We would like you to start with the development of 10 reports in scope for the strange project. The first one, the nutrient one, is the calendar. We need a dynamic world Callender suitable for any year, month. We would like to easily view and print a month per page with all the weekends in red. Let's analyze it in. The first phrase is, we would like you to start the development of 10 reports in scope before the strange project. Report in scope. So we already have the final set of reports in scope for the project. The customer explicitly ask for reports. So we assume to use assessor as the current established reporting platform. A great idea could be defined as single graphical template in order to have a coma report template used across the town reports to define. Then the first one, the urgent one, is the calendar. First consideration is that we can start with just one report. The report name is the calendar. So we can expect to visualize something like days, weeks, months, and years. This phrase specifier so that the report is urgent. Not the piano will take care of timing and not us. Then we need that dynamic with Callender suitable for any year. We would like to easily view print verbiage. We can extract some considerations here. I will Callender. So the customer probably have in mind the Acoma will collect data visualization. It would be like just the calendar you have in your home. So we can make some assumption on the expected mockup then any year, month. So we need to deliver a report which visualize days according to a given year or month. This probably will be our report parameters. At the end, customer specifies that it would like to easily view and print the report. So we need to understand the format. So this could be our first question to the customer. With all the weekends in red. So we need to highlight all the weekends in another color. 42. Understand - Non Functional Requirements: Until now, we understood all the functional requirements of the report. But under the hood, there's something more. I'm talking about non-functional requirements known as an affair. This typically are like performance. Report needs to be fast, or report needs to be clear. And essentially, your customer would never ask for them. But he always need them. So you need to take care of them. In particular. Before the design, need to take care of the user experience. And after the design, you can perform some fine tuning on the performance. 43. Design - Report Input and Ouput: One of the requirements are clear. You can start to think after the design phase. Here, you will need to define the report, the input. And the report output. Input is defined by report parameters and output is defined by this phase. Your solution architect, tech leader may help you with some headlines. Report parameters for our calendar are here. And here is requested me a free text as customer asks for any year. Of course, it's monopoly. And as default, we can set to the current year. On the other hand, the MFA is of course, a drop-down list is mandatory. And we can set the current month. By setting the default parameters. We can run the report of the opening. And this is a nice feature for the user experience. As the report output, we need to be lamb cup. Let's do it. We can start by placing the report. We can place the company logo on the left. The company may name in the center. And the page number on the right. We can put generation time just as a reference. The first of the month. And the second. The second part. We can put the current month name. And I saw an improvement on the user experience that we can define on the left. On the right, we can let the customer can navigate without input. Now, we can send to our customer implementation phase. 44. Design - Report Implementation: Now report the input and output are clear. So we can start thinking and the implementation phase. We just need the one data source because we want to create one single store procedure that drives by year and month days of a single month. We can create one data set with all the days of the month. And to Public's Viz on the data set for each column specified in the mockup. Also in this phase, your solution architect, tech leader can help you define these objects. Before starting the implementation phase. Please be sure that your customer validate your proposal by a written okay. So you can define what is the scope and what is not. 45. Ask: Now it's quite clear what do we need to do as we understood the customer requirements and how we need to do it as we defined the path of an implementation. Time to the ask phase. Let's ask to the customer. With the customer, we need to be proactive, proposing one solution to be validated before implementation starts. Here. We don't need to ask for details like report header, footer, columns, labeled body parameters, and so on. Just send it to the customer. Our solution, then discuss on it. On the other end. Question for our colleagues are, for example, they can query already exists. If not, who will develop the query. In the worst case, you will need to create the query from scratch. Of course, you can ask her to customer and colleagues any question. During the entire lifecycle of the report. Medici's a nice time to collect all your questions and submit them to the customer and colleagues. 46. Implement - Data Formatting: Now let's assume the two centimeter to the customer with the report parameters mu kappa. And the question regarding the calendar print format. Let's assume also that the customer already replied You. For him. Parameters and mockup are okay. And the print format is a four. So at this point is fully cleared what implemented and how. We need to follow these steps. Create a new SSIS project. And you stored procedures, but can a new data source to map it. A new data set. And through very important using toolbox Sweden. During store procedure coding, you may encounter some dots. For example, where should they form a weak name and these number in the store procedure or within a report expression. This is a problem of that formatting. In fact, SSRS and SQL can both perform some typical logical transformation. We are talking about. Date, time formatting, string or number formatting that the filtering aggregation, MOF operation and so on. So the question is where to do them. You can choose the place dam, mostly on the report or mostly on the stored procedure. Here are the cases that may happen. In the first one, we put the logic mostly on the report. The second one we could mostly on the stored procedure. And at the end we have a balanced configuration. Choose the first case if your assessor rescue or higher than your SQL language screen, your stored procedure is sharp or it can be helpful for other services. In fact, formatted data in the supersedes all the other service needed to use the same format. The end, if you need to move apart a little bit to the SSRS server. Choose the molecule. If your SQL language skills are higher than on SSRS, your store procedure is dedicated 3 or report only. So you can form whatever you want without affecting other services. And at the end, you need to move part of the load on the database server. Choose at the end, the mode free. If you have a balanced scale between SQL and SSRS, you stored procedure couldn't be used in other service in future. Or you need to balance the load between SSRS server and database server. Of course, the choice depends on several factors, like RPA configuration and server configuration. Your skill has mentioned before and during specific structure and complexity. The best data formatting strategy for our report is the first one. Because we have higher SSRS scale than SQL. And our stored procedure output, our list of a base lemma alpha. Of course, it can be helpful for other services in other formats. 47. Implement - Stored Procedure - Homework: Well, it's time to leave you some homework. Try to implement that report mechanics with a single store procedure. As your tech leader suggest to you. Should've seen, puts our ear and mouth procedures. Output is a list of days. So try to do this coding exercise. Take your time. Once you finished, you can compare your procedure with the core solution. If you're not comfortable with SQL language, try anyway. Of course, you would find that the commended the CO2 as an attachment. 48. Implement - Stored Procedure - Solution: Now we're going to see this to push the solution. I just created the stored procedure calendar with the two inputs. Here. The main idea is to have an output table and define. The first record has a start date, and the last record as an enolate, and then feed all the missing data. We are coarser. So the first thing we need to do is to set the start date, end date. You can set the start date. The date from parts function, which takes the year, our input parameter, gather input parameter. And the first day of the mouth. We can set the end date by taking the end of month function above the start date of the fine before. If the parameter mode is now, we can assume to set the start date as the first date of the year and the end date as the date of the year. In our context. This will not be used because our output is, my mom has made sure before we need to do a cursor that feed all the dates, a start date, and end date. For each step. We need to add one day. To currently wait until we reach the end date. In each step, we save each row in the output most stable, and then will terminate. As a stored procedure output. We can try to execute it. For example, this month. And as we can see here, we have the expected output of a single column, which will do days of February. 49. Implement - Fat SP & Multiple Resultsets: At this point, our stored procedure is finished. Let's see how the stored procedure looks like. A stored procedure mode. I created these new ones. The procedure has a copy of the previous one and some information in the output. Instead of correlating them. Within SSRIs expressions. We can provide already hear the day number, the name, recorder in the calendar, and the position of the row in the left or right on. Let's look at the output. Instead of one colon. We have the more information on SQL server side. So we move the load from assessor S to SQL Server. This is a good practice when you want to centralize the expression in a single script. Another topic we can handle here is multiple results sets. A single store procedure can handle more than one output results set. For example, we can add one more. Same, execute the procedure. And as we can see, we have free, financially independent or not. Here we have one issue because SSRS reports can handle in a single data set. Only one of these output. If we try to provide more than one result set to a single data set, the first is taken my SSRIs, but the other ones are all ignored. We can choose to visualize each result, set my parameter in the stored procedure, and conditioning the code to return the right one according to the parameter value. 50. Implement - FrontEnd - Homework: At this point, you need to try to implement the report front end. My checking mockup and customer specifications. You're stuck leader suggests you to use one data set. And suitably. Once you finished, you can compare your report with the core solution, which will be explained in the next lecture. Of course, you can find the report, the LDL, as an attachment. 51. Implement - FrontEnd - Solution: This is the proposed implementation regarding the report has a specification. We have one single data set which uses at the source to the AdventureWorks sample database. And the procedure. We have just one field and we pass here year and month parameters. Now, I recommend you to explore by yourself each element of this report in order to better understand how it was built. I can now show you the preview. It takes the current here and the current month. Thank Here we are. We can change the mode, drop-down list, and then navigate across moles. Make sure that you report on one single page. This is a frequent error. And to avoid the two blank pages, you'll need to check. Reportedly mentions. In this case, we have an H in heart rate. And we can check by viewing the ruler. And as you can see, the total length is 17 centimeters. Four centimeters for margins for the left side and two for the right side. 52. Implement - FrontEnd - Layout By Table: We are now going to explore a further aspect on the report from that. This is a real word report. As you can see, we have some sections. Operators can check some activities and put a signature at the end. Here. Some parameters are printed out. Here. We have some additional activities, timestamp, we do random, and in the end we have some nodes. Now the question is, how many we can see in the first section? I leave you some moments to pick account. When the answer isn't. Just one. This is because the first section, as the others, is made by a single table. This is a best practice in drawing. Elements like this. In a layout in which we hide all the borders that are not necessary. And we show the remaining ones. By doing this, we can easily resize. Each column, recites checkbooks with a few clicks. If you draw them as single elements, you need to cite them by week. Each element and taking care of the alignment of each checkbooks, lines, and arrows. This technique is used also in the second section. In the third section and in the last section. So in total, we have four tables that we can manage easily. So the main point here is that when you have a lot of elements, tried to draw them with a single table when possible. Or use the table itself as a container for nested elements. 53. Test - Targets: At this point, you report this implemented. Now, you needed to test it. But what to test? You need to test both functional requirements and non-functional requirements. In detail. You need to test that the correctness by comparing your data when the expected data from the customer. And the presentation. By comparing the report from 10, the report mock-up realized before. You may also need to fill your report that with the longest value. In order to understand if this longest value may change your layout. Regarding non-functional requirements, you need to test the performance by checking loading timings, explain it in a text, slides, and user experience. By replicating user action to understand if performing a simple action is taking too much steps or is not linear. 54. Test - Test Book: To ensure the best quality of your report? Form, that's a good practice, is to create a test book, lists all possible scenarios that may occur. A textbook is a table like this. For each test, you needed to report the description, the expected result. If the test is passed or not. The last execution. And maybe someone else. In this case, I reckon the first test, open the report in the browser. And we expect that the report is loaded with the parameter defaults. As a second test, we need to click on the next month bottom, and we expect the method M of changes. This practice is really useful also before the implementation starts in order to better define the scope of the report development in agreement with your customers. In this way, we usually define or manual tests. There is another way to automate all the tests needed for the report. 55. Test - Automation with Selenium (Part 1): In Selenium, we can automate the process and our reports in browser. So you can download the Selenium ID. We have the several types of download. We can choose. Id. In particular, on Chrome extension, my click on this link. I already added these extension. You can simply click on the Eastern Bloc from instead of this one. Once you get these extinction, opening, a new project, I created a project called calendar, which points to our report income and contains one basic and simply change the current year per meter. Of course, the expected output is to see menu here. First of all, let's look at the execution. To select it and press some test. Sudarium is now loading the report by blink. And it's going to change the year. The same. And click on the report on the test steps. And we can see our report is loaded. But how can we build a test like this? Let's create a similar test. And the new test case. Another name. And then you can click on Record the bottom. And now we record that each action that you perform in the browser. So let's click on that. Here in QuickBooks, Change layer, click outside, and then click on. Our test is completed. And we can go back to selenium to stop the recording. Here you have all the steps recorded from selenium performing the check or change the test. The first is the opening of the report. The second thing that we know of sides selected the current frame and the clicking on the input box. Before this step, we need to add a new comment. Because we need to be sure that the element is present on the interface. We can click on the Select targeting page and choose the input box is the first page building. We need to insert an element of the same box. In this way, we are sure that the click of the element is not only when the element is present and in the same way. On the end, we needed to wait. And that's the current here. So let's put weight for adamant present and select. The best way to select an element is to choose the targeting. And at the end, we needed to place an assertion. To check on that to be a parameter here is equal to the front-end that report here. We are asserting that this is equal to 20, 22. This point, you can access sequence. Now looking at the report, changing the year and clicking on View Report. As you can see, all the sequence is pesto. Correct. 56. Test - Automation with Selenium (Part 2): Now let's look on the test. This is just a composition of the techniques we use. The main idea is to change the year that changed the most in the summer. Then you report at the end, click on Next. The ER in the heater changes. The month invader becomes January to December and February. We can perform also other assertion. Check that spread correctly. For example, 28 number is always present. And also to check the report footer is correctly displayed. The expert. A final checking. We have with the next tier here, January in the middle, December, February, us next month them. We have 28 present in the report. Footer is okay. Let's look at the implementation. And the beginning is open. And as before, we wait for the first parameter to be present in the report and then click on it by typing 2022 year. Then we wait for them to be editable. And then select the summer. Then we wait for it to be visible. And at the end, we perform on the assertion to check the report is okay. We check that today here labeled least 2023. January. Labor is December, February. You've already find that some elements are present, like Reaper footer, that we printed only one pager. We not exceed the layout. We check the 2008, be present for any month. And at the end, it gets to the export of the report in PDF format. In general, you can split your test in more than one step and execute them in a single-step vote by clicking on tests. You can use. To understand the Selenium works on your report. You can also export each test them in separate formats. You can check all the Selenium in this link and use it the most convenient for your case. 57. Improve - Performance: At this point, your report is compliant with the functional requirements, but maybe you could improve it on a fundamental non-functional requirement. I'm talking about performance. To do so, you need to consider the components of loading time and minimize the biggest ones. Execution time is the time needed to restore procedure to complete its execution. And they're turning an output. Network. Latency time is the time in which the network move data between client computer, SSRS, server, and database servers. In real-world, several databases can be placed in several different sandbars. So you need to consider this fact. Network latency, moreover, depends also from the client to network. And the computer current performance. Report processing time is the time in which SSRS receives data from the database and process it mainly by aggregating, filtering, sorting datasets, and solving expressions. Reports rendering is the time in which the report is graphically rendered to the final user. At the end. Here we have a real world scenario. We have a web client asking for a report, SSRS as a web server, and a database where our data lives. Let's look in detail at this flow. The web client generates an HTTP request to the web server. Ssrs under the hood. Data from the database. Invoking for example, a stored procedure. When the query is completed, the output is generated and return it to SSRS. Here happens very post-processing. And then very powerful rendering. Once the report is rendered, our response is sent back to the browser. And the browser renders the page. For the final user. Mean that the components are report data retrieval. That is a sum over the point 2, 3, 4. Report data processing and report the rendering. This information can be found in this system log of reporting services. In particular, in the execution log table on the report server, that kind of database. As you can see, we have a row in this table. For each report execution. We have the parameters start, time, and time and timing seen before. I'm not a Retriever. Time processing and time rendering. In the case of row one, we can see that the biggest time is spent on processing. We can see same information in a specific view provided by Reporting Services called execution lock-free, which provides the same data but also other information. For each row. We can explore more details in a specific XML. With this, we could go into, for example, in timing for each data set. As you can see here, we can see that total time not a retrial for each data set. By doing this, we can find this lower data set. 58. Improve - Report Variables: Humidity, also quite complex reports. In a complex report, you may find a lot of similar sections in which, for example, you need to specify the same font size. 100 case could be a report, but you need to visualize a lot of the times. Each one with the same string format. In both cases, changing the font size or a date format. Cuz you to add several beaches, taking a lot of time and generating potential mistakes. We can solve this issue. We do report the variables. When a variable you can edit once the font size and once the date format and all the widget will change other same time. For a variable. Font size and net format, as mentioned before, section widget visibility, the obliques column visibility, chart style, or a report style in general. But how to create a new variable? Let's go to Visual Studio. Open or report. Click on the black side. Angle to report properties. Here, go to the variables tab. And as you can see, the variables here. And the value is 15. We can also put it as read-only variable or create a variable with an expression. Click on Okay. And now you have defined your variable global to the direct report. You can use your variable inside an expression you want. Just open it, open new expression and reach the node variables. Here. We can find our test variable and use it as you want. 59. Improve - Report Shared Variables: Now, what about if you want to control an entire set of reports with a single variable. For example, for the font size, for the date format. You can choose variables because they are global for a single report. To do this, the main idea is to use a k value, the shared data set. You need to create a simple table on your SQL Server database, which two columns, K and the K is the attribute name like font size. And the value is the corresponding value. Implementing it as a shared data set can share this information across each report. And when you change a value in the database, or the reports will follow the new value. 60. Improve - Report Shared Graphics: In addition to variables and shared variables, when you need it to implement an entire set of reports. You can use also single-shot section. I'm referring to graphical sections like header and footer, some reports, any major. A common case is to implement a logo for a company has a single image across the entire set of reports. When the company changes its logo, then you cannot please the logo ones. Instead, the changing data on each single report. 61. Maintenance: Once your report is delivered to the customer, the maintenance phase starts. In this phase, you may be triggered for checking for checking performance over time. Because after a long time you may have a lot of data instead of one as the beginning. And at the end, evaluate additional request. It maybe change request or not. During any check, you need to maintain, updated. The test. 62. Report Desing in Real World - Key Points: You've reached the end of the report design in real-world section. K points here where how to approach may phases over the lifecycle like understand, design, implement, to improve and maintain. This is the end of the course. Thanks for watching.