Bar chart from datasource with multiple columns per axis

[Shawn]

I have a bar chart that is getting its data from my controller in the form of Json data as shown below:

[
{"SiteId":1,"Hour":9,"Quantity":9.0000,"Value":43.0500,"NumCustomers":5},
{"SiteId":1,"Hour":10,"Quantity":18.0000,"Value":43.6900,"NumCustomers":8},
{"SiteId":1,"Hour":11,"Quantity":7.0000,"Value":22.4200,"NumCustomers":3},
{"SiteId":1,"Hour":14,"Quantity":1.0000,"Value":1.0000,"NumCustomers":1}
{"SiteId":2,"Hour":9,"Quantity":10.0000,"Value":48.0500,"NumCustomers":7},
{"SiteId":2,"Hour":10,"Quantity":20.0000,"Value":83.6900,"NumCustomers":11},
{"SiteId":2,"Hour":11,"Quantity":9.0000,"Value":27.4200,"NumCustomers":5},
{"SiteId":2,"Hour":14,"Quantity":3.0000,"Value":11.0000,"NumCustomers":4}
{"SiteId":3,"Hour":9,"Quantity":7.0000,"Value":33.0500,"NumCustomers":4},
{"SiteId":3,"Hour":10,"Quantity":16.0000,"Value":35.6900,"NumCustomers":7},
{"SiteId":3,"Hour":11,"Quantity":7.0000,"Value":22.4200,"NumCustomers":3},
{"SiteId":3,"Hour":14,"Quantity":2.0000,"Value":5.0000,"NumCustomers":2}
]

 

As you can see, there are multiple sites for the same hours.  The horizontal axis should represent each hour from the data.  Each hour should have a column representing each site, very similar to the 'Column Chart' example on your site.  The code example on the site shows the series data like this:

.Series(series => {
    series.Column(new double[] { 3.907, 7.943, 7.848, 9.284, 9.263, 9.801, 3.890, 8.238, 9.552, 6.855 }).Name("India");
    series.Column(new double[] { 4.743, 7.295, 7.175, 6.376, 8.153, 8.535, 5.247, -7.832, 4.3, 4.3 }).Name("Russian Federation");
    series.Column(new double[] { 0.010, -0.375, 1.161, 0.684, 3.7, 3.269, 1.083, -5.127, 3.690, 2.995 }).Name("Germany");
    series.Column(new double[] { 1.988, 2.733, 3.994, 3.464, 4.001, 3.939, 1.333, -2.245, 4.339, 2.727 }).Name("World");
})

 

My current code that shows the hourly totals for 1 site is listed below, but I'd like this to work for multiple sites as shown the in above collection of Json data. What is the best way to accomplish this?

My current code:

    @(Html.Kendo().Chart<CBECloudBO2.ViewModels.ChartHourlySales>()
            .Name("chartHourlySales")
            .Title("Hourly Sales")
            .DataSource(datasource => datasource
                .Read(read => read.Action("Chart_HourlySales", "Home")))
            .Legend(legend => legend
                .Visible(false))

            .ChartArea(chartArea => chartArea
                .Background("transparent"))

    .Series(series =>
    {
        series.Column(p => p.Value).Name("Total Sales");
    })
    .CategoryAxis(axis => axis
        .Name("series-axis")
        .Line(line => line.Visible(false))
    )
    .CategoryAxis(axis => axis
        .Name("label-axis")
        .Categories(p => p.Hour)
    )
    .ValueAxis(axis => axis
        .Numeric()
        .AxisCrossingValue(0, int.MinValue)
    )
    .Tooltip(tooltip => tooltip
        .Visible(true)
        .Format("{0}%")
        .Template("#= series.name #: #= value #")
    )
)

 

Thanks,

Shawn

[Tsvetina]

Hello Shawn,

Looking at your data, it looks like it would be best to group it by SiteID. This will show one column per each site id in each category (in your case, this will be Hour I guess). You can see a demo with a grouped column chart here:
Bar Charts / Binding to grouped data

Regards,
Tsvetina
Telerik by Progress

[Shawn]

Tsvetina,

The .Group did the trick.  Thanks for the reply.  However I am seeing an interesting issue with where the column data is actually being display.  The 'CBE Live Demo' data is all correct based on each hour the data was recorded.  However, the second site 'Townstal Road Garage' was recorded in hour 12, yet is being displayed at hour 0.  Below is my JSON data and the code implementing the chart.

[
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":0,"Quantity":11.0000,"Value":119.0700,"NumCustomers":8},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":1,"Quantity":6.0000,"Value":86.1500,"NumCustomers":5},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":2,"Quantity":5.0000,"Value":18.6500,"NumCustomers":5},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":3,"Quantity":6.0000,"Value":220.4500,"NumCustomers":6},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":5,"Quantity":9.0000,"Value":56.1500,"NumCustomers":8},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":6,"Quantity":23.0000,"Value":229.1500,"NumCustomers":19},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":7,"Quantity":159.0000,"Value":1204.3500,"NumCustomers":112},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":8,"Quantity":131.0000,"Value":860.5400,"NumCustomers":100},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":9,"Quantity":134.0000,"Value":1232.5000,"NumCustomers":95},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":10,"Quantity":208.0000,"Value":1281.8400,"NumCustomers":135},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":11,"Quantity":210.0000,"Value":1022.2600,"NumCustomers":130},
{"SiteId":1,"SiteName":"Townstal Road Garage","Hour":12,"Quantity":8.0000,"Value":28.5600,"NumCustomers":3},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":12,"Quantity":154.0000,"Value":1193.5500,"NumCustomers":106},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":13,"Quantity":230.0000,"Value":933.4500,"NumCustomers":139},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":14,"Quantity":169.0000,"Value":893.4100,"NumCustomers":113},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":15,"Quantity":210.0000,"Value":1204.1800,"NumCustomers":118},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":16,"Quantity":208.0000,"Value":1402.3400,"NumCustomers":145},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":17,"Quantity":171.0000,"Value":1233.3000,"NumCustomers":107},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":18,"Quantity":205.0000,"Value":1418.8400,"NumCustomers":112},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":19,"Quantity":143.0000,"Value":911.1400,"NumCustomers":92},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":20,"Quantity":157.0000,"Value":829.1500,"NumCustomers":83},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":21,"Quantity":79.0000,"Value":635.3000,"NumCustomers":51},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":22,"Quantity":88.0000,"Value":328.6400,"NumCustomers":37},
{"SiteId":4,"SiteName":"CBE Live Demo","Hour":23,"Quantity":58.0000,"Value":302.0500,"NumCustomers":31}
]

 

As you can see by the bold text above, there are two chunks of data for hour 12.  One for site 1 and one for site 4.  I would expect the two columns to appear above the hour 12 on the chart, but they do not.  See attached screen shot.

Here is my chart implementation:

<div class="panel panel-default">
    <div class="demo-section k-content wide">
        @(Html.Kendo().Chart<CBECloudBO2.ViewModels.ChartHourlySales>()
                .Name("chartHourlySales")
                .Title("Hourly Sales (All Sites)")
                .DataSource(datasource => datasource
                    .Read(read => read.Action("Chart_HourlySales", "Home"))
                    .Group(group => group.Add(model => model.SiteName))
                    .Sort(sort => sort.Add(model => model.Hour).Ascending())
                    )
                .Legend(legend => legend.Visible(true))
                .ChartArea(chartArea => chartArea
                .Background("transparent"))

        .Series(series =>
        {
            series.Column(model => model.Value).Name("#= group.value #");
        })
        .CategoryAxis(axis => axis
            .Name("series-axis")
            .Line(line => line.Visible(false))
        )
        .CategoryAxis(axis => axis
            .Name("label-axis")
            .Categories(p => p.Hour)
        )
        .ValueAxis(axis => axis
            .Numeric()
        //.Labels(labels => labels.Format("{0}%"))
            .AxisCrossingValue(0, int.MinValue)
        )
        .Tooltip(tooltip => tooltip
            .Visible(true)
            .Format("{0}%")
            .Template("#= series.name #: #= value #")
        )
        )
    </div>
</div>

 

Regards,

Shawn

[Tsvetina]

Hi Shawn,

This post explains why you observe this behavior. To summarize the instructions, you either need to provide a matching data item from both groups for each category, or you can use series.categoryField property instead of the categoryAxis.field one. You can see a JS version of the same chart with this setting applied here.

Regards,
Tsvetina
Telerik by Progress

[Shawn]

Tsvetina,

Thanks for the links.  I got it working by using the 'matching data' method, but I converted to use the CategoryField.  Works great now.

Regards,

Shawn