Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Developer and personalized recommendations.Start Your Free Trial Now
- View Offline
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
Skill Level Advanced
We've seen how to filter our datasets and even apply parameters to them. What we haven't talked about yet is how to sort our data, so let's see that now. I am in Report Builder, where I've just created a new blank report, and all I have done is define a simple data source pointing to the regular AdventureWorks database. I'll give this report a simple title, and I'm now going to create the dataset. This will be embedded in my report, and what I'm going to make it do is point to the person table that's in the AdventureWorks database, just bringing back a list of people. It really doesn't matter what data we have.
I just need something to illustrate these sorting concepts. So I'm going to jump into the Query Designer, drill down into that Person table and select a few pieces of data. We'll go for FirstName, LastName, and I'll also select rowguid, which is the generated unique identity for this row, just so we have some other piece of data to take a look at. Now, we've seen that in the Query Designer window there are ways we can apply filters to the data and even parameterize it. There are ways we can auto detect relationships, and even up here which shows the fields I have selected if I wanted to apply an aggregate function, I could actually do that right here. But there doesn't seem to be a way to sort that data.
So I can run this query to test it, and I'm really at the mercy off how the database is internally structured. Here I don't have this information coming back in any kind of a meaningful sort order. That's more to do with the indexes that are defined on this table than anything else. So what if I wanted to have this sorted by first name ascending or last name descending? Well, if you're familiar with SQL, you know that's pretty simple to do, so you might be tempted to add it yourself. One of the ways I could do is just click the Edit as Text button up here and I could be tempted to just add in an SQL ORDER By statement and say order by last name descending or first name ascending.
Now this would work, but it's not the way we would normally do things in Reporting Services. This is another instance where we should bring back the data that we want. Just don't worry about sorting it in the query in the dataset. Instead, we'll sort it in the report, in our data region, our table or matrix. So I'll click OK. That's my dataset defined. Now I'll jump into the Insert tab and just insert a simple table here. Choose that dataset. I'm going to drag on FirstName and LastName.
I could drag on rowguid. It really doesn't matter if I do or not. I'm not bothering with grouping them together in any category. I'm just having a list of people. And I'll except all the other defaults and just add that to the report. Go ahead and run this and we've got our data, but right now I'm at the mercy of how this database is internally structured. I might get lucky, but I want some other behavior. So I'm going to decide to sort these by last name ascending, go back into Design view, and what I want to do is select the table, so clicking somewhere inside the table to get the gray bars along the top. I'll then select the top corner to make sure that I have Tablix selected in my Properties window, and then open the Properties Pages. And over here we have the Sorting option.
So it's saying I don't care about how this actually comes back from the database; I'm going to sort it in our table here. If I click the Add button, I then get the option to Sort by, and I'll say LastName, A to Z, so ascending. That's fine. Click OK. That's it. We now have a LastName ascending applied to the table. If I wanted to be explicit about multiple levels of sorting, I'd go back into the table, into the Property Pages, and add multiple levels of sorting here, making sure I sort by last name and then by first name and then by middle name and so on.
But it's just as easy to change the order of that, to make it descending. I'll try that again. No surprise here. Now we're descending last name order. And this might work for a lot of what you present, but what's also very useful is to be able to apply interactive sorting, to let the end user sort the data whatever way they want to. So let's see how to do that.