Recipe - Registration Instance Fees Report

Skill level: Beginner

Organization: Simple

---

{# strip images & classes from the HTML but otherwise leave structure #}

This report will help your event organizers keep quick and easy track of the fees paid based on each option and quantities selected for an event in one quick and easy report.

Set Up:

1. Navigate to the Event Registration Page (Tools -> Event Registration)
   
   
2. Select an Active Registration Instance from the 'Active Registration Instance List' Block
   
   
3. On the Fees sub-page of the Registration Instance show the Page Zones by selecting the Page Zone button on the admin bar or by pressing Alt + Z on Windows or Ctrl + Opt + Z for Mac
   
   
4. Scroll down to the bottom of the page and find 'Section D' and select the 'Zone Blocks' setting
   
   
5. Press the '+' on the 'Section D Zone' block or press Alt + N on Windows or Ctrl + Opt + N for Mac
   
   
6. Add a name to the Name field. Make sure the default Type of 'HTML Content' is selected, select 'Save,' and select 'Done' last.
   
   
7. Show the Block Configuration Setting by selecting 'Block Configuration' on the Admin Bar or by pressing Alt + B on Windows or Ctrl + Opt + B for Mac
   
   
8. Hover over the arrow in Section D where the new HTML Block that was just created and press the 'Block Properties' button
   
   
9. Under the 'Enable Lava Commands' settings select the 'Rock Entity' and 'SQL' setting and select 'Save'
   
   
10. Hover over the arrow in Section D where the new Dynamic Data Block that was just created and press the 'Edit' button
    
    
11. Set the Parameter to

    RegistrationInstanceId=

    and ensure that Wrap in Panel is selected with a panel title of Registration Instance Fee Totals
    
    
12. Copy this SQL to the Dynamic Data Block Query field
    
    

    
    SELECT
        COALESCE(rtf.[Name], 'Totals') as [FeeName]
        , CASE WHEN rtf.[Name] is not null and rrf.[Option] is null THEN CONCAT(rtf.[Name], ' - Totals') ELSE rrf.[Option] END as [FeeOption]
        , SUM(CASE WHEN rrf.[Quantity] > 1 THEN rrf.[Cost] * rrf.[Quantity] ELSE rrf.[Cost] END) as [OptionTotal]
        , SUM(rrf.[Quantity]) as [FeeQuantity]
    FROM [Registration] re
        LEFT JOIN [RegistrationRegistrant] rr ON rr.[RegistrationId] = re.[Id]
        LEFT JOIN [RegistrationRegistrantFee] rrf ON rrf.[RegistrationRegistrantId] = rr.[Id]
        LEFT JOIN [RegistrationInstance] ri ON ri.[Id] = re.[RegistrationInstanceId]
        LEFT JOIN [RegistrationTemplateFee] rtf ON rtf.[Id] = rrf.[RegistrationTemplateFeeId]
    WHERE ri.[Id] = @RegistrationInstanceId
        AND rtf.[Name] is not null
    GROUP BY ROLLUP(rtf.[Name], rrf.[Option])
            

    
    
13. Ensure that the 'Customize Results with Lava' is checked and copy this Lava and HTML to the Dynamic Data Block Formatted Output field
    
    

    
    <style>
        .highlight {
            font-size: 1em;
            background-color: #dcf6ed !important;
            margin-right: -9px;
            padding: 0.5rem !important;
        }
    </style>
    
    <div class="panel panel-block">
        <div class="panel-heading"><h1 class="panel-title">Registration Instance Fees Totals</h1></div>
        <div class="panel-body padding-all-none">
        <table class="table table-bordered table-striped">
        <thead>
            <th>Fee Name</th>
            <th>Fee Option</th>
            <th>Fee Totals</th>
            <th>Fee Quantity</th>
        </thead>
        <tbody>
            {% for row in rows %}
            {% if row.FeeOption contains 'Totals' and row.FeeQuantity > 1 %} <!-- for per fee type totals lines if more than 1 quantity -->
            <tr>
                <td width="35%"><span class="text-semibold text-gray-600">{{ row.FeeName }}</span></td>
                <td width="35%"><span class="text-semibold text-gray-600">{{ row.FeeOption }}</span></td>
                <td width="15%" class="text-right"><span class="text-semibold" style="font-size: 1em;">{{ row.OptionTotal }}</span></td>
                <td width="15%" class="text-right"><span class="text-semibold" style="font-size: 1em;">{{ row.FeeQuantity }}</span></td>
            </tr>
            {% elseif row.FeeOption contains 'Totals' and row.FeeQuantity <= 1 %} <!-- hide fee type total lines with a quantity of 1 -->
            {% elseif row.FeeName contains 'Totals' %} <!-- for grand totals line -->
            <tr class="" style="background-color: @brand-success !important;">
                <td width="35%"><span class="text-bold text-gray-700">{{ row.FeeName }}</span></td>
                <td width="35%"><span class="text-bold text-gray-700">{{ row.FeeOption }}</span></td>
                <td width="15%" class="text-right"><span class="text-bold highlight">{{ row.OptionTotal }}</span></td>
                <td width="15%" class="text-right"><span class="text-bold highlight">{{ row.FeeQuantity }}</span></td>
            </tr>
            {% else %}
            <tr>
                <td width="35%">{{ row.FeeName }}</td>
                <td width="35%">{{ row.FeeOption }}</td>
                <td width="15%" class="text-right">{{ row.OptionTotal }}</td>
                <td width="15%" class="text-right">{{ row.FeeQuantity }}</td>
            </tr>
            {% endif %}
            {% endfor %}
        </tbody>
        </table>
        </div>
    </div>
    

14. Example of the new report on the Fees sub-page:

Screenshots

• /GetImage.ashx?guid=f5641c84-7475-422d-9974-9ea2f1f4597b