#Report builder 3.0 tutorial how to
#Report builder 3.0 tutorial series

Also, the OrderYear and SubcategoryName fields are listed just as they appear in the dataset, except theyРІР‚в„ўre enclosed in brackets to ensure that the name in its entirety is used correctly, should it contain spaces or special characters. ThatРІР‚в„ўs the thick grey border to the left and at the top. First, when youРІР‚в„ўre working within the matrix, the two-sided frame becomes active. There are several things to note about the matrix at this point. Your matrix should now look similar to the one shown in Figure 2.

Drag the OrderQty field from the dataset to the Data cell of the matrix.

Drag the SubcategoryName field from the dataset to the Rows cell of the matrix.

Drag the OrderYear field from the ProductsSold dataset in the ReportData window to the Columns cell of the matrix.

To set up your matrix, take the following steps: In other words, the number of bikes sold will be broken down by year and quarter as well as by subcategory and product. The OrderQty field, which reflects the number of bicycles sold with each order, will provide the values to be aggregated. The top level of the date hierarchy will be the OrderYear field, and the top level of the product hierarchy will be the SubcategoryName field. For this report, weРІР‚в„ўll include two groups: one based on dates and one based on products. The simplest way to do this is to add the top-level field from each group hierarchy. Once youРІР‚в„ўve added the matrix object, you can start adding the fields that define the columns and rows-and that also define the initial groups.

YouРІР‚в„ўll have plenty of opportunity to configure it as youРІР‚в„ўre adding other components. At this point, donРІР‚в„ўt worry too much about how you position and size your matrix. When you let go, your design surface should look similar to the one shown in Figure 1.

The cursor should now appear with a tiny table icon.Р”В¬lick the left mouse button and drag the icon to the spot where you want to position the bottom-right corner of the matrix. Move your cursor to the report design surface, to the spot where you want to position the top-left corner of the matrix. Go to the Insert ribbon, click the Matrix icon, and then click InsertMatrix. If youРІР‚в„ўve read the other articles in this series, you already know the basics of adding an object to a report. The first step is to add the matrix to your reportРІР‚в„ўs design surface and then to add the initial fields, so letРІР‚в„ўs get started with both of those tasks. Once youРІР‚в„ўve created your data source and dataset, youРІР‚в„ўre ready to create a matrix. The ProductSubcategoryID value, specified in the WHERE clause, determines that only bicycles are returned and not other products. ProductSubcategoryID BETWEEN 1 AND 3 Р”Р‡or each order, the SELECT statement retrieves the number of bicycles sold, the subcategory and product names, and the year and quarter the bikes were ordered. Finally, I configured the dataset with the following T-SQL query: I then created a dataset that uses the AdventureWorks data source to retrieve the necessary sales data. On my system, I created a data source that connects to the AdventureWorks2012 database on a local instance of SQL Server 2012. If you want to create this matrix on your own system, youРІР‚в„ўll need to create a Report Builder report and add a data source and dataset that retrieve AdventureWorks data. It will also group data by the year and quarter the bikes were sold. The matrix will group data by product subcategory (types of bikes) and product names.

In this article, weРІР‚в„ўll add a matrix to a report that shows the number of bicycles sold by the AdventureWorks bicycle company (MicrosoftРІР‚в„ўs fictitious company used to provide sample SQL Server data). You simply drag the fields you want to include from your dataset to the appropriate row or column, and then format the matrix components as best fits your needs. In fact, Report Builder automates much of this process. Repot Builder automatically aggregates the data based on how youРІР‚в„ўve defined your groups across the rows and columns. The years would be at the top of the hierarchy and the months at the bottom. For example, one of your groups might be based on dates, starting with years, then quarters, and finally months. You set up the matrix by defining groups of data in a hierarchical order.

#Report builder 3.0 tutorial how to

In this article, youРІР‚в„ўll learn how to add and configure a matrix.Р”Р„ matrix is a type of table that lets you aggregate data across both rows and columns, similar to a crosstab or pivot table.

#Report builder 3.0 tutorial series

In the first four articles of the Report Builder 3.0 series ( article 1 | article 2 | article 3 | article 4), you learned how to add tables, charts, and maps to a report and configure their properties. Report Builder 3.0: Adding Matrices to Your Reports - Simple Talk