Disclaimer: I am not an HTML or SQL expert

This will be a recipe on how to start to create a dashboard to quickly see the key items you care about. I built it for campuses but did not want to have to edit several pages when a change needed to be made. So, it is really one single page and the URL parameters set the campus. This makes adding new items to the page very easy. First I will show you an example of the page and then get into how to create it.

Campus selection screen

Screen1

Campus metrics screen

Screen2

1) Let's start with the code for the campus selection screen. Before you can create this screen, you need the campus metrics page created. It doesn't have to have anything on it but you need the page route. This code is in an HTML block.


<style>
btn,
.btn-size {
    width: 100%;
}
</style

<p>
<br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=3" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Anderson</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=5" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Binford</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=1" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Carmel</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=2" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Fishers</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=4" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Greater Lafayette</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=6" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Kokomo</span></a><br><br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=7" class="btn btn-primary btn-size" target="_blank"><span style="font-size: 18px;">Westfield</span></a><br><br>
</p>

2) The code at the top of the campus metrics page. It allows you to switch campuses easily. It is basically the code we just looked a with the key of getting the URL parameter for CampusId. I set the page title off of this in this block.


{% assign campus = 'Global' | PageParameter:'CampusId' %}

<style>
btn,
.btn-size {
    width: 200px;
}
</style
<br>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=3" class="btn btn-primary btn-size"><span style="font-size: 18px;">Anderson</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=5" class="btn btn-primary btn-size"><span style="font-size: 18px;">Binford</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=1" class="btn btn-primary btn-size"><span style="font-size: 18px;">Carmel</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=2" class="btn btn-primary btn-size"><span style="font-size: 18px;">Fishers</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=4" class="btn btn-primary btn-size"><span style="font-size: 18px;">Greater Lafayette</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=6" class="btn btn-primary btn-size"><span style="font-size: 18px;">Kokomo</span></a>
<a href="https://rock.northviewchurch.us/CampusMetrics?CampusId=7" class="btn btn-primary btn-size"><span style="font-size: 18px;">Westfield</span></a>


{% case campus %}
{% when '1' %}
<h3><b>Carmel Campus</b></h3>
{% when '2' %}
<h3><b>Fishers Campus</b></h3>
{% when '3' %}
<h3><b>Anderson Campus</b></h3>
{% when '4' %}
<h3><b>Greatest Lafayette Campus</b></h3>
{% when '5' %}
<h3><b>Binford Campus</b></h3>
{% when '6' %}
<h3><b>Kokomo Campus</b></h3>
{% when '7' %}
<h3><b>Westfield Campus</b></h3>
{% endcase %}

3) Next is an HTML block that contains a graph for campus attendace. This chart is based on this Should The Boulder article. I made changes to remove the option to select campuses, so it would show the campus I wanted. I removed the code that displayed the campus buttons and then altered this line "var campus = '{{ campus }}';" The campus variable is pulling the URL parameter.

4) Now the reason most people will be reading this article. How to display the data below the chart. A big part of this is determining what you want to display. I will provide a few examples. Below the chart is three zones. I have an HTML block in each zone. The SQL query is done with lava in the HTML block. First I get the URL parameter again and set some variables I am going to use for some calculations. All of the following is in the same HTML block (along with a lot more not shown)


{% assign campus = 'Global' | PageParameter:'CampusId' %}
{% assign amount = 0 %}
{% assign one = 100 %}
{% assign weeks = 44 %}

5) This is the query inside the HTML block. You have to enable SQL inside the HTML block settings. The key here is the first line, putting the results into a variable called 'eams'


{% sql return:'teams' %}
SELECT COUNT(DISTINCT gpm.PersonId) AS [Count]
FROM GroupMember gpm
JOIN [Group] gp ON gpm.GroupId=gp.Id
WHERE gp.GroupTypeId IN (72,80,81,82,83,84,85,86,87,157,159,162,165,167,168,169,170,171,172,175,176,177) /* Serving Group Type ID */
AND gp.CampusId = {{ campus }} /* Anderson Campus ID */
AND gpm.GroupMemberStatus = 1 /* Active Members */
AND gp.IsActive = 1 /* Active Groups */
AND gp.IsArchived = 0; /* Not Archived Groups */
{% endsql %}

6) I get the result and store it into a variable called 'teamCount' 


{% for row in teams %} {% assign teamCount = row.Count %} {% endfor %}
{% assign avgTeams = teamCount | DividedBy:avgweek %}
{% assign avgTeams1 = avgTeams | Times:one %}

7) Then to display that


<table>
<tr><td>Groups</td><td>{{groupCount}} ({{avgGroups1}}%)</td></tr>
<tr><td>Teams</td><td>{{teamCount}} ({{avgTeams1}}%)</td></tr>
<tr><td>Growth Plan</td><td>{{growCount}} ({{avgGrow1}}%)</td></tr>
<tr><td>Salvations Last 12</td><td>{% for row in salvationslast %} {{ row.Sum }} {% endfor %}</td></tr>
<tr><td>Salvations YTD</td><td>{% for row in salvations %} {{ row.Sum }} {% endfor %}</td></tr>
<tr><td>Uncharted Giving Last 12</td><td>${% for row in giving %}{{ row.Sum | Format:'#,##0.00' }} {%assign amount = row.Sum%}{% endfor %}</td></tr>
<tr><td>Uncharted Unique Givers Last 12</td><td>{% for row in units %} {{ row.Count }} {%assign unit = row.Count%} {% endfor %}</td></tr>
<tr><td>Uncharted Giving Per Unique Last 12</td><td>${{amount | DividedBy:unit | Format:'#,##0.00'}}</td></tr>
</table>

This has been a very quick how-to. The different SQL queries to get different items is really a different topic all together. Feel free to comment with any questions or suggestions.

Here is the entire code in the left most HTML zone


{% assign campus = 'Global' | PageParameter:'CampusId' %}
{% assign amount = 0 %}
{% assign one = 100 %}
{% assign weeks = 44 %}

{% sql return:'teams' %}
SELECT COUNT(DISTINCT gpm.PersonId) AS [Count]
FROM GroupMember gpm
JOIN [Group] gp ON gpm.GroupId=gp.Id
WHERE gp.GroupTypeId IN (72,80,81,82,83,84,85,86,87,157,159,162,165,167,168,169,170,171,172,175,176,177) /* Serving Group Type ID */
AND gp.CampusId = {{ campus }} /* Anderson Campus ID */
AND gpm.GroupMemberStatus = 1 /* Active Members */
AND gp.IsActive = 1 /* Active Groups */
AND gp.IsArchived = 0; /* Not Archived Groups */
{% endsql %}

{% sql return:'groups' %}
SELECT COUNT(DISTINCT gpm.PersonId) AS [Count]
FROM GroupMember gpm
JOIN [Group] gp ON gpm.GroupId=gp.Id
WHERE gp.GroupTypeId = 124 /* Small Group Type ID */
AND gp.CampusId = {{ campus }} /* Anderson Campus ID */
AND gpm.GroupMemberStatus = 1 /* Active Members */
AND gp.IsActive = 1 /* Active Groups */
AND gp.IsArchived = 0; /* Not Archived Groups */
{% endsql %}

{% sql return:'growth' %}
SELECT COUNT(*) AS [Count]
FROM Person P
JOIN GroupMember gpm ON P.Id=gpm.PersonId
JOIN [Group] gp ON gpm.GroupId=gp.Id
JOIN AttributeValue AV ON AV.EntityId = P.Id
JOIN Attribute A ON A.Id = AV.AttributeId AND A.[Key] = 'GrowthAreaDate'
WHERE gp.GroupTypeId IN (10) /* Family Group Type ID */
AND gp.CampusId = {{ campus }} /* Anderson Campus ID */
AND AV.ValueAsDateTime IS NOT NULL;
{% endsql %}

{% sql return:'giving' %}
SELECT SUM(ftd.Amount) as [Sum]
FROM FinancialTransaction ft
JOIN FinancialTransactionDetail ftd ON ft.Id=ftd.TransactionId
JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId=pa.Id
JOIN GroupMember gpm ON pa.PersonId=gpm.PersonId
JOIN [Group] gp ON gpm.GroupId=gp.Id
WHERE gp.GroupTypeId = 10
AND gp.CampusId = {{ campus }}
AND ftd.AccountId = 10
AND ft.TransactionDateTime  >= DATEADD(month,-12, GETDATE()-1);
{% endsql %}

{% sql return:'units' %}
SELECT COUNT(DISTINCT ft.AuthorizedPersonAliasId) AS [Count]
FROM FinancialTransaction ft
JOIN FinancialTransactionDetail ftd ON ft.Id=ftd.TransactionId
JOIN PersonAlias pa ON ft.AuthorizedPersonAliasId=pa.Id
JOIN GroupMember gpm ON pa.PersonId=gpm.PersonId
JOIN [Group] gp ON gpm.GroupId=gp.Id
WHERE gp.GroupTypeId = 10
AND gp.CampusId = {{ campus }}
AND ftd.AccountId = 10
AND ft.TransactionDateTime  >= DATEADD(month,-12, GETDATE()-1);
{% endsql %}

{% sql return:'salvations' %}
SELECT CONVERT(int,ROUND(SUM(mv.YValue),0)) AS [Sum]
FROM MetricValue mv
JOIN MetricValuePartition mvp ON mv.Id=mvp.MetricValueId
WHERE [MetricId] = 1 /* Metric ID For Salvations */
AND mvp.MetricPartitionId = 1 /* Campus Partition */
AND mvp.EntityId = {{ campus }} /* Anderson Campus Entity Id */
AND DATEPART(year, mv.MetricValueDateTime) = '2019'
AND mv.YValue IS NOT NULL;
{% endsql %}

{% sql return:'salvationslast' %}
SELECT CONVERT(int,ROUND(SUM(mv.YValue),0)) AS [Sum]
FROM MetricValue mv
JOIN MetricValuePartition mvp ON mv.Id=mvp.MetricValueId
WHERE [MetricId] = 1
AND mvp.MetricPartitionId = 1
AND mvp.EntityId = {{ campus }}
AND mv.MetricValueDateTime  >= DATEADD(month,-12, GETDATE()-1)
AND mv.YValue IS NOT NULL;
{% endsql %}

{% sql return:'avg' %}
SELECT CONVERT(int,ROUND(SUM(mv.YValue),0)) AS [Sum]
FROM MetricValue mv
JOIN MetricValuePartition mvp ON mv.Id=mvp.MetricValueId
WHERE mv.MetricId = 2 /* Metric ID For Total */
AND mvp.MetricPartitionId = 2 /* Campus Partition */
AND mvp.EntityId = {{ campus }} /* Anderson Campus Entity Id */
AND mv.MetricValueDateTime  >= DATEADD(month,-12, GETDATE()-1) /* Last 7 days */
AND mv.MetricValueDateTime  NOT IN ('2018-03-29','2018-03-30','2018-03-31','2018-04-01')  /* Easter */
AND mv.MetricValueDateTime  NOT IN ('2018-04-07','2018-04-08', '2018-04-14','2018-04-15', '2018-04-21','2018-04-22', '2018-04-28','2018-04-29', '2018-05-05','2018-05-06') /* ATM */
AND mv.MetricValueDateTime  NOT IN ('2018-12-20','2018-12-22','2018-12-23','2018-12-24')  /* Christmas */
AND mv.YValue IS NOT NULL;
{% endsql %}

<style>
table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 100%;
  font-size: 22px;
}

td, th {
  border: 1px solid #dddddd;
  text-align: left;
  padding: 8px;
}

tr:nth-child(even) {
  background-color: #dddddd;
}
</style>

<div class="col-md-12">
<div class="panel panel-block">
<div class="panel-heading">
<h4 class="panel-title"><b>Campus Wide</b></h4>
</div>
<ul class="list-group list-group-panel">
<div class="panel-body">

{% for row in groups %} {% assign groupCount = row.Count %} {% endfor %}
{% for row in avg %} {% assign sum = row.Sum %} {% endfor %}
{% assign avgweek = sum | DividedBy:weeks %}
{% assign avgGroups = groupCount | DividedBy:avgweek %}
{% assign avgGroups1 = avgGroups | Times:one %}

{% for row in teams %} {% assign teamCount = row.Count %} {% endfor %}
{% assign avgTeams = teamCount | DividedBy:avgweek %}
{% assign avgTeams1 = avgTeams | Times:one %}

{% for row in growth %} {% assign growCount = row.Count %} {% endfor %}
{% assign avgGrow = growCount | DividedBy:avgweek %}
{% assign avgGrow1 = avgGrow | Times:one %}

<table>
<tr><td>Groups</td><td>{{groupCount}} ({{avgGroups1}}%)</td></tr>
<tr><td>Teams</td><td>{{teamCount}} ({{avgTeams1}}%)</td></tr>
<tr><td>Growth Plan</td><td>{{growCount}} ({{avgGrow1}}%)</td></tr>
<tr><td>Salvations Last 12</td><td>{% for row in salvationslast %} {{ row.Sum }} {% endfor %}</td></tr>
<tr><td>Salvations YTD</td><td>{% for row in salvations %} {{ row.Sum }} {% endfor %}</td></tr>
<tr><td>Uncharted Giving Last 12</td><td>${% for row in giving %}{{ row.Sum | Format:'#,##0.00' }} {%assign amount = row.Sum%}{% endfor %}</td></tr>
<tr><td>Uncharted Unique Givers Last 12</td><td>{% for row in units %} {{ row.Count }} {%assign unit = row.Count%} {% endfor %}</td></tr>
<tr><td>Uncharted Giving Per Unique Last 12</td><td>${{amount | DividedBy:unit | Format:'#,##0.00'}}</td></tr>
</table>

</div>
</ul>
</div>
</div>