title: ASP Chart, Year, Month, Day drill down
body: <p>I'm trying to make a drill down chart(stack column) in ASP.NET(C#) which presents a total revenue by year(2011, 2012, 2013 etc.), when I click a column let say the year 2012, the chart shows the total revenue from each month from that year. This the same for month to day.</p>
  
  <p>So this is the idea, but I have no clue how to make something like this, I've searched days finding some drill down examples but nothing worked for me, there are some examples but not with date drill down. </p>
  
  <p>This is what I got already, but how further?</p>
  
  <p>Database Table definition:<br>
  RevenueNumbers: int<br>
  Date: Date</p>
  
  <p>(Create and fill chart from SQL server)</p>
  
  <pre><code>public SqlConnection conn;
  
      protected void Page_Load(object sender, EventArgs e)
      {
          conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString);
  
          string query = "SELECT SUM(RevenueNumber) AS TotalRevenue, DATENAME(YYYY, Date) AS Year FROM test GROUP BY DATENAME(YYYY, Date)";
          SqlCommand cmd = new SqlCommand(query, conn);
          conn.Open();
  
          Chart1.DataSource = cmd.ExecuteReader();
          Series Series1 = new Series();
          Series1.XValueMember = "Year";
          Series1.YValueMembers = "TotalRevenue";
  
          Chart1.Series.Add(Series1);
          Chart1.Series[0].PostBackValue = "#XVAL";
          Chart1.Series[0].ChartType = SeriesChartType.Column;
          Chart1.DataBind();
  
      }
  </code></pre>
  
  <p>(HTML)</p>
  
  <pre><code>&lt;div&gt;  
      &lt;asp:Chart ID="Chart1" runat="server"&gt;
          &lt;chartareas&gt;
              &lt;asp:ChartArea Name="ChartArea1"&gt;
              &lt;/asp:ChartArea&gt;
          &lt;/chartareas&gt;
      &lt;/asp:Chart&gt;
  &lt;/div&gt;
  </code></pre>