Recipe - Failed Communication Page - Who Didn't Get It and Why

1. On the new page, open Page Zones and add a Dynamic Data block to the Main zone.
2. Open its settings (you want the Obsidian version).
3. Paste this into SQL Query:

DECLARE @Cid INT = TRY_CAST(NULLIF(@CommunicationId, '') AS INT);
DECLARE @Start DATE = ISNULL(TRY_CAST(NULLIF(@StartDate, '') AS DATE), DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1));
DECLARE @End DATE = ISNULL(TRY_CAST(NULLIF(@EndDate, '') AS DATE), EOMONTH(GETDATE()));

SELECT TOP 200
    c.Id AS [Value],
    FORMAT(c.SendDateTime, 'M/d/yy') + '  '
        + LEFT(ISNULL(NULLIF(c.[Name], ''), ISNULL(NULLIF(c.[Subject], ''), 'Communication ' + CAST(c.Id AS NVARCHAR(20)))), 60)
        + CASE c.CommunicationType WHEN 1 THEN ' (Email)' WHEN 2 THEN ' (SMS)' WHEN 3 THEN ' (Push)' ELSE '' END
        + '  -  ' + FORMAT(f.Failed, 'N0') + ' of ' + FORMAT(f.Recips, 'N0') + ' failed' AS [Text]
FROM Communication c
CROSS APPLY (
    SELECT COUNT(*) AS Recips, SUM(CASE WHEN cr.Status = 2 THEN 1 ELSE 0 END) AS Failed
    FROM CommunicationRecipient cr
    WHERE cr.CommunicationId = c.Id
) f
WHERE c.SenderPersonAliasId IN (
        SELECT pa.Id
        FROM PersonAlias pa
        WHERE pa.PersonId = TRY_CAST('{{ CurrentPerson.Id }}' AS INT)
    )
    AND c.Status = 3
    AND c.SendDateTime >= @Start
    AND c.SendDateTime < DATEADD(DAY, 1, @End)
    AND f.Recips >= 10
    AND f.Failed > 0
ORDER BY c.SendDateTime DESC;

SELECT
    @Cid AS CommunicationId,
    r.Medium,
    r.Reason,
    r.Kind,
    COUNT(*) AS Failed,
    CHECKSUM(r.Reason) AS ReasonKey
FROM CommunicationRecipient cr
LEFT JOIN EntityType et ON et.Id = cr.MediumEntityTypeId
CROSS APPLY (
    SELECT
        CASE
            WHEN et.[Name] LIKE '%.Email' THEN 'Email'
            WHEN et.[Name] LIKE '%Sms' THEN 'SMS'
            WHEN et.[Name] LIKE '%PushNotification' THEN 'Push'
            ELSE ISNULL(et.FriendlyName, '')
        END AS Medium,
        LEFT(ISNULL(NULLIF(cr.StatusNote, ''), '(no status note)'), 200) AS Reason,
        CASE
            WHEN cr.StatusNote LIKE 'No Email Address%'
                OR cr.StatusNote LIKE 'Recipient Email Address is not active%'
                OR cr.StatusNote LIKE 'Not delivering to previously bounced%'
                OR cr.StatusNote LIKE 'No Phone Number with Messaging Enabled%'
                OR cr.StatusNote LIKE 'Invalid%'
                OR cr.StatusNote LIKE '55[0-9] %'
                OR cr.StatusNote LIKE '%mailbox not found%'
                THEN 'Fixable'
            WHEN cr.StatusNote LIKE 'Communication Preference%'
                OR cr.StatusNote LIKE 'Email Preference%'
                OR cr.StatusNote LIKE 'Spam Reporting Address%'
                OR cr.StatusNote LIKE '%marked your messages as spam%'
                OR cr.StatusNote LIKE '%unsubscribed recipient%'
                THEN 'Preference'
            WHEN cr.StatusNote LIKE 'Person is inactive%'
                OR cr.StatusNote LIKE 'Person is deceased%'
                THEN 'Record'
            ELSE 'Provider'
        END AS Kind
) r
WHERE cr.CommunicationId = @Cid
    AND cr.Status = 2
GROUP BY r.Medium, r.Reason, r.Kind
ORDER BY COUNT(*) DESC, r.Reason;

SELECT
    FORMAT(@Start, 'yyyy-MM-dd') AS StartDate,
    FORMAT(@End, 'yyyy-MM-dd') AS EndDate;

4. Set Query Parameters to CommunicationId=;StartDate=;EndDate=.
5. Under Results Formatting, switch from Grid to Lava Template.
6. Leave Enabled Lava Commands set to none.
7. Paste this into Lava Template, then save:

{%- assign communicationId = 'Global' | PageParameter:'CommunicationId' | Default:'' -%}
{%- assign kind = 'Global' | PageParameter:'Kind' | Default:'' -%}
{%- assign selectedKey = 'Global' | PageParameter:'ReasonKey' -%}
{%- assign rows = table2.rows -%}
{%- assign dateRange = table3.rows | First -%}
{%- assign reasonCount = rows | Size -%}
<div class="panel panel-default">
    <div class="panel-body">
        <div class="row">
            <div class="col-md-6">
                <div class="form-group rock-drop-down-list">
                    <label class="control-label" for="failed-communication">Communication</label>
                    <div class="control-wrapper">
                        <select id="failed-communication" class="form-control chosen-select" data-placeholder="Pick a communication">
                            <option value=""></option>
                            {%- assign isListed = false -%}
                            {%- for sent in table1.rows -%}
                                {%- assign sentId = sent.Value | AsString -%}
                                {%- if sentId == communicationId -%}
                                    {%- assign isListed = true -%}
                                    <option value="{{ sentId }}" selected>{{ sent.Text | Escape }}</option>
                                {%- else -%}
                                    <option value="{{ sentId }}">{{ sent.Text | Escape }}</option>
                                {%- endif -%}
                            {%- endfor -%}
                            {%- if isListed == false and reasonCount > 0 -%}
                                <option value="{{ communicationId | Escape }}" selected>Communication {{ communicationId | Escape }}</option>
                            {%- endif -%}
                        </select>
                    </div>
                </div>
            </div>
            <div class="col-md-3">
                <div class="form-group">
                    <label class="control-label" for="failed-start-date">From</label>
                    <input id="failed-start-date" type="date" class="form-control" value="{{ dateRange.StartDate }}">
                </div>
            </div>
            <div class="col-md-3">
                <div class="form-group">
                    <label class="control-label" for="failed-end-date">To</label>
                    <input id="failed-end-date" type="date" class="form-control" value="{{ dateRange.EndDate }}">
                </div>
            </div>
        </div>
    </div>
</div>
<script>
    (function () {
        var setParameter = function (name, value) {
            var parameters = new URLSearchParams(window.location.search);
            parameters.set(name, value);
            if (name === 'CommunicationId') {
                parameters.delete('Kind');
                parameters.delete('ReasonKey');
            }
            window.location.search = parameters.toString();
        };
        var setDate = function (name, value) {
            if (value > '2000') {
                setParameter(name, value);
            }
        };
        $('#failed-communication').chosen({ width: '100%', search_contains: true }).on('change', function () {
            setParameter('CommunicationId', this.value);
        });
        $('#failed-start-date').on('change', function () {
            setDate('StartDate', this.value);
        });
        $('#failed-end-date').on('change', function () {
            setDate('EndDate', this.value);
        });
    })();
</script>
{%- if reasonCount > 0 -%}
    {%- assign firstRow = rows | First -%}
    {%- assign baseUrl = '?StartDate=' | Append:dateRange.StartDate | Append:'&EndDate=' | Append:dateRange.EndDate | Append:'&CommunicationId=' | Append:firstRow.CommunicationId -%}
    {%- assign totalFailed = 0 -%}
    {%- assign listCount = 0 -%}
    {%- for row in rows -%}
        {%- assign totalFailed = totalFailed | Plus:row.Failed -%}
        {%- if kind == '' or row.Kind == kind -%}
            {%- assign listCount = listCount | Plus:1 -%}
        {%- endif -%}
    {%- endfor -%}
    {%- assign totalText = totalFailed | Format:'#,##0' -%}
    {%- assign kindCards = 'Fixable|ti-tool|yellow-700|Can be corrected on the record,Preference|ti-bell-off|blue-700|The person chose this,Record|ti-user-off|gray-700|Inactive or deceased records,Provider|ti-cloud-off|red-700|Errors from the email or SMS provider' | Split:',' -%}
    {[ kpis columncount:'5' columncountmd:'5' columncountsm:'2' columnmin:'170px' ]}
        [[ kpi icon:'ti-send-off' value:'{{ totalText }}' label:'Failed' color:'indigo-700' url:'{{ baseUrl }}' ]][[ endkpi ]]
        {%- for card in kindCards -%}
            {%- assign cardParts = card | Split:'|' -%}
            {%- assign cardKind = cardParts[0] -%}
            {%- assign cardTotal = 0 -%}
            {%- for row in rows -%}
                {%- if row.Kind == cardKind -%}
                    {%- assign cardTotal = cardTotal | Plus:row.Failed -%}
                {%- endif -%}
            {%- endfor -%}
            {%- assign cardText = cardTotal | Format:'#,##0' -%}
            {%- assign cardUrl = baseUrl | Append:'&Kind=' | Append:cardKind -%}
            {%- if kind == cardKind -%}{%- assign cardUrl = baseUrl -%}{%- endif -%}
            [[ kpi icon:'{{ cardParts[1] }}' value:'{{ cardText }}' label:'{{ cardKind }}' color:'{{ cardParts[2] }}' description:'{{ cardParts[3] }}' url:'{{ cardUrl }}' ]][[ endkpi ]]
        {%- endfor -%}
    {[ endkpis ]}
    {%- if listCount > 0 -%}
        <div class="panel panel-default margin-t-md">
            <div class="panel-heading">
                <h3 class="panel-title">Failure Reasons</h3>
            </div>
            <div class="list-group">
                {%- assign shownCount = 0 -%}
                {%- for row in rows -%}
                    {%- if kind != '' and row.Kind != kind -%}{%- continue -%}{%- endif -%}
                    {%- if kind == '' and shownCount == 10 -%}{%- break -%}{%- endif -%}
                    {%- assign shownCount = shownCount | Plus:1 -%}
                    {%- assign reasonKey = row.ReasonKey | AsString -%}
                    {%- case row.Kind -%}
                        {%- when 'Fixable' -%}{%- assign kindClass = 'label-warning' -%}
                        {%- when 'Preference' -%}{%- assign kindClass = 'label-info' -%}
                        {%- when 'Record' -%}{%- assign kindClass = 'label-default' -%}
                        {%- else -%}{%- assign kindClass = 'label-danger' -%}
                    {%- endcase -%}
                    {%- if reasonKey == selectedKey -%}
                        <a href="{{ baseUrl }}" title="{{ row.Reason | Escape }}" class="list-group-item active d-flex align-items-center">
                    {%- else -%}
                        <a href="{{ baseUrl }}&amp;Kind={{ row.Kind }}&amp;ReasonKey={{ reasonKey }}" title="{{ row.Reason | Escape }}" class="list-group-item d-flex align-items-center">
                    {%- endif -%}
                        <span class="flex-grow-1 min-w-0 line-clamp-1 mr-3">{{ row.Reason | Escape }}</span>
                        <span class="label {{ kindClass }} mr-3">{{ row.Kind }}</span>
                        <strong>{{ row.Failed | Format:'#,##0' }}</strong>
                    </a>
                {%- endfor -%}
            </div>
            {%- if kind == '' and listCount > 10 -%}
                <div class="panel-footer">
                    <small class="text-muted">Showing the top 10 of {{ listCount }} reasons. Pick a Kind to see all of them.</small>
                </div>
            {%- endif -%}
        </div>
    {%- endif -%}
{%- endif -%}

1. Add another Dynamic Data block to the Main zone, below the dashboard. Leave this one on Grid not custom lava template.
2. Paste this into SQL Query:

DECLARE @Cid INT = TRY_CAST(NULLIF(@CommunicationId, '') AS INT);
DECLARE @RKey INT = TRY_CAST(NULLIF(@ReasonKey, '') AS INT);
DECLARE @MobileTypeId INT = (SELECT TOP 1 Id FROM DefinedValue WHERE [Guid] = '407E7E45-7B2E-4FCD-9605-ECB1339F2453');

SELECT
    p.Id AS Person,
    p.NickName + ' ' + p.LastName AS [Name],
    rs.[Value] AS RecordStatus,
    p.Email,
    CASE
        WHEN ISNULL(p.Email, '') = '' THEN ''
        WHEN p.IsEmailActive = 1 THEN 'Active'
        ELSE 'Inactive'
    END AS EmailStatus,
    CASE p.EmailPreference WHEN 0 THEN 'Allowed' WHEN 1 THEN 'No Bulk' WHEN 2 THEN 'Do Not Email' ELSE '' END AS EmailPreference,
    ph.NumberFormatted AS Mobile,
    CASE WHEN ph.Id IS NULL THEN '' WHEN ph.IsMessagingEnabled = 1 THEN 'On' ELSE 'Off' END AS Sms,
    r.Medium,
    r.Reason,
    r.Kind
FROM CommunicationRecipient cr
INNER JOIN PersonAlias pa ON pa.Id = cr.PersonAliasId
INNER JOIN Person p ON p.Id = pa.PersonId
LEFT JOIN DefinedValue rs ON rs.Id = p.RecordStatusValueId
LEFT JOIN EntityType et ON et.Id = cr.MediumEntityTypeId
OUTER APPLY (
    SELECT TOP 1 pn.Id, pn.NumberFormatted, pn.IsMessagingEnabled
    FROM PhoneNumber pn
    WHERE pn.PersonId = p.Id
    ORDER BY pn.IsMessagingEnabled DESC, CASE WHEN pn.NumberTypeValueId = @MobileTypeId THEN 0 ELSE 1 END, pn.Id
) ph
CROSS APPLY (
    SELECT
        CASE
            WHEN et.[Name] LIKE '%.Email' THEN 'Email'
            WHEN et.[Name] LIKE '%Sms' THEN 'SMS'
            WHEN et.[Name] LIKE '%PushNotification' THEN 'Push'
            ELSE ISNULL(et.FriendlyName, '')
        END AS Medium,
        LEFT(ISNULL(NULLIF(cr.StatusNote, ''), '(no status note)'), 200) AS Reason,
        CASE
            WHEN cr.StatusNote LIKE 'No Email Address%'
                OR cr.StatusNote LIKE 'Recipient Email Address is not active%'
                OR cr.StatusNote LIKE 'Not delivering to previously bounced%'
                OR cr.StatusNote LIKE 'No Phone Number with Messaging Enabled%'
                OR cr.StatusNote LIKE 'Invalid%'
                OR cr.StatusNote LIKE '55[0-9] %'
                OR cr.StatusNote LIKE '%mailbox not found%'
                THEN 'Fixable'
            WHEN cr.StatusNote LIKE 'Communication Preference%'
                OR cr.StatusNote LIKE 'Email Preference%'
                OR cr.StatusNote LIKE 'Spam Reporting Address%'
                OR cr.StatusNote LIKE '%marked your messages as spam%'
                OR cr.StatusNote LIKE '%unsubscribed recipient%'
                THEN 'Preference'
            WHEN cr.StatusNote LIKE 'Person is inactive%'
                OR cr.StatusNote LIKE 'Person is deceased%'
                THEN 'Record'
            ELSE 'Provider'
        END AS Kind
) r
WHERE cr.CommunicationId = @Cid
    AND cr.Status = 2
    AND (@Kind = '' OR r.Kind = @Kind)
    AND (@RKey IS NULL OR CHECKSUM(r.Reason) = @RKey)
ORDER BY p.LastName, p.NickName

3. Set these and leave the rest alone (you may want to adjust them to your case, and also set filtering):

4. Paste this into Grid Header Content:

{%- assign communicationId = 'Global' | PageParameter:'CommunicationId' -%}
{%- assign kind = 'Global' | PageParameter:'Kind' -%}
{%- assign reasonKey = 'Global' | PageParameter:'ReasonKey' -%}
{%- assign startDate = 'Global' | PageParameter:'StartDate' -%}
{%- assign endDate = 'Global' | PageParameter:'EndDate' -%}
{%- assign count = rows | Size -%}
{%- if communicationId == null or communicationId == '' -%}
<div class="alert alert-info alert-grid">Pick one of your communications above to see who did not receive it and why.</div>
{%- else -%}
<div class="panel-body">
    <strong>{{ count | Format:'#,##0' }}</strong> failed {{ 'recipient' | PluralizeForQuantity:count }}
    {%- if kind and kind != '' %} <span class="label label-info">{{ kind | Escape }}</span>{% endif -%}
    {%- if reasonKey and reasonKey != '' %} <span class="label label-default">{{ rows | First | Property:'Reason' | Default:'Selected reason' | Escape }}</span>
    <a href="?CommunicationId={{ communicationId | EscapeDataString }}&amp;StartDate={{ startDate | EscapeDataString }}&amp;EndDate={{ endDate | EscapeDataString }}" class="btn btn-default btn-xs">Show all failures</a>{% endif %}
</div>
{%- endif -%}

5. Under Column Configuration, click Reload Columns From Query.
6. Set the Person column's type to Person. This is what makes it a person report, and without it the grid actions don't show up.

1. Reload the page. If it's early in the month, set From further back so the dropdown has something in it.
2. Type part of a name in the dropdown and pick one of your communications.
3. The dashboard and people fill in. Check that the Failed card matches the number in the dropdown.
4. Click a "Kind" card. The list and people narrow to that Kind, and clicking the card again clears it.
5. Click a reason. The page reloads with that reason highlighted and only those people in Failed Recipients.
6. Click the reason again to go back to everyone. Show all failures in the Failed Recipients header does the same thing.
7. Click a row to open their profile edit page.