DESIGNING REPORTS in VB.NET

Create Data Matrix in VB.NET DESIGNING REPORTS

who handle the Canada region. In addition, the user has decided to see the monthly sales data for 2004, while the sales data for 2003 is displayed consolidated. Let s discuss the essential points of this report design process. Setting up the report data First, we set up the dsTerritorySales report dataset with the following SQL statement:

SELECT ST.TerritoryID, ST.Name AS Territory, SP.SalesPersonID, C.LastName + N', ' + C.FirstName AS EmployeeName, SOH.OrderDate AS Date,SUM(SOD.UnitPrice*SOD.OrderQty)AS Sales, COUNT(SOH.SalesOrderID) AS NoOrders FROMSales.SalesOrderDetail SOD INNER JOIN Sales.SalesOrderHeader SOH ON SOD.SalesOrderID = SOH.SalesOrderID INNER JOIN Sales.SalesPerson SP ON SOH.SalesPersonID = SP.SalesPersonID INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN Person.Contact C ON C.ContactID = E.EmployeeID INNER JOIN Sales.SalesTerritory ST ON SP.TerritoryID = ST.TerritoryID WHERE (SOH.OrderDate BETWEEN @StartDate AND @EndDate) GROUP BY ST.TerritoryID,ST.Name,SOH.OrderDate, SP.SalesPersonID, C.LastName + N', ' + C.FirstName ORDER BY ST.Name, SOH.OrderDate

Since the matrix rows of this report summarize the information in territories and salespersons, the query statement provides these groups. Drilldown per year is achieved with expressions based on the OrderDate field inside the report. The query also takes start and end dates as parameters. DESIGNING CROSSTAB (MATRIX) REPORTS 135

Adding the matrix region Next, we switch to the layout mode and drag and drop the matrix region into the report, as shown in figure 4.29. The upper-left cell of the matrix region is the corner cell. You can use it to display a title for the matrix region. In our case, we ve used that cell as a container for the AWC logo image. The matrix data region makes defining the rows and columns in the crosstab easy. To group the data into rows and columns, you must define the row and column dynamic groups.

Figure 4.29 You define dynamic and static matrix groups by dragging and dropping dataset fields into the respective areas.

Defining dynamic groups Dynamic row and column groups can nest within other dynamic row and column groups. You add dynamic groups by dragging and dropping dataset fields to the Rows and Columns areas. The Report Designer displays a helpful bar hint when you drag the field over the row or column headers to show you valid places where you can drop the field to nest the new group inside an existing group. For example, to drill down by territory and salesperson, we drag and drop the Territory and EmployeeName fields from the dsTerritorySales dataset into the Rows section. As a result, the matrix region creates two dynamic row groups, which we rename rowTerritory and rowEmployee, respectively, as shown in figure 4.30.

Figure 4.30 You can achieve data drilldown with the matrix region by creating nested column and row dynamic groups.

The Columns section is little bit trickier. Here we need to define dynamic columns for years and months. To achieve this we create two column groups, colYear and colMonth, and set them to be based on the Fields!Date.Value.Year and Format(Fields!Date.Value, "MMM") expressions, respectively. Because the Date field from the report dataset is of type DateTime, you could use the methods and properties of the .NET DateTime structure to retrieve the year and month portions. We also format the month value to show the abbreviated version of the month, for example, Jan for January. Defining static groups To display the actual data (intersected cells for dynamic row and column groups), you define static rows or column groups. You are not restricted to one static group. When you add more than one static group under a given dynamic column, the dynamic header splits to accommodate the new group. To demonstrate this, let s drag and drop both the Sales and NoOrders dataset fields to the matrix region data section, so that users can see the sales dollar amount alongside the number of orders placed per territory segment. To get the expand/collapse magic working, we have to change the visibility for the rowEmployee and colMonth groups. Figure 4.31 shows the visibility settings for the rowEmployee group. These settings make the rowEmployee group invisible initially. Only when the user expands the higher-level Territory group does the Employee group become