How to show asp.net chart grouped by 2 parameters

Question

I want to present some condensed data to the user using the Chart component.

SQL (C# / Oracle):

SELECT c.date, c.approved, count(distinct c.f1) amt_c, count(b.f1) amt_b, sum(b.value) sum_values
FROM contracts c
JOIN bens b ON c.ben_id = b.id
WHERE :YearMonth = to_char(c.date,'YYYYMM') AND NOT c.approved = 'REJECTED'
GROUP BY c.date, c.approved
ORDER BY c.date

I have this SQL in a method that passes a DataSet to the ObjectDataSource in the .aspx page (The approved field can have 3 values: REJECTED, APPROVED and PENDING).

Chart in .aspx page:

<asp:Chart ID="Chart1" runat="server" DataSourceID="RelatorioDataSource" 
    Width="700px" Compression="10" Palette="Chocolate">
    <Series>
        <asp:Series Name="Contracts" XValueMember="date" 
            YValueMembers="amt_c" IsXValueIndexed="False" 
            XValueType="DateTime" IsValueShownAsLabel="True" BorderDashStyle="DashDot" 
            CustomProperties="DrawingStyle=Emboss, EmptyPointValue=Zero, DrawSideBySide=True" 
            YValuesPerPoint="4">
        </asp:Series>
        <asp:Series BorderDashStyle="DashDot" ChartArea="ChartArea1" 
            CustomProperties="DrawingStyle=Emboss, EmptyPointValue=Zero, DrawSideBySide=True" 
            IsValueShownAsLabel="True" Name="Bens" 
            XValueMember="date" XValueType="DateTime" 
            YValueMembers="amt_b" YValuesPerPoint="4">
        </asp:Series>
    </Series>
    <ChartAreas>
        <asp:ChartArea Name="ChartArea1">
        </asp:ChartArea>
    </ChartAreas>
</asp:Chart>

I want to show the numbers of approved/pending contracts/bens for each day (4 bars), but the chart shows only two columns.

Answer 1

Create a group for each series..

example: chart1.series[0]["StackedGroupName"] = "group1";

Answer 2

Solved by creating an object relatorio to hold the returned data (instead of the DataSet), filtering the results using LINQ to Objects, and adding the series programmatically in codeBehind.

var approved = relatorio
    .Where(r => r.APPROVED == "APPROVED")
    .ToList()
    ;
var pending = relatorio
    .Where(r => r.APPROVED == "PENDING")
    .ToList()
    ;

Creating Legends

Legend legenda = new Legend("Legenda");
legenda.Docking = Docking.Bottom;
legenda.LegendStyle = LegendStyle.Row;
legenda.Alignment = System.Drawing.StringAlignment.Center;

Creating series in a loop

for (int i = 0; i < 4; i++) {
    Series temp = new Series {
        XAxisType = AxisType.Primary,
        XValueType = ChartValueType.DateTime,
        YAxisType = AxisType.Primary,
        //mostra só a quantidade de contratos
        IsValueShownAsLabel = i % 2 == 0 ? true : false,
        ChartType = SeriesChartType.Column,
        CustomProperties = "EmptyPointValue=Zero",
        Legend = "Legenda"
    };
    grafico.Series.Add(temp);
}
approvedValues.Points.DataBindXY(approved, "DATE", approved, "SUM_VALUES");

DataBinding series

// approved CONTRACTS
grafico.Series[0].Points.DataBindXY(approved, "DATE", approved, "AMT_C");
grafico.Series[0].LegendText = "Contratos approved";
// approved BENS
grafico.Series[1].Points.DataBindXY(approved, "DATE", approved, "AMT_B");
grafico.Series[1].LegendText = "Ben approved";
grafico.Series[1].ChartType = SeriesChartType.Line;
// pending CONTRACTS
grafico.Series[2].Points.DataBindXY(pending, "DATE", pending, "AMT_C");
grafico.Series[2].LegendText = "Contratos pending";
// pending BENS
grafico.Series[3].Points.DataBindXY(pending, "DATE", pending, "AMT_B");
grafico.Series[3].LegendText = "Ben pending";
grafico.Series[3].ChartType = SeriesChartType.Line;