Binding RadChart to PivotGrid

[Gareth]

I've done this in a desktop project, but I can't for the life of me figure out how to do this in webforms?
I have data unioned together so it can be displayed in a pivot grid

Date     | Property | Value

1/1/13   | AAA        | 74
1/1/13   | BBB       | 62
2/1/13   | AAA        | 42
2/1/13   | BBB       | 12
2/1/13   | CCC       | 14
3/1/13   | AAA       | 16
x            | series    | y
Which works fine, I was hoping to display this in the chart, so that we can allow users to alter the graph live based on the pivot grid.

I tried HighCharts but I'd have to run multiple queries to try and separate out x-axis and then the series,
RadCharts is the only way I've been able to display the data in a chart thus far, but the amount of data means that the chart is so small its pointless.

Forgive the obfuscation, you can't tell much from the graph anyway but has to be done!

[Antonio Stoilkov]

Hi Gareth,

You could achieve your scenario by binding the RadPivotGrid and subscribing to its CellDataBound event. The idea is to use the e.Cell.ParentRowIndexes and e.Cell.ParentColumnIndexes and build both x and y axis depending on the values. You could take a look at the example code below to investigate and get the idea how to create a RadHtmlChart from the pivot grid CellDataBound event.

HashSet<string> already = new HashSet<string>();
Dictionary<string, ColumnSeries> yAxis = new Dictionary<string, ColumnSeries>();
protected void RadPivotGrid1_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
{
    PivotGridDataCell dataCell = e.Cell as PivotGridDataCell;
    if (dataCell != null && dataCell.CellType == PivotGridDataCellType.DataCell)
    {
        if (already.Count == 0)
        {
            this.RadHtmlChart1.PlotArea.XAxis.Items.Clear();
            this.RadHtmlChart1.PlotArea.Series.Clear();
        }
        string rowName = GetName(dataCell.ParentRowIndexes);
        string columnName = GetName(dataCell.ParentColumnIndexes);
        if (already.Add(columnName))
        {
            AxisItem axisItem = new AxisItem(columnName);
            this.RadHtmlChart1.PlotArea.XAxis.Items.Add(axisItem);
        }
        ColumnSeries columnSeries = null;

        if (yAxis.ContainsKey(rowName))
        {
            columnSeries = yAxis[rowName];
        }
        else
        {
            columnSeries = new ColumnSeries();
            yAxis.Add(rowName, columnSeries);
            columnSeries.Name = rowName;
            this.RadHtmlChart1.PlotArea.Series.Add(columnSeries);
        }
        SeriesItem item = new SeriesItem();
        decimal value = 0;
        if (decimal.TryParse(e.Cell.Text, out value))
        {
            item.YValue = value;
        }
        else
        {
            item.YValue = null;
        }
        columnSeries.Items.Add(item);
    }
}

private string GetName(object[] indexes)
{
    StringBuilder builder = new StringBuilder();
    foreach (object index in indexes)
    {
        builder.Append(index.ToString());
        builder.Append(" / ");
    }
    builder.Remove(builder.Length - 3, 3);
    return builder.ToString();
}

All the best,
Antonio Stoilkov
the Telerik team

[Gareth]

Incredibly helpful, thank you!