Description

Using an Expression you can sum the values of a column in Reports automatically. I will show you a demo by which you can total the values of a column in reports.

Note: I am assuming that you can create the report. If you are a beginner to reports then see my earlier article "Getting Started with Reports in .NET".

Use the following procedure to understand this article.

Step 1: I will create a table named "Employee" in my database "Test".

CREATE TABLE [dbo].[Employee](

[Name] [varchar](50) NULL,

[Salary] [int] NULL

)

insert into Employee (Name,Salary)values('A',50000)

insert into Employee (Name,Salary)values('B',10000)

insert into Employee (Name,Salary)values('C',60000)

insert into Employee (Name,Salary)values('D',20000)

insert into Employee (Name,Salary)values('E',10000)

insert into Employee (Name,Salary)values('F',70000)

insert into Employee (Name,Salary)values('G',40000)

Select * from employee

table

Step 2: Create a page named "Default.aspx" with "ScriptManager" from the Ajax Extensions section, "SQLDataSource" from the Data section and "ReportViewer" Control from the Reporting section.

Bind the table columns with a "SQLDataSource" to access the data.

SQLDataSource

Step 3: Add a report named "Report.rdlc" and bind the dataset into the reports using Table.
Add a report

Run the "Default.aspx" after adding the report named "Report.rdlc" to the "ReportViewer" control.
ReportViewer

Purpose: I want to total the salary, in other words what is the total salary?

Solution

To do that I will use an "Expression" named RowNumber.
  1. Insert a new row on the following side that will be outside the group:

    outside from the group

    Note: The Outside Group will create the single row after all the group rows whereas an Inside Group will create the single row after every single row within the group.
  2. Create the expression by right-clicking on that column and typing the total in the first column:

    column and type

  3. Select the "Aggregate" subcategory in the "Comman Functions" category and "Sum" as an item, then the expression will be like:

    =Sum(

    Comman Functions

    Then select the "Fields" as category and "Salary" as values, that will look like:

    =Sum(Fields!Salary.Value)

    Fields

    Click the "OK" button.

    Sum

  4. Run the "Default.aspx".

    Run