This is part two of a series about Report Builder 3.0.

In this post, I’m going to introduce you to one the Table or Matrix Wizard.

SQL Server Report Builder 3.0 has a built-in Table or Matrix Wizard. It will walk you through all the necessary building blocks for a table or matrix, and generate a report layout for you. This is great if you’re not comfortable with starting a report from scratch, or want to quickly create a report.

The Table or Matrix Wizard can be accessed in two ways. It will appear on the splash screen when you open Report Builder. It can also be found under File > New.

The Wizard

First, choose a dataset. I select “Create a dataset”.

I created a new dataset to connect to AdventureWorks2008R2.

The Design a Query screen can be intimidating at first glance. There is a lot of information in one place. It’s easy to understand once it’s broken down, though.

On the left, the Database View column shows the database, sorted by schema. Expand a schema to see its tables, views and stored procedures. Expand a table or view and click on the box in front of a column to add it to the report. Expand stored procedures, and check one to use it in the report.

The right pane has three sections: Selected Fields, Relationships and Applied Filters.

Selected Fields shows you the fields that you’ve selected for use in this query. In the top right, use the X to delete a column, or the arrows to reorder your selections. You can use Group and Aggregate to select a field to aggregate and other fields to group by.

Relationships shows the relationships between tables. You can use Auto Detect to find relationships based on primary and foreign keys defined in the database. You can also edit an existing relationship, or create one yourself.

Applied Filters allows you filter the results of the query.

Hint: Prefer writing T-SQL? Click Edit As Text to do so.

Hint: Already have a query written and saved as a .sql or .rdl file? Click Import to use it.

Click Run Query to run it and see you results.

The next screen, Arrange Fields, allows you to add groups to your table or matrix. A table can have row groups. A matrix can have row and column groups. Values is the fields from your dataset that will appear on the report. You can apply aggregations to these. Click on a field and drag it to the appropriate box.

Hint: adding a numeric field to Values will automatically Sum it. Click the drop-down arrow and uncheck Sum if you do not want aggregation.

In my example, I do not want any grouping, so I only add Values.

Choose the Layout shows you how the report will look. Don’t like the order of the columns or rows? You can go back and rearrange if needed.

Choose A Style will apply a set of colors and fonts to the report. (Anyone that has used the wizard in Report Designer will be familiar with these.) If you want no colors, choose Generic.

The report is created and displayed on the main screen. It’s now able to be edited further, or run.

Clicking Run will run the report and display the results. You can view, print and export from here.

Click Save to save the .rdl.

Deploying

Is that perfect, brand new, shiny report ready to be turned loose in the business? It’s time to deploy it!

In the bottom left corner, you should see “No current report server.” Click Connect. Enter the Report Server URL.

Go to the File menu and click Publish Report Parts.

I chose Publish all report parts with default settings.

I navigate to my report server. I have a new folder listed, Report Parts.

In this folder, there is one item: the tablix that I created in this report.

If you choose Review and Modify, it lets you select which parts of the report to publish. You can add a description, and also choose which folder to publish them to.

The Table or Matrix Wizard is a powerful tool to help you quickly design reports in Report Builder.

Next, we’ll look at the Chart Wizard!