Grouping and Totals in RDLC Report Viewer in VB.Net

Hi kana250688,

For adding grouping and totals, you need to configure the Report Designer.

Group Data

1. Select the Design tab.
2. If you don't see the Row Groups pane, right-click the design surface and select View >Grouping.
3. From the Report Data pane, drag the [Category 1] field to the Row Groups pane.
4. Follow the same for [Category 2] field.
5. Delete Columns.

Add Totals

1. Go to the Design view.
2. Right-click the data region cell that contains the [QTY] expression, and select Add Total. Report Designer adds a row with a sum of the amount for each Category.
3. Right-click the cell that contains the field and select Add Total. Report Designer adds a sum of the quantity for each order to the totals row.
4. In the empty cell to the left of the Sum[Qty] cell, enter the string "Total".

Add Category Total

1. Right-click the expression cell, and select Add Total > After. Report Designer adds a new row containing sums of the [Qty] value for each Category, and the string "Total" to the bottom of the [QTY]expression column.
2. Enter the word "Total" in the same cell, so it reads "Total".

Add Grand Total

1. Right-click the expression cell, and select Add Total > After. Report Designer adds a new row containing sums of the [Qty]value for the entire report, and the string "Total" to the bottom of the expression column.
2. Enter the string "Total" in the same cell.
3. Select the cell with "Total", the two SUM() expression cells and the empty cells between them.

Please refer the complete steps.

1. Right click on the Row Groups Details and click on Add Group and then Parent Group.

2. From the Tablix group window select Category1 column from Group by DropDownList and click on OK.

3. Repeat the same step for Category2 column.

4. Then, Right click on the original columns and click Delete Columns.

5. Now in the QTY column, Right click and click on Add Total.

6. Then in the newly added row, Right click and click on Add Total.

7. Again in the newly added row, Right click and click on Add Total.

8. Now, set the Text for the columns. Here it is Total.

Finally, the report will look as shown below.

Namespaces

Imports System.Data.SqlClient
Imports Microsoft.Reporting.WinForms

Code

Here i have used SQL Client for populating the data from Database. You need to use OleDb Client instead of SQL Client.

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim dsCustomers As Customers = GetData()
    Dim datasource As New ReportDataSource("Customers", dsCustomers.Tables(0))
    Me.ReportViewer1.LocalReport.DataSources.Clear()
    Me.ReportViewer1.LocalReport.DataSources.Add(datasource)
    Me.ReportViewer1.RefreshReport()
End Sub

Private Function GetData() As Customers
    Dim constr As String = "Data Source=.\SQL2022;Initial Catalog=Samples;UID=sa;PWD=pass@123;"
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT * FROM Item")
            Using sda As New SqlDataAdapter()
                cmd.Connection = con
                sda.SelectCommand = cmd
                Using dsCustomers As New Customers()
                    sda.Fill(dsCustomers, "DataTable1")
                    Return dsCustomers
                End Using
            End Using
        End Using
    End Using
End Function

Screenshot

Refer below link for more details.

https://learn.microsoft.com/en-us/sql/reporting-services/lesson-6-adding-grouping-and-totals-reporting-services?view=sql-server-ver16