Sample Report - Company Sales
The Company Sales report displays Adventure Works Cycles sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.
This report uses a single dataset named Sales, which uses the AdventureWorks shared data source. The query for this dataset is based on several tables from the AdventureWorks2000 database and contains a query that retrieves sales data by product and date. The dataset returns the following fields.
|ProdCat||Name of product category|
|SubCat||Name of product subcategory|
|OrderYear||Order year, calculated in query based on order date|
|OrderQtr||Order quarter, calculated in query based on order date|
For information about using datasets, see Querying a Data Source.
The AdventureWorks shared data source contains connection information for the AdventureWorks database. By default, this data source uses the AdventureWorks2000 database on the local computer, but you can change it to use a database on a different computer. Because the data source is shared, the same connection information can be used by all reports that use the data source. For information about data sources, see Connecting to a Data Source.
Layout and Report Features
The general layout of this report consists of a single text box containing the report title, and a matrix with multiple groups. The cells within the matrix contain visibility options that provide drilldown to the report user. The report also uses a background image behind the matrix.
The data for this report is displayed using a matrix data region named SalesMatrix which uses the Sales dataset. This matrix displays the ProdCat and SubCat fields on rows, OrderYear and OrderQtr fields on columns, and the Sales field in data. All rows and columns are dynamic; that is, the rows and columns depend on the contents of the database. For information about matrices, see Adding a Matrix.
The matrix also contains drilldown capability. This is accomplished by applying visibility options to the inner groups in the matrix. The groups are initially hidden, and toggles are placed on groups that point to text boxes belonging to the outer groups. For example, in this report, the ProductSubcategory group is initially hidden, and its visibility is toggled by the Category text box. When the user clicks on the toggle image (+ / - sign) for the category, subcategory rows appear. For information about drilldown and item visibility, see Hiding Items on a Report.
Note The visibility information is set on the matrix group, not the text box that contains the field. Setting visibility options on a group will show and hide entire groups of rows. Setting visibility options on text boxes will show and hide only the text within the text boxes.
The Company Sales report contains an embedded background image named logoback. The image is embedded, or stored, as encoded data within the report definition. A property is set on the report body to repeat that image across the background of the report. For information about embedded and background images, see Adding an Image.