Campus Attendance Breakdown Charts

  • By Daniel Hazelbaker 8 Months Ago

Summary

ChartSample.png

Everybody likes graphs and charts. But everybody wants them slightly different.

"I want to only see the data for the North Campus"
"I only care about seeing Childrens numbers"
"Why can't I see all this data on one page?"

I tell ya. It's enough to drive an administrator up the wall. Well, I'm sorry to say we can't solve all your problems, but we can solve a few of them. We are going to go over how to create an interactive Metric chart that lets the user pick which campus and which metric they see, all on a single page.

Prerequisites

This article makes the following assumptions and will not work unless they are true.

  1. You are running Rock v7.0 or later.
  2. You have your weekend service metrics partitioned by Campus and Schedule. This means you have a single metric for Adults (partitioned by Campus and Schedule), a single metric for Nursery (partitioned by Campus and Schedule), and so on. You can have as many metrics as you want, separated by whatever ages you want, but you cannot have one metric for Campus A and another metric for Campus B.
  3. All metric attendance data should be entered for the Sunday date. For example, if you have a Saturday 6pm service then you should enter the Sunday date and select the Schedule for the Saturday 6pm service. This is required in order for the grouping by weekend to work correctly.

Metric Setup

First lets cover some fictitious metrics. Lets assume we have created the following metrics with their Id numbers. Remember, each of these must be partitioned by Campus and Schedule.

  • 173: Adults
  • 174: Youth - HS
  • 175: Youth - MS
  • 176: Children
  • 177: Nursery

The numbers above represent the MetricId, this is different than what shows in the URL when you are viewing the Metric. That number in the URL is the MetricCategoryId. This means you will need to do some SQL to determine the MetricId. The following SQL should give you the MetricId from the MetricCategoryId.

SELECT [MetricId] FROM [MetricCategory] WHERE [Id] = ##MetricCategoryIdNumber##

Campus Setup

Next, we are going to assume you have 4 campuses, along with their id numbers.

  • 3: North Campus
  • 4: East Campus
  • 5: South Campus
  • 6: West Campus

SQL Query

Okay, so now we need a SQL query to get this data. But we need this somewhere that our dashboard page can get to it. Rock v7 introduced something called Lava Webhooks. This allows you to create your own API endpoints that are powered by Lava. Go to your Defined Types and look for one called "Lava Webhook". Once you are there, you need to add a new Value to that.

  • Value = /AttendanceData
  • Enabled Lava Commands = Sql
  • Response Content Type = application/json

Template:

{% sql %}
SELECT
    SUM([MV].[YValue]) AS [Value]
    , [MV].[MetricValueDateTime] AS [DateTime]
    , [C].[Name] AS [Campus]
    , [S].[Name] AS [Schedule]
FROM [MetricValue] AS [MV]
INNER JOIN [Metric] AS [M] ON [M].[Id] = [MV].[MetricId]
INNER JOIN [MetricPartition] AS [MPCampus] ON [MPCampus].[MetricId] = [M].[Id]
INNER JOIN [EntityType] AS [ETCampus] ON [ETCampus].[Id] = [MPCampus].[EntityTypeId] AND [ETCampus].[Name] = 'Rock.Model.Campus'
INNER JOIN [MetricValuePartition] AS [MVCampus] ON [MVCampus].[MetricValueId] = [MV].[Id] AND [MVCampus].[MetricPartitionId] = [MPCampus].[Id]
INNER JOIN [Campus] AS [C] ON [C].[Id] = [MVCampus].[EntityId]
INNER JOIN [MetricPartition] AS [MPSchedule] ON [MPSchedule].[MetricId] = [M].[Id]
INNER JOIN [EntityType] AS [ETSchedule] ON [ETSchedule].[Id] = [MPSchedule].[EntityTypeId] AND [ETSchedule].[Name] = 'Rock.Model.Schedule'
INNER JOIN [MetricValuePartition] AS [MVSchedule] ON [MVSchedule].[MetricValueId] = [MV].[Id] AND [MVSchedule].[MetricPartitionId] = [MPSchedule].[Id]
INNER JOIN [Schedule] AS [S] ON [S].[Id] = [MVSchedule].[EntityId]
LEFT JOIN [dbo].[ufnUtility_CsvToTable]('{{ QueryString.Metric | Replace:"'","''" }}') AS [SSMetric] ON [SSMetric].[item] = [MV].[MetricId]
LEFT JOIN [dbo].[ufnUtility_CsvToTable]('{{ QueryString.Campus | Replace:"'","''" }}') AS [SSCampus] ON [SSCampus].[item] = [C].[Id]
WHERE [MV].[MetricId] IN (173, 174, 175, 176, 177)
  AND ('{{ QueryString.Metric | Replace:"'","''" }}' = '' OR [SSMetric].[item] IS NOT NULL)
  AND ('{{ QueryString.Campus | Replace:"'","''" }}' = '' OR [SSCampus].[item] IS NOT NULL)
  AND [MV].[MetricValueDateTime] > DATEADD(YEAR, -1, GETDATE())
GROUP BY [MV].[MetricValueDateTime],[S].[Name],[C].[Name]
ORDER BY [MV].[MetricValueDateTime]
{% endsql %}
{{ results | ToJSON }}

Towards the bottom, you will see this line: WHERE [MV].[MetricId] IN (173, 174, 175, 176, 177). These numbers need to be changed to reflect your Metric Id numbers.

Now that is a big, complex query. But what it's doing is actually pretty simple. The top two-thirds is just gathering all the various tables we need to get the right metric values. Remember we partitioned by Campus and Schedule so we need to filter down to the specific Campus and Schedule that the user will be looking at. The bottom third is where all the magic happens, starting with the WHERE clause we mentioned above.

That first WHERE line is a safety net. It limits the metrics that we can pull data for to the specific metrics related to attendance data. This way if the user edits things on the page and tries to request data for a different metric they will not see any of that data.

Next, we use Lava and the Query String to get the user's requested Metric and Campus values. These come in the form of a comma separated list. This is a two part check. The first part is the two lines above the WHERE statement. Those two lines tell us if the requested Metric and Campus are contained in the specific value (row). The two lines below the WHERE statement actually filter. Basically, we are saying "if they didn't specify a campus, return data for all campuses. Otherwise only the campus they specified." And the same is true for the Metric.

Finally, we are going to pull data for only the last one year.

DefinedTypeDetail.png

You can test to make sure this is working by going to this URL, relative to your Rock domain, /Webhooks/Lava.ashx/AttendanceData. If you get any errors then something did not get setup correctly above.

Page Config

Okay, so now we can get the data, but we want to display it all pretty. Either create a new page or pick the page you want the chart to show up on. Then go to the Page Settings. Under the Advanced Settings tab you need to add this line to the Header Content.

<script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.7.1/Chart.bundle.min.js"></script>

Next you need to add a HTML Content block to the page. This is where we will display the chart. Once the block is on the page edit the content. You need to paste this code into the content area, but make sure you are in Code Editor mode first. That is the left-most button in the editor toolbar.

<div class="margin-b-sm row type-buttons">
    <div class="col-sm-6">
        <div class="btn btn-default btn-block btn-show-breakdown default">Breakdown</div>
    </div>
    <div class="col-sm-6">
        <div class="btn btn-default btn-block btn-show-totals">Campus Totals</div>
    </div>
</div>

<div class="well well-breakdown" style="display: none;">
    <div class="margin-b-sm row campus-buttons">
        <!-- data-id specifies the Id number of the campus -->
        <div class="col-sm-3"><div class="btn btn-default btn-block default" data-id="3">North Campus</div></div>
        <div class="col-sm-3"><div class="btn btn-default btn-block" data-id="4">East Campus</div></div>
        <div class="col-sm-3"><div class="btn btn-default btn-block" data-id="5">South Campus</div></div>
        <div class="col-sm-3"><div class="btn btn-default btn-block" data-id="6">West Campus</div></div>
    </div>

    <div class="margin-b-md row metric-buttons">
        <!-- data-id specifies the Id number of the metric -->
        <div class="col-sm-2"><div class="btn btn-default btn-block default" data-id="173">Adults</div></div>
        <div class="col-sm-2"><div class="btn btn-default btn-block" data-id="174">Youth - HS</div></div>
        <div class="col-sm-2"><div class="btn btn-default btn-block" data-id="175">Youth - MS</div></div>
        <div class="col-sm-2"><div class="btn btn-default btn-block" data-id="176">Children</div></div>
        <div class="col-sm-2"><div class="btn btn-default btn-block" data-id="177">Nursery</div></div>
    </div>
</div>

<div class="chart-container" style="position: relative; height:300px; width:100%;">
    <canvas id="attendanceChart"></canvas>
</div>

<script>
(function() {
    var groupBy = function(xs, key) {
        return xs.reduce(function(rv, x) {
            (rv[x[key]] = rv[x[key]] || []).push(x);
            return rv;
        }, {});
    };

    var config = {
        type: 'line',
        data: {
            datasets: []
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            animation: {
                duration: 2500,
            },
            legend: {
                position: 'right'
            },
            scales: {
                xAxes: [{
                    type: 'time',
                    time: {
                        displayFormats: {
                            'hour': 'MM/YY',
                            'day': 'MM/YY',
                            'week': 'MM/YY',
                            'month': 'MM/YY',
                            'quarter': 'MM/YY',
                            'year': 'MM/YY',
                        },
                        tooltipFormat: "MM/DD/YYYY"
                    }
                } ]
            }
        }
    };
    
    var chartColors = [
        "rgb(54, 162, 235)",
        "rgb(75, 192, 192)",
        "rgb(255, 159, 64)",
        "rgb(153, 102, 255)",
        "rgb(255, 99, 132)",
        "rgb(255, 205, 86)",
        "rgb(19, 141, 117)",
        "rgb(160, 64, 0)",
        "rgb(27, 79, 114)",
        "rgb(210, 180, 222)" ];

    var ctx = document.getElementById("attendanceChart").getContext('2d');
    var chart = new Chart(ctx, config);
    
    var getTotalDataSet = function(rows) {
        var groups = groupBy(rows, 'DateTime');
        var dataset = { label: 'Total', borderWidth: 1, fill: false, data: [] };

        for (k in groups) {
            var value = 0;
            groups[k].forEach((row) => {
                value += row.Value;
            });
            dataset.data.push({ x: k, y: value });
        }
        
        return dataset;
    };
    
    var addSummedDataSets = function(rows, key) {
        var groups = groupBy(rows, key);
        for (k in groups) {
            var dates = groupBy(groups[k], 'DateTime');
            var dataset = { label: k, borderWidth: 1, fill: false, data: [] };

            for (d in dates) {
                var value = 0;
                dates[d].forEach((dRow) => {
                    value += dRow.Value;
                });
                dataset.data.push({ x: d, y: value });
            }

            dataset.borderColor = chartColors[config.data.datasets.length % chartColors.length];
            dataset.backgroundColor = dataset.borderColor;
            chart.data.datasets.push(dataset);
        }
    };
    
    var addDataSetsInGroups = function(groups) {
        for (k in groups) {
            var dataset = { label: k, borderWidth: 1, fill: false, data: [] };

            dataset.borderColor = chartColors[config.data.datasets.length % chartColors.length];
            dataset.backgroundColor = dataset.borderColor;
            
            groups[k].forEach((row) => {
                dataset.data.push({ x: row.DateTime, y: row.Value });
            });

            chart.data.datasets.push(dataset);
        }
    };
    
    var updateChart = function() {
        var campus = '';
        var metric = '';
        
        if ($('.btn-show-breakdown').hasClass('btn-primary')) {
            campus = $('.campus-buttons .btn-primary').map(function () { return $(this).data('id'); }).get().join(',');
            metric = $('.metric-buttons .btn-primary').map(function () { return $(this).data('id'); }).get().join(',');
        }

        $.ajax({
            url: '/Webhooks/Lava.ashx/AttendanceData?Campus=' + campus + '&Metric=' + metric,
            success: function (rows) {
                chart.data.datasets = [];

                if ($('.btn-show-totals').hasClass('btn-primary')) {
                    var ds = getTotalDataSet(rows);
                    ds.borderColor = chartColors[config.data.datasets.length % chartColors.length];
                    ds.backgroundColor = ds.borderColor;
                
                    chart.data.datasets.push(ds);
                    addSummedDataSets(rows, 'Campus');
                }
                else {
                    var ds = getTotalDataSet(rows);
                    ds.borderColor = chartColors[config.data.datasets.length % chartColors.length];
                    ds.backgroundColor = ds.borderColor;
                    ds.hidden = true;
                    chart.data.datasets.push(ds);

                    addDataSetsInGroups(groupBy(rows, 'Schedule'));
                }

                chart.update();
            }
        });
    };

    Sys.Application.add_load(function () {
        $('.campus-buttons .btn.default').addClass('btn-primary').removeClass('btn-default');
        $('.metric-buttons .btn.default').addClass('btn-primary').removeClass('btn-default');
        $('.type-buttons .btn.default').addClass('btn-primary').removeClass('btn-default');

        if ($('.btn-show-totals').hasClass('btn-primary')) {
            $('.well-breakdown').hide();
        }
        else {
            $('.well-breakdown').show();
        }

        $('.campus-buttons .btn').on('click', function () {
            $('.campus-buttons .btn').removeClass('btn-primary').addClass('btn-default');
            $(this).toggleClass('btn-default').toggleClass('btn-primary');

            updateChart();
        });
        
        $('.metric-buttons .btn').on('click', function () {
            if ($(this).hasClass('btn-primary') && $('.metric-buttons .btn-primary').length == 1) {
                return false;
            }

            //$('.metric-buttons .btn').removeClass('btn-primary').addClass('btn-default');
            $(this).toggleClass('btn-default').toggleClass('btn-primary');

            updateChart();
        });
        
        $('.type-buttons .btn').on('click', function () {
            $('.type-buttons .btn').removeClass('btn-primary').addClass('btn-default');
            $(this).toggleClass('btn-default').toggleClass('btn-primary');

            if ($('.btn-show-totals').hasClass('btn-primary')) {
                $('.well-breakdown').hide(350);
            }
            else {
                $('.well-breakdown').show(350);
            }
            
            updateChart();
        });
        
        updateChart();
    });
})();
</script>

Yikes that is a lot of stuff. Don't worry, most of that works out of the box. There are only a few things you will need to change.

First we need to change the buttons of available campuses. At line 12 you will see this section:

<!-- data-id specifies the Id number of the campus -->
<div class="col-sm-3"><div class="btn btn-default btn-block default" data-id="3">North Campus</div></div>
<div class="col-sm-3"><div class="btn btn-default btn-block" data-id="4">East Campus</div></div>
<div class="col-sm-3"><div class="btn btn-default btn-block" data-id="5">South Campus</div></div>
<div class="col-sm-3"><div class="btn btn-default btn-block" data-id="6">West Campus</div></div>

That defines that campus buttons to display. You will need to modify that list of div tags to be your campuses. The data-id="3" specifies the Id number of the campus and North Campus is, obviously, the name of the campus to display in the button. Also notice that the first item has an extra class called default. This is used to identify which campus button should be selected by default when the page loads.

Next we need to do the same thing for schedules. A few lines down you will see a similar section:

<!-- data-id specifies the Id number of the metric -->
<div class="col-sm-2"><div class="btn btn-default btn-block default" data-id="173">Adults</div></div>
<div class="col-sm-2"><div class="btn btn-default btn-block" data-id="174">Youth - HS</div></div>
<div class="col-sm-2"><div class="btn btn-default btn-block" data-id="175">Youth - MS</div></div>
<div class="col-sm-2"><div class="btn btn-default btn-block" data-id="176">Children</div></div>
<div class="col-sm-2"><div class="btn btn-default btn-block" data-id="177">Nursery</div></div>

The same rules apply as what you changed for the campus buttons. But in this case the data-id="###" value is the Id of the Metric.

Once you are done making these changes, click Save. Your chart will not appear right away but if you reload the page it should show up.

You can also add and remove buttons if you have more or fewer campuses and metrics. The code will pick up the change and work fine with however many buttons you have.

ChartSample.png

Now, if you click the various buttons you should see the chart update automatically. Only a single campus can be selected at any given time, but you can select multiple metrics at once.

Well done, your interactive chart is all ready!

Hey! Speaking of interactive chart, did you know you can click the chart legend titles on the left to toggle those lines off and on? Give it a try!

@danielhazelbaker
Shepherd Church

Prolific coding monkey and avid Overwatch player. If there is a "right way" to do something you can be sure Daniel's approach will be a "near miss".