Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,987 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
Databases are great for storing our raw data, but usually raw data isn't all we want to see. We want to see calculated values on our reports. We want to see the total sales for last week, or the average daily bug submissions for this quarter versus the same quarter last year, or perhaps the number of products we have right now with a status of Out of Stock. But this kind of information, these kinds of numbers, would not and should not be stored in the database directly because we can figure them out on the fly. We can calculate these numbers, and we will use functions to perform these calculations.
Common functions including totaling or summing numbers together, counting them, averaging them, minimum or maximum, first and last. So let's say I want to create a report in Reporting Services with the total sales for last week. Now, I could get to this total and add these numbers together in two different places. First, when I write the dataset or query or SQL I use a function that asks the database to total up all those individual amounts and just return with one value to the report, and then all the report does is spread that single value out.
But another option is I don't ask the database engine to do that calculation. I ask it to return all the values to the report, and I ask the report to do it, where the query itself is simpler. We get all the numbers back and in the report we total them up. So which way is better? Now you might think that it's a coin toss; it doesn't matter. Or if you are a developer, you're probably tempted to say it's best to do it all in the SQL, all in the query. Let the database engine do the heavy lifting. But actually, in Reporting Services it's often preferable to do this kind of calculation in the report itself, because we might want that report to not only show the total but to also be able to drill down into the individual data.
We can't do that if all we are fetching from the database is one number. But I am getting ahead of myself. So let's see an example. So I am in my Reporting Services Report Manager. I am going to click Report Builder and create a new report. Like most of them that I'm doing early on, I will just use the Blank Report option here. And the first thing I need of course is a data source. I am going to point this to AdventureWorks light. It will be, again, a connection embedded inside this one report. Selecting SQL Server, I'll use the Build button and point it to my SQL Server, which I am just going to use AdventureWorksLT.
And now create a dataset. What is the data that I am interested in here? Well for this example I am just interested in sales information, sales totals. So I am going to call this dataset SalesInfo. It would be embedded in the report. We will use the data source I just created and then I will jump into Query Designer. Now in the AdventureWorksLT database, that information I'm looking for would be in the Sales Order Header table, and for the purposes of this example, all I am really interested in is the TotalDue.
So selecting that one column, I could run the query here and we can see some of the results coming out here. Sure, normally I'd have more data than this. But right now I am staying super focused on using a few different functions, and I can perform several just from these results. I could get a total by adding up all these values. I could count all the rows returned to get the total number of orders, and I can average them. So I am just going to be done with this option. So I will click OK and my dataset is done. So now I'm going to do something with the reports. I will just give it a title here, of Using Functions.
And instead of adding a table, I am actually just going to add three pieces of information. First, what I am going to do is have a label just to show what they are. So on my Insert section of the Ribbon, I am going to grab a normal text box here and with that selected, I should have little crosshairs that I can come down and just draw an area by clicking and dragging on this layout. I am going to show the total sales, and I am also going to show the number of orders and the average order amount. Again, this right now is just a label.
I am also going to improve the font a little bit here, take it up to, say, 20. Align this on the right-hand side. I am going to drag it a little bit wider and as I do that, it will move out the actual size of the page, so we can see it properly here. In fact that font is a little big. I will just drop it down to 18 and that will work. Now, I need some actual values that would be calculated when this report is run. So where do I get them from? If I look over here in the Report Data section in Report Builder, I can see that my data set is showing up as SalesInfo with just one field here, of TotalDue.
So what actually happens if I drag that over, because you can drag from the Report Data panel? Now, do be careful if you are dragging over existing elements in the report layout, like text boxes here; it can often upset things, so it's often a better idea to kind of move it around so you don't move over the top of another element. So I am just going to let go. And it's dropping in a textbox here with this little Expr for expression. So what? What does that actually going to do? After all, if I just dragged on one column name, is it going to repeat them all? We could have a thousand different orders so what will this show? Well let's find out. I am going to go up here and click Run.
Now actually here, it's showing here the Total Sales 956303.5949, and that actually is the total amount of orders. It's adding all those total due amounts together. The question is, why? Why did it do that rather than just write out the first one or right out the last one or just write out some random single value? Well, let's find out. Going back into Design view I am going to highlight that. And there is a couple of ways I could get to this, but with that selected, I am going to come over here to my Property Pages and open that up, because here is where we get the easy way of looking at the value.
It's saying it's getting it from an expression. And if I click the little Expression Builder button here, I can see that we have got this value being written in this expression, this calculation, which says =Sum(Fields!TotalDue.Value, "SalesInfo"). Okay, the format might look a little weird here, but what we are basically saying is add together all the values in that Total due column for the dataset called SalesInfo, and it's this word, Sum, that is summing them all together. This is Report Builder's best guess of what I probably want, seeing as I'm not using a table here to repeat multiple columns.
So this actually works in this case. Well, what if I wanted something else? What if I wanted the average instead, or the count of them? Well, I can always use that as a starting point. So I am going to grab the TotalDue again from my Report Data section and drag it over here and put it beside, say, Number of Orders. Now, right now this will do exactly the same thing. If I clicked into the Run mode, we'd see the same number. But I can use this as a usual starting point. I'll grab this one, open up the Property pages, and open up the Expression builder for the value. And instead of Sum, I'm going to just change that to Count.
Now, how do I know what this is going to do? What's the difference between Sum and Count? Well if you have been working with SQL, you are probably familiar with Sum and Count, but if you are not, Sum is the total. It will add all the individual numbers together and count will just add the number of rows together. How many Total Due values do we have? It's not interested in what's in each one; it's just going to count them all together. Now, if I look down below here in the Expression Builder, I can see that there is a whole bunch of different options. We have things like datasets that are available here. We have Variables.
Well, there is nothing in here, Fields, Built-in Fields like PageNumber, TotalPages, ReportName, and so on. And there is a list of common functions. The most typical ones we are using with data are in the Aggregate or Grouping functions. This is where we have the option for Averaging, Counting, First, Last, Maximum, Minimum, Summing, and so on, and even some examples here of how you might use them in the expression itself. You could type these in or you can actually double-click any of them and it will jump them up into this Expression Builder area.
We are going to see more about expressions later on, but for right now this will do. I am saying =Count instead of =Sum. I click OK, I click OK, and we run this again. Now it's saying Total Sales, 956303, Number of Orders is 32. Well, let's add one more to add the Average Order Amount. And instead of dragging and dropping, I will show a different way of putting this one the page. I'm going to jump over to my Insert section, insert a text box, and with that cross- hairs, I am just going drag in a place that I want this to be.
This is the Average Order Amount. And if I was confident enough in my expression building, I could actually just type in that expression directly in here. And if I click off, we are actually going to see that change the expression. Let's see if that worked. And that seems to be about right, regarding about 29894. So I can't type that directly in there, but again, I could just select that and use my Property pages to get to that expression if I wanted to construct it myself inside this window. And while you're learning how to work with expressions, it's often not a bad idea to drag something on here to use as a starting point and then change it rather than invent it all from scratch.
This is still looking a little bit ugly, so let's do a bit of alignment and reformatting here. I am going to grab these three text box areas and just move the font up a bit to, let's say 16. I may have drag them a little bit wider just so that number will fully appear. Also in the Home tab, I should be able to align the left-hand side of them and I will select to left align them as well. The main thing that we are missing here in terms of formatting is that the numerical ones here look pretty ugly here. We have got total sells 956303.5949.
This is meant to be some kind of currency. Well back into Design view, what I am going to do here is--grabbing this to get myself a bit more room-- is select that first one which should be a dollar amount and from the Home section of the ribbon, here I have got this Formatting section. How is this being presented? And right now it says Default. What I am going to select is Currency. I am also going to select that for the third one, which is the Average Order Amount. From Default to Currency. Run that again. And now we have something that's a bit more presentable.
Okay, I could tidy this up a bit more with alignment, but this should do the trick for right now. If I wanted to do more with the formatting, I could do a little bit more with any of these selected. If you start to mouse over the different buttons here, you can see things like placeholder styles. What that would change it from is this Expr to, for example, Sample Values, 12,345.00. And then I can start manipulating things like increasing the decimal point or decreasing the decimal point, and I am getting a little preview of how that would actually look here. But this should do the trick for right now.
As we go forward, we will start combining these functions with more complex report elements like tables and matrices.
Note: These tutorials are applicable to both the 2008 and 2012 versions of SQL Server.
- Understanding the elements of a report
- Grouping table regions
- Joining data from multiple tables
- Displaying data in a matrix
- Customizing report parameters
- Filtering and sorting data
- Creating charts
- Adding sparklines and data bars
- Creating at-a-glance reports with indicators
- Using Maps in Reporting Services
- Configuring report security
- Printing and exporting reports