Looking to generate and share reports with your organization? In this course you'll get up and running with SQL Server Reporting Services quickly, or just refresh yourself on specific techniques when creating reports.
SQL Server Reporting Services is a fantastic tool for generating and sharing reports across your organization. This course, SQL Server Reporting Services Playbook, is designed for two types of people. The first is the busy IT Pro, who needs to come up to speed quickly with SSRS. The second is for people who have developed SSRS reports before, and just need a reminder how to do specific tasks. First, you'll learn about creating Line Item reports, both manually and with the wizard. Next, you'll explore creating matrix style reports as well as reports featuring charts and graphs. Finally, you'll learn how to deploy reports to the Report Portal. By the end this course, you'll know the basics so you can start creating reports with SQL Server Reporting Services right away.
Robert C. Cain (arcanecode.com) is a Microsoft MVP, MCTS Certified in BI, and is the owner of Arcane Training and Consulting, LLC. He is also a course author for Pluralsight, team member at Linchpin People, and co-author of 4 books.
Course Overview Hi, everyone. My name is Robert Cain, and welcome to my course, the SQL Server Reporting Services Playbook. I'm the owner of Arcane Training and Consulting. This course is designed for busy IT professionals who need to get up to speed quickly with SSRS, or for those who just need a refresher on a specific topic. Some of the major topics that we'll cover include creating line item style reports with both the wizard and manually, creating matrix style reports, creating reports with charts and graphs, and deploying reports to the report portal. By the end of this course, you'll know the basics, so you can start creating reports with SQL Server Reporting Services right away. I hope you'll join me on this journey to learn SQL Server Reporting Services with the SQL Server Reporting Services Playbook at Pluralsight.
Creating a Line Item Listing Report with SQL Server Data Tools Greetings, this is Robert Cain. In this module, we're going to be creating a line item listing report with SQL server data tools. In this module, we're actually going to be generating two reports. The first report will be using the built-in Wizard to generate the report. Later on in this module, we'll see how to replicate the Wizard report, but doing it manually. What you're currently looking at will be the output of the Wizard. At the top, we have the title, underneath that we have the column headers. In the next line we'll be grouping our output by the customer, so here you can see Tailspin Toys, and we have the subtotal for the quantity and the subtotal excluding tax for this particular customer. Below this, we have each individual invoice that went into making up this particular customer's transaction history. Now, you're probably thinking, gee whiz, Robert. This is really, really ugly. Well, you're right. But don't worry, our next module will be focused on formatting this output to make it acceptable to your business users. Okay, let me switch over to the empty project we created in module one, and we'll start with the Wizard.
Formatting a Report Welcome, it's Robert Cain and in this module, we're going to look at formatting a report. Believe it or not, we're going to take that really, really ugly report we generated manually in the previous section and we're going to turn it into this work of art. At the top, you see we have a nice page header that says Sales Report. Under it, we have our column headers that are nicely colored. Below it, we have the customer name. The customer name now stretches across the top of the details area. It means it's not scrunched up on the left anymore, taking up space. And then we have the details area. As I page through this report, you're going to see that my page header, my column headers, and my customer stay on each page. If I go all the way to the last page, you're also going to see that I have a subtotal and grand total that are nicely formatted, including commas and dollar signs. Below that, I actually have a page footer, which includes the date this report was printed, who actually printed it. We have a dynamic copyright, every time the year advances, it will automatically update that copyright year. And then finally we have the page number. So let's go look at the steps involved to actually make our report as beautiful as this one.
Creating a Matrix Report with SQL Server Data Tools Greetings, in this module we'll be Creating a Matrix Report with SQL Server Data Tools. A matrix style report is one in which the intersections of rows and columns provides the useful information. Here you can see at the intersection of the year 2013 column and row of Alabama, I have my grand total sales. To the left of the year 2013, you'll see a little Plus button. The Plus button allows us to drill down. Here you can see I've now drilled down into the year 2013 and it has broken it out by quarters. I can drill down into quarter one and it shows me the totals for January, February, and March. This is courtesy of the Enable drilldown feature that is part of the Wizard. In addition to drilling down, I can also drill up. You'll have noticed to the left in the rows I've already drilled down into the United States. To the left of the United States is a little minus. I can come over here and click the minus, and it now collapses the individual states into totals for all of the United States. Let's go see just how to generate a matrix report using the built-in Wizard.
Creating a Chart Report with SQL Server Data Tools Greetings. In this module, we'll be creating a chart report with SQL Server data tools. They say a picture is worth a thousand words, something definitely true when it comes to reporting services. Here you can see a chart where I placed our sales per buying group. On the left side, I have my sales total excluding tax and across the bottom, I have my dates so that we can see this plotted overtime. A couple of things immediately leap out at me. First, I see that my biggest sales go to a buying group called NA. NA stands for not applicable or perhaps a better word would be unknown. This conveys to me that I have bad data in my system that needs to be fixed. The next thing I know is my two buying groups of tailspin toys and wingtip toys are running roughly in parallel overtime. This is all information that might be hard to pick out by just looking at a report that is all textual. So let's jump back to our demo project and look at the steps involved to create this chart.
Creating a Report with Both a Chart and Line Item Listing in SSDT Greetings. In this module we'll be creating our report with both a chart and a line item listing in Sequel Server Data Tools. It is a common request for a user to request a report that has a nice summary of the data in the form of a chart at the top of the report and then underneath it, to have the details that went into that chart. In those situations, both the chart and the detailed information will come from the same Dataset. Because this is so common, let's take a look at how to create this particular report in which we have a column-based chart of our sales by buying group at the top and then the individual rows of data that went into making this summation across the bottom.