From the course: SQL Server: Reporting Services

Add a data table with the wizard - SQL Server Tutorial

From the course: SQL Server: Reporting Services

Start my 1-month free trial

Add a data table with the wizard

“

- [Narrator] With the data source and the data set both defined, the next step in building a report is to place that that collection of records onto to page. The most common way to do that is to create a tabular region in the report's design. On the insert menu of the report designer are a couple of different elements that we can add into the report. Tables are grouped together with matrices and lists in a section called Data Regions. Let's go ahead and press the table button, and you'll have two different options to choose from. Either use the wizard to help set up the table, or place a blank table into the design of the report, and you'll have to hook the pieces together on your own. I'm going to go ahead and use the table wizard. That'll open up the new table or matrix window. First, we need to choose a data source. We created one embedded in this report called People, so go ahead and select that and press the next button at the bottom of the screen. Then we need to arrange the fields into the table structure. If you've ever built a pivot table in Excel, then this is probably going to look pretty familiar. On the left are the different columns that are being returned by the data set. We just need to drag and drop them into these 3 different areas, column groups, row groups, and values. Values is going to be the most common, and that'll simply create a standard data table. You can drag each field one at a time, or you can click the first one and Shift + click on the last one to select all three, and then drag them all as a group. With all of the fields in the values section, go ahead and press the next button. Here, we get to choose a layout, but because we didn't include any fields in either of the grouping sections, there's actually nothing that we need to do on this screen. Go ahead and press next again. Then, we get a small preview of the table, and you can press the finish button to exit out of the wizard and place the table object into the report layout. The table comes in selected, and you can resize it by dragging the grab handles spaced around the edges. I'm going to click this middle one on the right-hand side and drag it to the right to make the table wider. Notice that as you resize elements, you'll get some snapping guides. It'll help you align things on the page. Now at this point, our table doesn't look like much, and that's because we're currently viewing this report in design view. Here, we just see the outline of a data table, but none of the actual data. The top row will be our header information, and the row below, written in square brackets, represents a typical data row. If you click on the table, you'll get these gray bars on the left-hand side and across the top. In the box to the left of the data row, we see three lines. This indicates that the row will be copied for every record that's returned by the data set. Essentially, our table is going to grow vertically to accommodate as much data as we feed it. Go ahead and click into a blank area of the report to deselect the table. To view the report populated with data from the database, click on the run button on the home tab of the ribbon. Notice the shortcut key is F5. After a moment of processing, it'll pull the data from the database and populate our report. You can then use the buttons on the run tab of the ribbon to navigate through the different pages of the report. Now, this report could use a little bit of help with its layout. The email address columns need to be a little bit wider in order to accommodate the data without wrapping onto two different lines, but the basics are in place and we're getting the data to display, and that's always step number one. When you're done looking at the report, you can press the design button to return back into design into design view. Let's click at the top to add a title to the report, I'll call mine People. When you're done typing, click off to a blank area of the screen to deselect it. If you press Enter, it's simply going to add a second line to that text box. Then we can resize the columns in the table by selecting the table, then we can click on the gray bars at the top, and I'll reduce the width of the full name column, as well as the phone number column, and I'll make the email address column a little bit wider. Once again, I'll click off the report to deselect everything. Let's run this report again and check out the difference, and it's looking a lot better. I'll go back into design view, and then I'll press the save icon at the top of the screen to save the report. The Save as Report window opens up and defaults to the report server location. You can also save your report definition file to your local computer using the links along the left-hand side of your screen. However, saving the report to the server itself will make it available through the web portal. Let's give it the name People, and I'll press the save button. Now we can close the report builder and return back to the web portal, just like with Management Studio, you might have to refresh your view by reloading the webpage in your browser to see the change. And here's our first report. You can click on it here in the portal to view it right inside of your browser, and you can use the scroll bar on the right-hand side of your screen to scroll through the different records. When you're done, click on the home link in the breadcrumb navigation to return to the main page. And that's how you use a data set to populate a table in Report Builder.

Contents