Display count of ASP.Net RDLC report column specific value using C# and VB.Net

Vanessa
 
on May 30, 2021 11:43 PM
Sample_890811.zip
7994 Views

Hello,

I have a query on how to calculated column specific value on RDLC Report.

Sample I have two Column Name Department

On Department Column I have a three value which are IT, HR and Supply

then what I want is the total of two column only which are IT and HR only.

IT and HR only as overall total in new row like the Department Column there are IT and Hr which IT has a value of 1 and Hr has a value of 2.

The supply value in Column Department has a value of 3 but I don't want to add because only IT and HR value is my need to have a total in new row display whether top or button.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 04, 2021 12:45 AM
on Jun 04, 2021 12:48 AM

Hi Vanessa,

For display count add a row below to the table in rdlc report.

1. Right click on the table, selet Insert Row -> Outside Group - Below.

2. Add TextBox in the row below the column you want to display.

After that right click in the TextBox and select Expression...

3. In expression window write the expression.

You can use IF function instead of Switch for multiple condition.

=SUM(
Switch(
    Fields!Country.Value = "UK", 1, 
    Fields!Country.Value = "Mexico", 1
    )
)

Then press Ok.

Using the below article i have binded the report and display the column count based on the expression.

RDLC (Local SSRS) Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

HTML

<asp:ScriptManager runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Width="100%" Height="300" HyperlinkTarget="_blank"></rsweb:ReportViewer>

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.LocalReport.EnableHyperlinks = true;
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc");
        Customers dsCustomers = GetData("SELECT TOP 5 * FROM Customers");
        ReportDataSource datasource = new ReportDataSource("Customers", dsCustomers.Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}
private Customers GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (Customers dsCustomers = new Customers())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ReportViewer1.LocalReport.EnableHyperlinks = True
        ReportViewer1.ProcessingMode = ProcessingMode.Local
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/Report.rdlc")
        Dim dsCustomers As Customers = GetData("SELECT TOP 5 * FROM Customers")
        Dim datasource As ReportDataSource = New ReportDataSource("Customers", dsCustomers.Tables(0))
        ReportViewer1.LocalReport.DataSources.Clear()
        ReportViewer1.LocalReport.DataSources.Add(datasource)
    End If
End Sub

Private Function GetData(ByVal query As String) As Customers
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As SqlCommand = New SqlCommand(query)
    Using con As SqlConnection = New SqlConnection(conString)
        Using sda As SqlDataAdapter = New SqlDataAdapter()
            cmd.Connection = con
            sda.SelectCommand = cmd
            Using dsCustomers As Customers = New Customers()
                sda.Fill(dsCustomers, "DataTable1")
                Return dsCustomers
            End Using
        End Using
    End Using
End Function

Screenshot