Yearly Report using Bar Chart in ASP.Net

Last Reply on Feb 25, 2016 05:12 AM By Shashikant

Posted on Feb 24, 2016 03:18 AM

I want to generate the yearly report 

how i get my input in dropdown list as march2015 to febuary 2016

i want to extract and generate the datas from database and sorting by using month and the year of the date??? How?? help me with the code?????

Posted on Feb 25, 2016 05:12 AM Modified on on Feb 25, 2016 05:13 AM

Hi,

Here I have created sample that will help you out.

HTML

<div>
    From :
    <asp:DropDownList ID="ddlFromMonths" runat="server">
    </asp:DropDownList>
    &nbsp;&nbsp;<asp:DropDownList ID="ddlFromYears" runat="server" OnSelectedIndexChanged="FromDateChanged"
        AutoPostBack="true">
    </asp:DropDownList>
    To:
    <asp:DropDownList ID="ddlToMonths" runat="server">
    </asp:DropDownList>
    &nbsp;&nbsp;<asp:DropDownList ID="ddlToYears" runat="server">
    </asp:DropDownList>
    <br />
    <asp:Button ID="Button1" Text="Submit" runat="server" ValidationGroup="val1" OnClick="GetChart" />
    <asp:Chart ID="Chart1" runat="server" Height="300px" Width="600px" Palette="Berry">
        <Series>
            <asp:Series Name="Series1" YValueMembers="OrderCount" XValueMember="Month" BorderWidth="1"
                IsValueShownAsLabel="True" ChartType="StackedColumn">
            </asp:Series>
        </Series>
        <ChartAreas>
            <asp:ChartArea Name="ChartArea1">
                <AxisX IsStartedFromZero="True" Title="Mese" TitleFont="Segoe UI, 10pt, style=Bold"
                    IsLabelAutoFit="False" LineColor="Gray" Minimum="0" Maximum="12">
                    <MajorGrid LineColor="LightGray" />
                    <LabelStyle Font="Segoe UI, 9pt" />
                </AxisX>
            </asp:ChartArea>
        </ChartAreas>
    </asp:Chart>
</div>

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        PopulateMonths(ddlFromMonths);
        PopulateMonths(ddlToMonths);
        PopulateYears(ddlFromYears);
        PopulateYears(ddlToYears);
    }
}

protected void FromDateChanged(object sender, EventArgs e)
{
    ddlToYears.ClearSelection();
    ddlToYears.Items.FindByValue(ddlFromYears.SelectedItem.Value).Selected = true;
}

private void PopulateMonths(DropDownList dropDownList)
{
    var months = DateTimeFormatInfo.CurrentInfo.MonthNames.Select((m, i) => new { Month = m, Value = i + 1 }).Take(DateTimeFormatInfo.CurrentInfo.MonthNames.Length - 1);
    dropDownList.DataSource = months;
    dropDownList.DataTextField = "Month";
    dropDownList.DataValueField = "Value";
    dropDownList.DataBind();
    dropDownList.Items.Insert(0, new ListItem("Select", "0"));
}

private void PopulateYears(DropDownList ddl)
{
    var result = Enumerable.Range(1990, DateTime.Now.Year - 1990 + 1);
    ddl.DataSource = result;
    ddl.DataBind();
    ddl.Items.Insert(0, new ListItem("Select", "0"));
}

protected void GetChart(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    string constr = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("GetChartData", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FromMonth", ddlFromMonths.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@FromYear", ddlFromYears.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@ToMonth", ddlToMonths.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@ToYear", ddlToYears.SelectedItem.Value);
            using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
            {
                sda.Fill(dt);
            }
        }
    }



    var data = from row in dt.AsEnumerable()
                select new
                {
                    OrderCount = row["OrderCount"],
                    OrderMonth = row["OrderMonth"],
                    Year = row["OrderYear"]
                };
    var months = DateTimeFormatInfo.CurrentInfo.MonthNames.Select((m, i) => new { Month = m, Value = i + 1 }).Take(DateTimeFormatInfo.CurrentInfo.MonthNames.Length - 1);

    var result = from month in months
                    join d in data
                    on month.Value equals d.OrderMonth into g
                    from d in g.DefaultIfEmpty()
                    select new MonthChart
                    {
                        OrderMonth = month.Value,
                        OrderCount = d == null ? string.Empty : d.OrderCount.ToString(),
                        Month = string.Format(d == null ? "{0}" : "{0} - {1}", month.Month, d == null ? string.Empty : d.Year.ToString())
                    };
    Chart1.DataSource = result;
    Chart1.ChartAreas[0].AxisX.Interval = 1;
}

MonthChart Class

class MonthChart
{
    public int OrderMonth { get; set; }
    public string OrderCount { get; set; }
    public string Month { get; set; }
    public int Year { get; set; }
}

SQL

-- GetChartData 1,1997,12,1997
CREATE PROCEDURE [dbo].[GetChartData]
	@FromMonth INT = NULL,
	@FromYear INT,
	@ToMonth  INT = NULL,
	@ToYear INT
AS
BEGIN
	DECLARE @FromDate DATETIME  
	DECLARE @ToDate DATETIME

	SET @FromDate = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(YEAR,@FromYear - 1900,0))+ @FromMonth -1,0)
	SET @ToDate = DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(YEAR,@ToYear - 1900,0))+ @ToMonth -1,0)

	SELECT COUNT(OrderId) OrderCount, DATEPART(MONTH,OrderDate)  as OrderMonth,DATEPART(YEAR,OrderDate) OrderYear
	FROM Orders 
	WHERE OrderDate >= @FromDate AND OrderDate <= @ToDate
	GROUP BY 
	DATEPART(MONTH,OrderDate),DATEPART(YEAR,OrderDate)
END

Screenshot