• Article

Create a Basic Table Report with a Wizard (Report Builder 2.0 Video)

Applies to:Microsoft SQL Server Report Builder 2.0

Author: Maggie Sparkman, Microsoft Corporation

Length: 00:12:52

Size: 9 Mb

Type: WMV file

Watch this video
Related help topics:

Tutorial: Creating a Basic Table Report (Report Builder 2.0)

Designing and Implementing Reports Using Report Builder 2.0
Additional Web pages:

Install Microsoft SQL Server 2008 Report Builder 2.0 from the Download Center on www.microsoft.com.

Video Summary

This video demonstrates how to create a report with a table by using the New Table wizard, apply rich formatting to the text in the report, and run the report in Report Builder and from Report Manager.

Video Transcript

Hello, my name is Maggie Sparkman. I write about the Microsoft SQL Server Report Builder 2.0 product.

In this video

I'm going to demonstrate how to:

  • Use a wizard to design a report with a table:

    • Choose the report data source: AdventureWorks2008.

    • Create a report dataset: a query that returns sales data.

    • Arrange fields and groupings.

    • Choose the layout, style, and colors.

  • Add a title and apply rich formatting.

  • Add a page number.

  • Format numbers as currency.

  • Widen the columns.

  • Run the report in Report Builder.

  • Save it to the report server.

  • Run the report from Report Manager.

I'm also going to point out the various panes, design surfaces, and tools that you can work with in Report Builder 2.0.

To do this video

I need:

  • Report Builder 2.0 installed.

  • A report server with the AdventureWorks2008 sample database installed from CodePlex.

  • Publish permissions for the report server.

Open Report Builder 2.0

  • On the Start menu, click All Programs, click Microsoft SQL Server 2008 Report Builder 2.0, and then click Report Builder 2.0.

It automatically creates a new blank report.

Notice that you see No current report server in the lower-left corner.

Create a new table or matrix by using the wizard

  • Click the Table or Matrix wizard link in the center of the screen.

You can also access the wizard from the Insert tab on the Ribbon.

  • Point to Matrix and click Matrix Wizard, point to Table and click Table Wizard, or point to Chart and click Chart Wizard.

The wizard walks you through creating a table or matrix, including the creation of the needed data sources and data sets.

Choose a connection to a data source

  1. I click Browse.

  2. In Name, I type the URL for the report server: http://video1/reportserver.

  3. I click Open.

  4. I double-click the Data Sources folder.

  5. I click the PersonSales data source.

  6. I click Open and return to the Choose a connection to a data source page of the wizard.

  7. The PersonSales data source is selected.

I click Next to go to the query designer to build my dataset (query).

Design a query

This is a graphical view of my data source, AdventureWorks 2008.

I can look at the structure of the database – there are tables relating to human resources, people, production, purchasing, sales, and so on.

I can add specific fields or whole tables to the query designer by clicking the check boxes to their left.

But this time, I’m going to import a query.

  1. I click the Import button and navigate to the text file on the desktop.

    Note that when I first open the Open Report dialog box, it's filtered for RDL files, so I don't see my text file. I have to set the Items of type box to All Items (*.*) to see it.

  2. I click SalesQuery.txt, and then I click Open.

Here is a copy of that query:

SELECT

PC.Name AS ProductCategory,

PS.Name AS SubCategory,

DATEPART(yy, SOH.OrderDate) AS OrderYear,

'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,

SUM(SOD.UnitPrice * SOD.OrderQty) AS SalesAmount,

Sales.SalesTerritory.[Name] AS Territory,

Sales.SalesTerritory.[Group] AS Region

FROM

Production.ProductSubcategory AS PS JOIN

Sales.SalesOrderHeader AS SOH JOIN

Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID JOIN

Production.Product AS P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID JOIN

Production.ProductCategory AS PC ON PS.ProductCategoryID = PC.ProductCategoryID JOIN

Sales.SalesTerritory ON SOH.TerritoryID = Sales.SalesTerritory.TerritoryID

WHERE

(SOH.OrderDate BETWEEN '1/1/2003' AND '12/31/2004')

GROUP BY

DATEPART(yy, SOH.OrderDate),

PC.Name,

PS.Name,

'Q' + DATENAME(qq, SOH.OrderDate),

PS.ProductSubcategoryID,

Sales.SalesTerritory.Name,

Sales.SalesTerritory.[Group]

I click Next to continue and choose how to arrange the fields (column groups, row groups, and values).

Arrange fields

You can arrange your fields in the Row groups, Column groups, and Values boxes. The Values box is the only one that has to have a field.

Sometimes double-clicking a field will place it in the correct box, but sometimes not. If it doesn't go to the box that you intended, you can drag them from one box to another. If you decide you don't want a field in any box, just drag it away from the wizard.

  1. I drag the SalesAmount to the Values box.

  2. I drag ProductCategory and then SubCategory to the Row groups box.

  3. I drag OrderYear and then OrderQtr to the Column groups box.

  4. I click Next to continue and choose a layout.

Choose Layout

I use the defaults in the wizard -- Show subtotals and grand totals and Blocked, subtotal below. The wizard is useful because it gives you an idea what the alternatives look like. For example, Stepped, subtotal above is useful if you will have a lot of columns and are concerned about the width of your report.

I also accept the Expand/collapse groups default. When I first run my report, the row and column groups will be collapsed, and I'll be able to expand them if I want to. This way I can see my whole report at once.

Click Next to choose style.

Choose style

Check out the alternatives. I’m going to use the default, Ocean.

No matter which style you select in the wizard, you can change colors and fonts (and everything else) after you complete the wizard.

Click Finish to view the report in Design view.

In Design View

Let's look at Design view.

I can zoom in and out by using the zoom bar in the lower-right corner.

I have a number of panes around the design surface. If you don't see them, click the View tab and then select the check boxes for the different panes:

  • The Report Data pane shows the data source and dataset for the report.

  • The Properties pane shows the properties for whatever part of the report you select. Currently it showing the properties for the tablix I created.

    A tablix is a hybrid between a table and a matrix, with characteristics of both.

  • Row and column groups are displayed in the Grouping pane. When you click them in the Grouping pane, they’re also selected in the design surface.

    Grouping indicators appear at the sides of rows and the top of columns.

Other items to notice in the design surface:

The Current report server is now named in lower-left corner: http://video2/reportserver.

The Execution Time field is already added to the footer. This is a built-in field, from the Built-in Fields folder in the Report Data pane. There are other built-in fields such as page number, which I can add to footer.

Add a title

Notice the top of the reports has a field that reads Click to add title.

  1. I click there and type: Sales by Year Basic Table Report.

  2. I select the Basic Table Report text and click the Font Size button and the Font Color button make it smaller and blue, to go with the blue theme.

  3. I add a paragraph break between Year and Basic to put the text on two lines.

  4. I select Sales by Year and click the Bold button and the Font Color button to make the text bold and a different shade of blue.

Run the report

I run the report.

  • On the Home tab, I click Run.

Note a few things:

  • The columns and rows are collapsed. I can expand them by clicking the plus signs.

  • The numbers look funny: the column is too narrow for the numbers, and there are four places after the decimal, which doesn't look like a currency format.

  • We can see the execution time and page number.

  • It shows the current report server in this view, too.

  • The functionality available on Run ribbon in Run mode—zoom, navigating to different pages, printing, page setup, exporting to a number of different formats

To go back to Design view to fix the number formatting, I click Design on the Run tab.

Improve the number formatting and layout

Widen the columns

  1. I select the whole tablix, so I get the gray column handles on the outside border of the tablix.

  2. I drag the double-headed arrow to make the column wide enough for the larger numbers.

Format the numbers as currency

  1. I select all the number fields at once by selecting the upper-left number field, holding down the SHIFT key, and then selecting the lower-right number field.

  2. I click the Currency button.

Note that I can go back and forth between seeing the placeholders and seeing sample currency values.

Run the report again

  • I click Run on the Home tab.

Note that the columns are a better width, and the numbers are formatted as currency.

Save the report

In either Design view or Run mode, I can save the report.

  1. I click the Report Builder button and click Save As.

  2. I save it to the same report server where the data source is stored.

  3. I call it SalesByYear2.rdl.

View the report from Report Manager

  1. I open Internet Explorer.

  2. In the Address bar, I type:

    http://video1/reports

  3. I open the Video1 folder.

  4. I click my new report, SalesByYear2.

It renders and looks the way it did inside Report Builder 2.0. We can expand the columns and rows. It has the same functionality that we saw in Report Builder 2.0:

  • Navigate to different pages.

  • Zoom in and out.

  • Export to different formats.

  • Show the execution time and page number in the footer.

  • Refresh the data.

Conclusion

In this video, you saw how to create a report with a table by using the New Table wizard, how to apply rich formatting to the text in the report such as changing color and size and setting currency formatting, and how to run the report in Report Builder 2.0 to preview it, and how save it to and run it from Report Manager.

Thank you for watching this video. I hope that you have found it useful, and will return to the Web site to view other Microsoft SQL Server videos. 

If you'd like to provide feedback on this video, try the Click to Rate and Give Feedback link in the upper-left corner.