Rock does a great job structuring data and functionalities in a manageable way. In some organizations the Accounts Receivable (Giving/Contributions) team has different members than the Accounts Payable (Accounting/General Ledger) team. In our organization, the Accounts Payable team has traditionally had little interaction with our ChMS, and we wanted to improve that with Rock. One of the challenges we ran into was how to display portions of the financial info in a way that is user and staff process driven, and not requiring the organization to change currently working processes to fit the technology. Fortunately, Rock's extensible in ways that makes this feasible and pretty painless.

Out of the box, Rock provides multiple tools you can use for understanding and reporting on Finances:

• Giving Analytics
• Data Views
• Reports
• Dynamic Data (the Dynamic Data Block)
• Dynamic Report (the Dynamic Report Block)
• Batches page
• Transactions page

We liked Giving Analytics along with the out of the box summary at the bottom of the Batch List and Transaction List pages. You can see below that they each provide slightly different ways to view the data.

Giving Analytics

(Date, Account, GL Code, Amount)

Batches List

(List of batches with transactions per-batch, per-batch account name splits, transaction totals, account totals, overall total)

Transactions List

(List of transactions with per-transaction account splits, batch id, transaction codes, currency type, account totals, overall total)

HOWEVER

The Challenge

If the out of the box functions aren't enough, how do you provide weekly financial reporting in a way that is consistent and eases the bank deposit reconciliation process?  

What we really wanted was:

• Per batch or per transaction detail was unnecessary
• Total numbers
• To always display the GL Code
• Breakouts/breakdowns for 'Tithe & Offering' accounts (we have camp payments and other 'non-giving' transactions that we need to report on)
• To understand what currency type was involved. When our Accounts Payable team goes to reconcile bank deposits with the reported numbers from Accounts Receivable, they have to take currency types into consideration. The currency type impacts when the money is actually available versus deposit date/time.

Tools Used

• Dynamic Data Block
• HTML
• Lava
• A 'buttons/picker' page
• A 'detail' page

This tutorial will show you how to create a report showing all transactions summarized and split based on currency types. As part of the process

Creating the Page Structure

To make this easy to use and manage, we're adding two new pages called Finance Weekly Reporting and Finance Weekly Reporting Detail to your 'Internal' Rock pages. You can use a different name/structure if you wish (this was just easy and consistent with an out of the box configuration.

Adding a Buttons/Picker Page

Navigate to CMS Configuration (or the Tools > Reporting page itself) and add a child page to Reporting. This will make it easy to find in the menu; I chose to call it Finance Weekly Reporting and put it below the Reports page in the menu order.

Adding a Detail Page

Next add another page as your Detail page. I chose to add this as a child page of the Finance Weekly Reporting page I just created. Take note of the Page Id (we'll use this later).

Configuring the Detail Page

We're going to add a Dynamic Data Block to the Detail page. The Buttons/Picker page will pass along query string parameters we'll use as part of the Dynamic Data

Adding the Dynamic Data Block

Navigate to the Finance Weekly Reporting Detail page you just created, and add a Dynamic Data block to the Main zone

Configuring the Dynamic Data Block Criteria

The Dynamic Data block needs some configuration before it will display the data we want. We're going to add some SQL (to get the data we want) along with some HTML and Lava (to format the output and set the page title)

Open the block's Criteria panel, which is where we'll do our configuration

Dynamic Data Block's SQL

The Dynamic Data Block will take the query string parameters from our URL and output a summed list of the transactions, grouped by the Name, GL Code, and Currency Type. Add the SQL to the Query section for the Dynamic Data Block

Select
    Sum(fd.Amount) as Total,
    fa.PublicName,
    fa.PublicName as Name,
    fa.GlCode as GlCode,
    dv.Description as CurrencyType
From
    FinancialTransactionDetail fd
Join
    FinancialTransaction ft
On
    fd.TransactionId = ft.Id
Join
    FinancialAccount fa
On
    fa.Id = fd.AccountId
JOIN
    FinancialPaymentDetail fpd
ON
    fpd.Id = ft.FinancialPaymentDetailId
JOIN
    DefinedValue dv
ON
    dv.Id = fpd.CurrencyTypeValueId
WHERE
    ft.TransactionDateTime BETWEEN '{{ BeginTransDate | ToMidnight | SanitizeSQL }}' AND '{{ EndTransDate | DateAdd:+1,'d' |  ToMidnight | DateAdd:-1,'s' | SanitizeSQL }}'
Group By fa.PublicName, fa.GlCode, dv.Description

NOTE: this SQL does a couple things to the date passed via query string parameter:

• Assumes you're wanting to use a 'Monday - Sunday' date range, as weeks are 'Monday - Sunday' in Rock
• Uses the SanitizeSQL filter when pulling lava into the SQL
• Takes the BeginTransDate value and sets it's time to midnight
• Takes the EndTransDate value, adds a day, sets the time to midnight, and subtracts 1 second. This has the effect of setting the time to 23:59:59 (or 11:59:59 PM)

Dynamic Data Block's Configuration Details

Make sure to set the following options for the Dynamic Data block:

• Parameters: "BeginTransDate;EndTransDate"
  note that each parameter is case sensitive, and it's semi-colon separated. My example has two parameters
• Under Show Grid Actions I only checked the Excel Export and Merge Template options, but if you use the Formatted Output section, that will 'override' the Grid functionality. Having Excel Export and Merge Template checked is helpful as you tweak and change your formatted output
• The other options can all be left at their defaults:
• Page Description is blank
• Query is a Stored Procedure is not checked
• Hide Columns is blank
• Person Report is not checked
• Show Grid Filter is checked
• Selection URL is blank
• Communication Merge Field is blank
• Communication Recipient Fields is blank
• Timeout is '30'
• Encrypted Fields is blank

Formatting the Output

By default, the output of the SQL is pretty good. However, if you want an overall total, or a total of only some of the output, you'll need to get fancy with the formatting.

We're going to do the following:

• Display a panel with totals (rows and columns)
• Display a second panel with totals only for accounts containing a specific GL Code (rows and columns)

You'll put both panels HTML & Lava in the Formatted Output section of the Dynamic Data block

The two panel's HTML & Lava (in full):

<!--- Begin Panel 1 --->
<div class="col-md-12 margin-t-md">
    <div class="panel panel-block">
        <div class="panel-heading">
            <h1 class="panel-title">Total Results</h1><br>
        </div>
        <div class="panel-body">
                <div class="row">
                  <div class="col-xs-4"><b>Account Name </b></div>
                  <div class="col-xs-2"><b>GL Code</b></div>
                  <div class="col-xs-3"><b>Currency Type</b></div>
                  <div class="col-xs-3 text-right"><b>Sub-Totals</b></div>
              </div>
            {% assign total = 0 %}
            {% for row in rows%}
                <div class="row">
                    <div class="col-xs-4">{{row.Name}}</div>
                    <div class="col-xs-2">{{row.GlCode}}</div>
                    <div class="col-xs-3">{{row.CurrencyType}}</div>
                    <div class="col-xs-3 text-right">{{row.Total | FormatAsCurrency }}</div>
                    {% assign total = total | Plus: row.Total %}
                </div>
            {% endfor %}
            <hr>
                <div class="row">
                    <div class="col-xs-4"><b>Total: </b></div>
                    <div class="col-xs-2"></div>
                    <div class="col-xs-3"></div>
                    <div class="col-xs-3 text-right"><b>
                        {{ total | FormatAsCurrency }}</b>
                    </div>
                </div>
        </div>
   </div>
</div>
<!--- End Panel 1 --->
<!--- Begin Panel 2 --->
<div class="col-md-12 margin-t-md">
  <div class="panel panel-block">
      <div class="panel-heading">
          <h1 class="panel-title">Tithe & Offering Totals</h1>&nbsp;<em>Accounts with a GL Code containing '1001'</em><br>
      </div>
      <div class="panel-body">
              <div class="row">
                  <div class="col-xs-4"><b>Account Name </b></div>
                  <div class="col-xs-2"><b>GL Code</b></div>
                  <div class="col-xs-3"><b>Currency Type</b></div>
                  <div class="col-xs-3 text-right"><b>Sub-Totals</b></div>
              </div>
          {% assign total = 0 %}
          {% for row in rows%}
          {% if {{row.GlCode}} contains '1001' %} 
              <div class="row">
                  <div class="col-xs-4">{{row.Name}}</div>
                  <div class="col-xs-2">{{row.GlCode}}</div>
                  <div class="col-xs-3">{{row.CurrencyType}}</div>
                  <div class="col-xs-3 text-right">{{row.Total | FormatAsCurrency }}</div>
                  {% assign total = total | Plus: row.Total %}
              </div>
          {% endif %}
          {% endfor %}
          <hr>
              <div class="row">
                  <div class="col-xs-4"><b>Tithe & Offerings Total: </b></div>
                  <div class="col-xs-2"></div>
                  <div class="col-xs-3"></div>
                  <div class="col-xs-3 text-right"><b>
                      {{ total | FormatAsCurrency }}</b>
                  </div>
              </div>
      </div>
  </div>
</div>
<!--- End Panel 2 --->

Deconstructing the HTML and Lava (the same process is used for each panel)

First we'll specify our bootstrap columns,

<div class="col-md-12 margin-t-md">
<div class="panel panel-block">

add a panel and panel heading,

   <div class="panel-heading">
            <h1 class="panel-title">Total Results</h1><br>
        </div>

and create 1 row with our headers

 <div class="panel-body">
                <div class="row">
                  <div class="col-xs-4"><b>Account Name </b></div>
                  <div class="col-xs-2"><b>GL Code</b></div>
                  <div class="col-xs-3"><b>Currency Type</b></div>
                  <div class="col-xs-3 text-right"><b>Sub-Totals</b></div>
              </div>

This assigns the value of zero to 'total' before starting

{% assign total = 0 %}

Next we'll specify the contents of each row using a for tag

the first panel's for content, along with a horizontal rule for a nice visual break

{% for row in rows%}
                <div class="row">
                    <div class="col-xs-4">{{row.Name}}</div>
                    <div class="col-xs-2">{{row.GlCode}}</div>
                    <div class="col-xs-3">{{row.CurrencyType}}</div>
                    <div class="col-xs-3 text-right">{{row.Total | FormatAsCurrency }}</div>
                    {% assign total = total | Plus: row.Total %}
                </div>
            {% endfor %}
<hr>

The second panel uses the same for tag, but with an if statement to specify that only GL Codes containing 1001 should be included

{% for row in rows%}
          {% if {{row.GlCode}} contains '1001' %} 
              <div class="row">
                  <div class="col-xs-4">{{row.Name}}</div>
                  <div class="col-xs-2">{{row.GlCode}}</div>
                  <div class="col-xs-3">{{row.CurrencyType}}</div>
                  <div class="col-xs-3 text-right">{{row.Total | FormatAsCurrency }}</div>
                  {% assign total = total | Plus: row.Total %}
              </div>
          {% endif %}
          {% endfor %}

The last row simply adds a summed total and a label

<div class="row">
                  <div class="col-xs-4"><b>Tithe & Offerings Total: </b></div>
                  <div class="col-xs-2"></div>
                  <div class="col-xs-3"></div>
                  <div class="col-xs-3 text-right"><b>
                      {{ total | FormatAsCurrency }}</b>
                  </div>
              </div>
      </div>
  </div>
</div>

Customizing the Page Title

Let's change the page title to include the date range. This helps the page viewer to know which week they are looking at (and is handy if the page is printed)
Put this in the Page Title Lava box.

Totals from {{PageParameter['BeginTransDate']}} through {{PageParameter['EndTransDate']}}

Notice that in the included lava we used the brackets and single quotations to pull the query string (page) parameter values into the page title lava. If your parameter was "LoremIpsum", you would pull in the page parameter values like this:

{{PageParameter['LoremIpsum']}}

Configuring the Buttons/Picker Page

Now that we have a detail page, we need to populate the 'Buttons/Picker' page. We'll create the buttons, and have the buttons populate the query string parameters the detail page will use to set the transaction date range

Adding an HTML Block for the Buttons

Navigate to the Finance Weekly Reporting page you previously created, and add an HTML Content block to the Main zone

Example HTML

For the HTML content, we'll need to use the Page Id you previously took notice of (page '541' in my example). First, make sure you have that. Then use the following process to create the appropriate buttons, with one button for each date/time range you want (each button will have a beginning date and and ending date). The SanitizeSQL lava filter is also used (best practice for these types of use cases). Each button will consist of 1. Two variables, the link-enabled button HTML with Lava for the query string parameters and values and 2. The button name with Lava. Here's an example, along with a step by step breakdown of how it's put together:

{% assign LastSunday = 'Now' | DateAdd:-7 | SundayDate %}
{% assign LastMonday = 'Now' | SundayDate | DateAdd:-13 %}
<a class="btn btn-default btn-md" href="/page/541?BeginTransDate={{- LastMonday | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-LastSunday | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">Last Week<br>({{- LastMonday | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- LastSunday | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>

Don't forget to enable versioning for the block if you'll be tweaking this as you go!

The individual pieces and how they are used for the button

1. Assign two variables for each button. This example shows "Last Sunday" and "Last Monday" as evaluated on page load
   

   {% assign LastSunday = 'Now' | DateAdd:-7 | SundayDate %}
   {% assign LastMonday = 'Now' | SundayDate | DateAdd:-13 %}

   What's happening here is this lava is looking up the current date/time, using a filter to subtract 7 days, and then using another filter to determine the 'Sunday Date' for that date.
2. Begin your button creation using the appropriate class and the Page Id

   <a class="btn btn-default btn-md" href="/page/541

3. Append two query string parameters to the end (just after the Page Id, the first one with a '?' in front, and a '&' joining the two parameters). The parameter (the part before the =) needs to be identical to the parameter used in the Dynamic Data Block and the SQL. I chose to also open in a new window (the " target="_blank"). This example adds a BeginTransDate parameter of LastMonday along with a EndTransDate parameter of LastSunday (both variables we assigned earlier formatted for our use)

   ?BeginTransDate={{- LastMonday | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-LastSunday | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">

4. Finally, set the button names and close out 'a' tag for the button. I chose to explicitly include the date and time range as part of the button (this is optional; just remove the lava if you don't want it)

   Last Week<br>({{- LastMonday | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- LastSunday | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>

I also added some alert info above my buttons HTML with a general tip and a warning (when we were testing), like so (optional; customize to your needs):

<div class="alert alert-info"><h3>v2 Financial Transactions Summaries (include currency type breakouts) </h3> </div>
<div class="alert alert-danger"><h3>These reports need verification by Finance AP & Finance AR teams </h3>&nbsp;(SMR 12-28-2018) </div>

What my HTML and Lava looks like in full (I chose to present buttons for 4 weeks):

<div class="alert alert-info"><h3>v2 Financial Transactions Summaries (include currency type breakouts)</h3></div>
<div class="alert alert-danger"><h3>These reports need verification by Finance AP & Finance AR teams</h3>&nbsp;(SMR 12-28-2018)</div>
<!-- Assign variables for button usage -->
{% assign ThisSunday = 'Now' | SundayDate %}
{% assign ThisMonday = 'Now' | SundayDate | DateAdd:-6 %}
{% assign LastSunday = 'Now' | DateAdd:-7 | SundayDate %}
{% assign LastMonday = 'Now' | SundayDate | DateAdd:-13 %}
{% assign TwoSundays = 'Now' | DateAdd:-14 | SundayDate %}
{% assign TwoMondays = 'Now' | SundayDate | DateAdd:-20 %}
{% assign ThreeSundays = 'Now' | DateAdd:-21 | SundayDate %}
{% assign ThreeMondays = 'Now' | SundayDate | DateAdd:-27 %}
<!-- Buttons that append query strings. Includes date and time output formatting -->
<a class="btn btn-default btn-md" href="/page/541?BeginTransDate={{- ThreeMondays | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-ThreeSundays | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">3 Weeks ago<br>({{- ThreeMondays | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- ThreeSundays | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>
<a class="btn btn-default btn-md" href="/page/541?BeginTransDate={{- TwoMondays | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-TwoSundays | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">2 Weeks ago<br>({{- TwoMondays | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- TwoSundays | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>
<a class="btn btn-default btn-md" href="/page/541?BeginTransDate={{- LastMonday | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-LastSunday | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">Last Week<br>({{- LastMonday | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- LastSunday | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>
<a class="btn btn-default btn-md" href="/page/541?BeginTransDate={{- ThisMonday | Date:'MM-dd-yyyy' | SanitizeSQL -}}&EndTransDate={{-ThisSunday | Date:'MM-dd-yyyy' | SanitizeSQL -}}" target="_blank">This Week<br>({{- ThisMonday | Date:'MM-dd-yyyy h:mm:ss tt' -}}&nbsp;through&nbsp;{{- ThisSunday | DateAdd:+1,'d' | ToMidnight | DateAdd:-1,'s' | Date:'MM-dd-yyyy h:mm:ss tt' -}})</a>
<br><!-- Reference so that page viewer understands context and definition of timeframes. Includes date and time output formatting on Mondays -->
<strong>Reference:</strong>
<em>Weeks run 'Monday - Sunday'</em>
<ul>
<li>Three Sundays: {{- ThreeSundays -}}</li>
<li>Three Mondays: {{- ThreeMondays | Date:'MM/dd/yyyy' -}}</li>
<li>Two Sundays: {{- TwoSundays -}} </li>
<li>Two Mondays: {{- TwoMondays | Date:'MM/dd/yyyy' -}}</li>
<li>Last Sunday: {{- LastSunday -}}</li> <li>
Last Monday: {{- LastMonday | Date:'MM/dd/yyyy' -}}</li>
<li>This Sunday: {{- ThisSunday -}}</li>
<li>This Monday: {{- ThisMonday | Date:'MM/dd/yyyy' -}}</li>
</ul>

The result

Other Tips

• If you just want the 'past 4 weeks' buttons like I did, simply copy and paste the HTML and Lava posted above, customizing the Page Id to match yours. Customize away!
• Pay attention to Page and Block security. Using Impersonation is a great way to verify you have it all configured correctly for your finance staff
• We chose to make the pages (and blocks inheriting) the permission of "View" as the only permission for Finance Accounts Payable and Finance Accounts Receivable staff. We have an security role enabled Org Unit for each (under Intranet > Org Chart), so this made it easy to present those pages only to those that needed it.
• If you at first 'hide' these pages from everyone that's not a Rock admin, make sure to check both page and block permissions when setting your final permissions.
• DO NOT give a user or role permission to Edit the Dynamic Data block unless you completely trust them (i.e. they are a Rock Admin). If they can run SQL, they can pretty much do whatever they want...
• I'm not sure why, but I get inconsistent behavior depending on the order of usage of the SundayDate Lava Date Filter and the DateAdd Lava Date Filter. If you're seeing inconsistent output formatting, that's probably why
• If the date ranges in the example aren't to your liking, simply tweak the variable assignments
• You can manually edit the query string parameters in the URL of the Detail page. This is a quick and easy way to select a different date range for one-off needs without creating new buttons (just make sure to get the formatting correct.... MM-DD-YYYY)
• I would not have been able to get this done without the help available in RockRMS' Rocket.Chat. In particular, Kevin Rutledge, Jim Michael, Chris Rea and Daniel Hazelbaker were instrumental in figuring this out for my org.