Dynamic grouping with Kendo and SQL

The brief for one of our projects was to create a dashboard that could display different time ranges based on a user selection.  A very common request, and one we accomplished in Kendo by defining values in a user input and passing them back via our REST service to SQL stored procedures as a parameter to change our date range.

The problem with this approach appears with any trend metric, where Kendo will default to a category axis label for each and every grouping.  Fine if you have a sensible number of data points, but if you’re switching from a dashboard displaying a weeks-worth of data to one displaying a year, the effect on a trend chart is something like this…

Kendo trend chart, badly formatted

With this many data points, the category axis labels are a complete mess and the markers overpower the rest of the chart.

After searching the Kendo framework documentation and forums I came across two ways of working on the issue.

Skip – Using the axis label skip function we can specify a number of labels to be skipped when rendering the chart.  Labels: { skip: 6 } on the chart above for instance, would remove enough labels to leave us with a date label for each week. This approach can be quite useful on a static chart whose date range never changes. If we needed to display a trend chart with a high degree of accuracy, but always covering the same range of values.

jQuery – I did come across a few posts covering ways to accomplish dynamic grouping in jQuery, so if we selected a month date range, we’d get grouping by days, if we selected a year, we could specify grouping by months instead – exactly the effect I was looking for, apart from one point.  Performance.

As the actual dashboard I was working on was to be integrated into a cloud based app, the end user could be anyone, anywhere.  Sending a years-worth of daily figures back to the users browser and having it grouped and summarised directly on the client creates unnecessary network and CPU load, so the most sensible place to make a change was in the SQL I was using to populate the chart.

Static grouping

The stored procedure code above is simple enough, taking an age parameter from the REST service and returning all the day rows from the date dimension table, along with figures from the aggregate table (I’m using ISNULL with a LEFT JOIN to preserve dates with no figures).

To change the grouping in the query we can use a DATEPART function on the ActualDate column, changing the grouping to weeks/months/years etc, but with this approach we’d have the reverse of our current problem – a tidier chart when dealing with a year’s data, with a complete loss of detail at the lower ranges.

The solution I found was to use a variable within the stored procedure to drive a CASE expression, giving us a different DATEPART code depending on the date range we’d selected.

The code below declares a variable and sets its value according to the @Age parameter being passed in from the dashboard via the REST service.

Variable definition

And here’s the code we’re going to use to drive the replacement for our ActualDate column.

CASE expression

The CASE expression is used in the SELECT portion of the query as well as in the GROUP BY and ORDER BY clauses, so that when we select any date range up to 90 days, we get day level accuracy.

Kendo trend chart - daily detail

Between 91 and 364 days, we get our data grouped by weeks for a more summarised view and when we go to a year or higher, we get a monthly summary in our chart.

Kendo trend chart - monthly summary

A great deal more readable, and more appropriate to the view we’ve selected as well and without sending any unnecessary data or adding any extra Javascript to the dashboard code.

A quick check in Fiddler shows with the static stored procedure, we’re bringing back 27,230 bytes of data to the client when we select the year date range, but pushing grouping back to SQL means we only return 1,039 bytes – a 96% saving just on one stored procedure call.

The readability benefits are obvious, but the performance increase isn’t to be ignored either.  Saving 26,191 bytes by grouping at the server might not sound like a lot, but if the real dashboard this demo was based on received a thousand page views in a day, we’d save 24.9MB of bandwidth on this metric alone.

If anyone else reading this is working on a similar solution, I'd love to hear about it.  Drop me a comment below if you have any questions - Andy Knight.

Ask Us a Question