Showing posts with label Charts. Show all posts
Showing posts with label Charts. Show all posts

Monday, February 1, 2016

Extending Chartjs


ChartJs is a free Javascript Chart library.Here is one example to extend the line chart with null data value.
    var ctx = document.getElementById("chart").getContext("2d");
    var data = {
        labels: ["January", "February", "March", "April", "May", "June", "July"],
        datasets: [{
                label: "My First dataset",
                fillColor: "rgba(220,220,220,0.2)",
                strokeColor: "rgba(220,220,220,1)",
                pointColor: "rgba(220,220,220,1)",
                pointStrokeColor: "#fff",
                pointHighlightFill: "#fff",
                pointHighlightStroke: "rgba(220,220,220,1)",
                data: [65, null, null, 81, 56, 55, 40]
            },
        ]
    };

    Chart.types.Line.extend({
        // Passing in a name registers this chart in the Chart namespace in the same way
        name: "MissingLine",
        initialize: function(data) {
            var helpers = Chart.helpers;
            //Declare the extension of the default point, to cater for the options passed in to the constructor
            this.PointClass = Chart.Point.extend({
                strokeWidth: this.options.pointDotStrokeWidth,
                radius: this.options.pointDotRadius,
                display: this.options.pointDot,
                hitDetectionRadius: this.options.pointHitDetectionRadius,
                ctx: this.chart.ctx,
                inRange: function(mouseX) {
                    return (Math.pow(mouseX - this.x, 2) < Math.pow(this.radius + this.hitDetectionRadius, 2));
                }
            });

            this.datasets = [];

            //Set up tooltip events on the chart
            if (this.options.showTooltips) {
                helpers.bindEvents(this, this.options.tooltipEvents, function(evt) {
                    var activePoints = (evt.type !== 'mouseout') ? this.getPointsAtEvent(evt) : [];
                    this.eachPoints(function(point) {
                        point.restore(['fillColor', 'strokeColor']);
                    });
                    helpers.each(activePoints, function(activePoint) {
                        activePoint.fillColor = activePoint.highlightFill;
                        activePoint.strokeColor = activePoint.highlightStroke;
                    });
                    this.showTooltip(activePoints);
                });
            }

            //Iterate through each of the datasets, and build this into a property of the chart
            helpers.each(data.datasets, function(dataset) {

                var datasetObject = {
                    label: dataset.label || null,
                    fillColor: dataset.fillColor,
                    strokeColor: dataset.strokeColor,
                    pointColor: dataset.pointColor,
                    pointStrokeColor: dataset.pointStrokeColor,
                    points: []
                };

                this.datasets.push(datasetObject);

                helpers.each(dataset.data, function(dataPoint, index) {
                    /***
                     * Check for datapoints that are null
                     ***/
                    if (helpers.isNumber(dataPoint) || dataPoint === null) {
                        //Add a new point for each piece of data, passing any required data to draw.
                        datasetObject.points.push(new this.PointClass({
                            /*** 
                             * add ignore field so we can skip them later
                             ***/
                            ignore: dataPoint === null,
                            value: dataPoint,
                            label: data.labels[index],
                            datasetLabel: dataset.label,
                            strokeColor: dataset.pointStrokeColor,
                            fillColor: dataset.pointColor,
                            highlightFill: dataset.pointHighlightFill || dataset.pointColor,
                            highlightStroke: dataset.pointHighlightStroke || dataset.pointStrokeColor
                        }));
                    }
                }, this);

                this.buildScale(data.labels);

                this.eachPoints(function(point, index) {
                    helpers.extend(point, {
                        x: this.scale.calculateX(index),
                        y: this.scale.endPoint
                    });
                    point.save();
                }, this);

            }, this);

            this.render();
        },

        draw: function(ease) {
            var helpers = Chart.helpers;
            var easingDecimal = ease || 1;
            this.clear();

            var ctx = this.chart.ctx;

            this.scale.draw(easingDecimal);

            helpers.each(this.datasets, function(dataset) {
                //Transition each point first so that the line and point drawing isn't out of sync
                //We can use this extra loop to calculate the control points of this dataset also in this loop

                helpers.each(dataset.points, function(point, index) {
                    point.transition({
                        y: this.scale.calculateY(point.value),
                        x: this.scale.calculateX(index)
                    }, easingDecimal);

                }, this);

                // Control points need to be calculated in a seperate loop, because we need to know the current x/y of the point
                // This would cause issues when there is no animation, because the y of the next point would be 0, so beziers would be skewed
                if (this.options.bezierCurve) {
                    helpers.each(dataset.points, function(point, index) {
                        //If we're at the start or end, we don't have a previous/next point
                        //By setting the tension to 0 here, the curve will transition to straight at the end
                        if (index === 0) {
                            point.controlPoints = helpers.splineCurve(point, point, dataset.points[index + 1], 0);
                        } else if (index >= dataset.points.length - 1) {
                            point.controlPoints = helpers.splineCurve(dataset.points[index - 1], point, point, 0);
                        } else {
                            point.controlPoints = helpers.splineCurve(dataset.points[index - 1], point, dataset.points[index + 1], this.options.bezierCurveTension);
                        }
                    }, this);
                }

                //Draw the line between all the points
                ctx.lineWidth = this.options.datasetStrokeWidth;
                ctx.strokeStyle = dataset.strokeColor;

                var penDown = false;
                var start = null

                helpers.each(dataset.points, function(point, index) {
                    /***
                     * no longer draw if the last point was ignore (as we don;t have anything to draw from)
                     * or if this point is ignore
                     * or if it's the first
                     ***/
                    if (!point.ignore && !penDown) {
                        ctx.beginPath();
                        penDown = true;
                        start = point;
                    }
                    if (index > 0 && !dataset.points[index - 1].ignore && !point.ignore) {
                        if (this.options.bezierCurve) {
                            ctx.bezierCurveTo(
                                dataset.points[index - 1].controlPoints.outer.x,
                                dataset.points[index - 1].controlPoints.outer.y,
                                point.controlPoints.inner.x,
                                point.controlPoints.inner.y,
                                point.x,
                                point.y
                            );
                        } else {
                            ctx.lineTo(point.x, point.y);
                        }
                    } else if (index === 0 || dataset.points[index - 1].ignore) {
                        ctx.moveTo(point.x, point.y);
                    }

                    if (((dataset.points.length > index + 1 && dataset.points[index + 1].ignore) ||
                        dataset.points.length == index + 1) && !point.ignore) {
                        ctx.stroke();

                        if (this.options.datasetFill) {
                            ctx.lineTo(point.x, this.scale.endPoint);
                            ctx.lineTo(start.x, this.scale.endPoint);
                            ctx.fillStyle = dataset.fillColor;
                            ctx.closePath();
                            if (point.x != start.x) {
                                ctx.fill();
                            }
                        }
                        penDown = false;
                    }

                }, this);


                //Now draw the points over the line
                //A little inefficient double looping, but better than the line
                //lagging behind the point positions
                helpers.each(dataset.points, function(point) {
                    /**
                     * don't draw the dot if we are ignoring
                     */
                    if (!point.ignore)
                        point.draw();
                });

            }, this);
        }
    });

    var myLineChart = new Chart(ctx).MissingLine(data);
Resources:
http://jsfiddle.net/leighking2/sLgefm04/6/

Monday, September 21, 2015

How to add Chart to your SSRS report

You can add charts to your SSRS reports.

A typical chart contains 3 parts: Category Groups, Series Groups, and Values. You can treat a chart like a matrix, which has columns, row, and data. Category Groups in the chart is like columns in the matrix. Series Groups in the chart is like rows in the matrix. And the values in the chart is like data in the matrix.

Right click on the white part of the report design page, and choose Chart then a default chart will add to your report. 


Right click on the chart you just added, a Chart Data window will popup so you can choose Category Groups, Series Groups, and Values. 

For example, I need to compare two years sales data month by month. I put following SQL script into the dataset:

SELECT Sum (dbo.FactFinance.Amount) AS SumCount
    ,  dbo.DimDate.MonthNumberOfYear
     , dbo.DimDate.EnglishMonthName
     , dbo.DimDate.CalendarYear
FROM  dbo.FactFinance
    INNER JOIN dbo.DimDate on dbo.FactFinance.DateKey = dbo.DimDate.DateKey
Where dbo.DimDate.CalendarYear = @Year1 OR dbo.DimDate.CalendarYear = @Year2
GROUP BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear,
    dbo.DimDate.EnglishMonthName
ORDER BY dbo.DimDate.CalendarYear, dbo.DimDate.MonthNumberOfYear

I set CalendarYear as Category Group, and set EnglishMonthName as Series Group, and SumCount as Value. 

Also I need to order Series Group by MonthNumberOfYear, so I right click on the EnglishMonthName under Series Group, and choose MonthNumberOfYear as following.


The report is done. The source DB is from Microsoft AdventureWorksDW2012. The result would be like this.


Source:
https://msdn.microsoft.com/en-us/library/dd239351.aspx