Display Days difference between two dates and specified dates in ASP.Net RDLC Report

Hi kankon,

For calculation of days except vacations days you need to add another column in your TypeDataSet.

Then from code behind set the value by calculating with vacation days.

After that use the column in your report to display instead of using expression for calculation.

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:ScriptManager runat="server" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server"></rsweb:ReportViewer>

Namespaces

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Reporting.WebForms;

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        ReportViewer1.ProcessingMode = ProcessingMode.Local;
        ReportViewer1.LocalReport.ReportPath = Server.MapPath("~/EmployeeReport.rdlc");
        ReportDataSource datasource = new ReportDataSource("Employees", GetData().Tables[0]);
        ReportViewer1.LocalReport.DataSources.Clear();
        ReportViewer1.LocalReport.DataSources.Add(datasource);
    }
}

private Employees GetData()
{
    string query = "SELECT TOP 5 FirstName 'Name',CAST(BirthDate AS DATE) 'StartDate',CAST(HireDate AS DATE) 'EndDate',0 'Days' FROM Employees " +
        " UNION SELECT 'Dharmendra',NULL,NULL,0";
    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 (Employees employees = new Employees())
            {
                sda.Fill(employees, "DataTable1");

                foreach (DataRow employee in employees.Tables["DataTable1"].Rows)
                {
                    if (!Convert.IsDBNull(employee["StartDate"]) || !Convert.IsDBNull(employee["EndDate"]))
                    {
                        DateTime startDate = Convert.ToDateTime(employee["StartDate"]);
                        DateTime endDate = Convert.ToDateTime(employee["EndDate"]);
                        // Calculate Holidays.
                        int diff = (endDate - startDate).Days;
                        int holidays = Holiday().Where(s => s >= startDate && s <= endDate).ToList().Count;
                        employee["Days"] = diff - holidays;
                    }
                }

                return employees;
            }
        }
    }
}

private List<DateTime> Holiday()
{
    List<DateTime> dates = new List<DateTime>();
    dates.Add(new DateTime(1949, 02, 25));
    dates.Add(new DateTime(2019, 01, 01));
    dates.Add(new DateTime(2019, 02, 26));
    dates.Add(new DateTime(2019, 04, 04));
    dates.Add(new DateTime(2019, 08, 31));
    dates.Add(new DateTime(2019, 09, 11));
    dates.Add(new DateTime(2019, 11, 10));

    return dates;
}

Screenshot