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 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 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
0 kommentar(er)
