Create Matrix Report using ReportViewer in ASP.NET 2.0

• Download source code - 9.34 KB

Introduction

This article provides a step-by-step demo on how to create matrix reports in local mode with the ReportViewer control in ASP.NET 2.0.

Matrix reports, commonly known as Pivot tables or Crosstab reports, summarizes data in more than one dimension. More details on pivot tables can be found at http://office.microsoft.com/en-us/excel/HA010346321033.aspx.

In this demo, we will create two matrix reports. The first one provides the details of the products sold for a given month, and the second one provides the products sold by each sales person for the given month. Below is the screenshot of the report.

Getting started

We can start by creating a website or a web project in Visual Studio 2005/2008.

• Step 1: Create the object model.

Create the Order, OrderList classes in the App_Code folder. The Order class has the purchase date, product type, sales person, units, and the margin amount of each order. The OrderList class method GetOrderList() returns orders needed to create the reports. Data is hardcoded in the GetOrderList() method, and can be replaced with your own code to get data from the database.

public class Order
{
    private DateTime _PurchaseDate;
    public DateTime PurchaseDate
    {
        get { return _PurchaseDate; }
        set { _PurchaseDate = value; }
    }
    private string _ProductType;
    public string ProductType
    {
        get { return _ProductType; }
        set { _ProductType = value; }
    }

    private string _SalesPerson;
    public string SalesPerson
    {
        get { return _SalesPerson; }
        set { _SalesPerson = value; }
    }

    private int _Units;
    public int Units
    {
        get { return _Units; }
        set { _Units = value; }
    }

    private int _MarginAmount;
    public int MarginAmount
    {
        get { return _MarginAmount; }
        set { _MarginAmount = value; }
    }

    public Order(string purchaseDate, string productType, 
           string salesPerson, int units, int marginAmount)
    {
        _PurchaseDate = Convert.ToDateTime(purchaseDate);
        _ProductType = productType;
        _SalesPerson = salesPerson;
        _Units = units;
        _MarginAmount = marginAmount;
    }
}

public class OrderList
{
    public List <Order> GetOrderList()
    {
        List<Order> orders = new List();

        orders.Add(new Order("01/01/2009", "Electronics" ,"Bob"  ,4  ,2000));
        orders.Add(new Order("01/01/2009", "Hardware" ,"Bob"  ,2  ,1500));
        orders.Add(new Order("01/01/2009", "Electronics" ,"Jack" ,3 ,1500));
        orders.Add(new Order("01/01/2009", "Hardware" ,"Jack" ,6 ,5500 ));
        orders.Add(new Order("01/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("01/01/2009", "Hardware" ,"Jill" ,1 ,500));
        orders.Add(new Order("02/01/2009", "Electronics" ,"Bob"  ,3  ,1500));
        orders.Add(new Order("02/01/2009", "Hardware" ,"Bob"  ,1  ,1000));
        orders.Add(new Order("02/01/2009", "Electronics" ,"Jack" ,4 ,2000 ));
        orders.Add(new Order("02/01/2009", "Hardware" ,"Jack" ,5 ,5000 ));
        orders.Add(new Order("02/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("02/01/2009", "Hardware" ,"Jill" ,2 ,1000));
        orders.Add(new Order("03/01/2009", "Electronics" ,"Bob"  ,3  ,1500));
        orders.Add(new Order("03/01/2009", "Hardware" ,"Bob"  ,1  ,1000));
        orders.Add(new Order("03/01/2009", "Electronics" ,"Jack" ,3 ,1500 ));
        orders.Add(new Order("03/01/2009", "Hardware" ,"Jack" ,5 ,5000 ));
        orders.Add(new Order("03/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("03/01/2009", "Hardware" ,"Jill" ,1 ,500));
        orders.Add(new Order("04/01/2009", "Electronics" ,"Bob"  ,3  ,1500));
        orders.Add(new Order("04/01/2009", "Hardware" ,"Bob"  ,1  ,1000));
        orders.Add(new Order("04/01/2009", "Electronics" ,"Jack" ,3 ,1500 ));
        orders.Add(new Order("04/01/2009", "Hardware" ,"Jack" ,6 ,5500 ));
        orders.Add(new Order("04/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("04/01/2009", "Hardware" ,"Jill" ,1 ,500));
        orders.Add(new Order("05/01/2009", "Electronics" ,"Bob"  ,3  ,1500));
        orders.Add(new Order("05/01/2009", "Hardware" ,"Bob"  ,1  ,1000));
        orders.Add(new Order("05/01/2009", "Electronics" ,"Jack" ,3 ,1500 ));
        orders.Add(new Order("05/01/2009", "Hardware" ,"Jack" ,5 ,5000 ));
        orders.Add(new Order("05/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("05/01/2009", "Hardware" ,"Jill" ,2 ,1000));
        orders.Add(new Order("06/01/2009", "Electronics" ,"Bob"  ,3  ,1500));
        orders.Add(new Order("06/01/2009", "Hardware" ,"Bob"  ,1  ,1000));
        orders.Add(new Order("06/01/2009", "Electronics" ,"Jack" ,3 ,1500 ));
        orders.Add(new Order("06/01/2009", "Hardware" ,"Jack" ,6 ,5500 ));
        orders.Add(new Order("06/01/2009", "Electronics" ,"Jill" ,12 ,6000));
        orders.Add(new Order("06/01/2009", "Hardware" ,"Jill" ,1 ,500));

        return orders;
    }
}

• Step 2: Create the ObjectDataSource.

Drag and drop ObjectDataSource to the ASPX page. Choose MatrixReport.OrderList as the business object. Select the GetOrderList method in Data Methods.



This data source is used to create the matrix report. The code below is generated by the designer in the ASPX page:

<asp:ObjectDataSource ID="ObjectDataSourceOrders" runat="server"
        SelectMethod="GetOrderList" TypeName="MatrixReport.OrderList">

• Step 3: Create the RDLC report.

Build the project. This is needed for the ObjectDataSource to appear in the DataSources of the Report Designer. Add a new Report.rdlc to the project. We will create both the reports in the same RDLC, using the following steps:



• Step 4: Create Matrix Report #1: Products Sold Every Month.

Drag and drop a Matrix from the toolbox on RDLC. From the Website DataSources on the picture above, drag and drop PurchaseDate in the Rows, ProductType columns of the grid. Now, we add the data, right click on Data, and click on Add Column. This creates a data column to the right. Drop Units in the first lower cell of Data and MarginAmount in the second lower cell. The formula Sum is generated by default in Sum(Fields!Units.Value). Let's now add the totals. Right click on the Rows (PurchaseDate) cell, and click on Subtotal. Row Totals gives the total at the Product level. Right click on the Rows (ProductType) cell and click on Subtotal. Column Totals gives the total units and the margin amount for each month.





• Step 5: Formatting and styling.

Set the value of the Format property in the Properties window of PurchaseDate to MMM yy. Set the value of the Format property of MarginAmount to N. This report has a Steelblue background and White color for headers. Right click on the green triangle on the Total and set the Format to N and Font->FontWeight to Bold.



• Step 6: Create MatrixReport #2, Products Sold By Sales Person Every Month.

This report gives the details of the products sold every month in the horizontal direction, and products sold by each sales person every month in the vertical direction. Repeat steps 4 and 5 in the same report. Right click on ProductType and select Insert Group. The Group and Sorting Properties dialog appears. Select =Fields!SalesPerson.Value as the expression.

• Step 7: Final one.

Create the report viewer on the ASP page. Specify RDLC and data source. Run the report to see the result.

<rsweb:ReportViewer ID="ReportViewerOrders" 
  runat="server" Font-Names="Verdana" 
  Font-Size="8pt" Height="800px" Width="1000px">
<LocalReport ReportPath="Report.rdlc">
<DataSources>
 <rsweb:ReportDataSource 
    DataSourceId="ObjectDataSourceOrders" 
    Name="OrdersReport" />
</DataSources>
</LocalReport>
</rsweb:ReportViewer>

History

• Initial version: February 5, 2010.