Click here to Skip to main content
Click here to Skip to main content
Go to top

Create Matrix Report using ReportViewer in ASP.NET 2.0

, 7 Feb 2010
Rate this:
Please Sign up or sign in to vote.
This article creates matrix reports with ObjectDataSource using the ReportViewer in ASP.NET 2.0.

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.

    pic2.jpg

    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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Shabana Mohammad
Software Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralModification in Code snippet PinmemberRookieCoder_NG2-Aug-12 1:30 
Hi. Good article. Smile | :) I got a solution for something else I was looking for - Using Business Objects for ReportViewer. Thanks.
 
There's an error in GetOrderList() method. orders definition should be.. List<Order> orders = new List<Order>(); This appears only in the snippet provided in the article. Thought you might want to edit it. Can you also provide the Report.rdlc xml file in article.?

There is no great genius without some touch of madness - Seneca, Epistles

QuestionAutosize Column Pinmemberpocchio26-Feb-12 23:09 
GeneralThanks for sharing PinmemberAhsan Murshed10-Feb-10 21:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140926.1 | Last Updated 7 Feb 2010
Article Copyright 2010 by Shabana Mohammad
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid