Filtering data - SQL Server Tutorial

- [Instructor] The reports that we've run so far have displayed all of the records returned by the data set query. You can add a filter to a shared data set, in order to limit the data that's displayed in the report. This is useful for when you want to display different views of your data; say a table and a chart, but you want them to filtered down subsets of the full data set. Lets explore this concept by creating a new paginated report. Then I'll choose to create a new blank report and I'll maximize my screen. For this example I'm going to use the shared data set called Warehouse Stock. And because it's saved to the severe, I can actually skip over the steps taken previously to create a data source and a data set. I'll jump right over to adding a table to the report by going to the Insert tab, clicking the Table button, and then clicking Table Wizard. The first step is to choose a data set. You can either choose it from the list if it appears here or click this radio button and click the Browse button; that'll take you to your Report Severe where you can choose the Warehouse Stock data set we've created previously and press the Open button. Then press Next to create the table. For this table I'm going to use the Stock Item ID field, the Stock Item Name field and the Color Name fields. Once those three are over here in the Values section go ahead and press Next, Next again, and press Finish to create the table. Then I'm going to make some small adjustments. I want to make the Stock Item ID column a little narrower, so I'll click here, then click on the box above it to select that column and make it narrower. The Stock Item Name, I need to make it a little bit wider and I'll just drag that over here to the right. And then Color Name, I'll go ahead and make that narrower. Then I want to make sure the labels here in the header row appear left aligned, so go ahead and select this box on the left side to select the entire header and left align that text. And we'll do the same thing with the data row. Then I'll run the report to see the current state of the data. So here are all the records that the data set query is pulling from the Wide World Importers database. We can filter this down to just a specific color. Let's say that I only wanted this report to display details about the yellow products. Let's go back into Design View and we can make that change and to do that we're going to make the change to the data set. I'll expand the Datasets folder and double click on the Warehouse Stock share dataset. Now, we're not going to modify the query that pulls data from the Wide World Importers database. That's going to stay exactly as it is now, but I will switch over here to the Filters page. This is where we can limit the records displayed in the report. I'll press the add button and for the expression I'll use the dropdown menu to choose the Color Name field. Then we have a listing of different operators we can choose from; the default is equals to. We can say "not equals to", "like", "greater than", or "less than", or "greater than or equal to", or "less than or equal to" and all these other choices. I'll leave it on equals. Then for the value I simply type yellow. Let's say OK to save the filter to the dataset and we'll run the report again. Now, we see a limited set of records, just the ones with the color yellow. So, adding a filter at the dataset level affects the data available to the report. Every data region object added to the report, like this table, will be limited by the datasets filter. As an alternative we can also apply filters to individual data region objects directly. Let's take a look at that by going back into Design view. And first we'll remove the filter from the dataset. Just double click on it again, come back to the filter section, click on the filter and press the Delete button. Then come down and say OK. This time I'll select the table itself. Go ahead and click any cell on the table and press Esc to select the table and you can see over here in the properties that it says Tablix. If you're not seeing the properties sheet you can get to it by going to the View menu and toggling it on here. One of the properties we have access to is called Filters. Let's click there in this box and then click on the ellipsis button to bring up a filter dialog box. Here I can add in the same filter that we applied to the data set. I'll press add. The expression will be the Color Name field. The operator will be equals and the value will be Yellow. I'll say OK. Click off of the report to save that change. And then go back to the Home tab and run the report again. You can see that the report looks exactly as it did just a moment ago. Here's the difference, because the filter is applied to the table, not at the data set level we can add additional tables with different filters. Let's go back into Design view and we'll create one more table that just looks at the Red products. In fact, I can simple make copies of the objects that we already have in the report. I'll click up here at the top to add a title and I'll call it Yellow Products and then click off of the report to deselect it. Then I'll select the table and then press Esc to select the table object itself and not a specific cell. I'll press the Shift key on my keyboard and then click into the title. Make sure you click over here on the blank area, not over the text. With both of these objects selected press Ctrl-C on your keyboard to make copy and press Ctrl-V to paste that copy down. Now, I can use the arrow keys to nudge it into position. Next, I'll deselect everything. I'll change this title to Red Products. I'll click in the table cell and press Esc to select the table. Then in the properties for Tablix2 I'll come back down to the filter, click the ellipsis button, and I'll change this filters value to Red. I'll press OK and deselect everything one more time and run the final report. See, now we can see we have two different tables, one at the top that just displays yellow products and one at the bottom that just displays the red products. Now, we have two different tables, each one displaying a selection from the full dataset return from the database. That's two different ways you can incorporate filters into your reports, either at the dataset level to effect the entire report or at the data object level to effect individual objects separately.