What Makes Up a Report?

There are two facets to any report: filter and display criteria. Consider this example. Your organization's leader walks in your door and asks for a list of attendees over the age of 18 who began attending within the last two years. He would like to have their names, contact information and the number of times they have attended. Using this example, let's look at each of the facets of reporting.

1. Filter Criteria: These are the criteria that limit the results to display. They answer the "Who" part of the request. In the case of the example, the criteria would be:
   1. Attendees
   2. Greater than 18 years of age
   3. Began attending in the last two years
2. Display Criteria: Once the results are filtered you must display them, with the necessary attributes, to the user. In the case of our example, these attributes might be:
   1. Name
   2. Phone
   3. Email
   4. Address
   5. Number of Times Attended

Once you create your handy report for your leader, it's very likely she'll be back asking for further changes. Perhaps now she wants another report with the same logic but only showing females. In most systems you'll have to start over and make a copy with the addition of the new criteria. Now you have two very similar reports to maintain.

With Rock, we have deliberately chosen to split the filter and display activities in reporting. You create your filters by defining Data Views. You then create your display by creating Reports that use the Data Views.

Reporting Strategy

The strategy part of reporting comes from the definition of your Data Views. It will be tempting to quick-write a very specific data view for each report. Consider, though, that data views can extend and build off of each other. For instance, you might create a data view that filters Adult Members & Attendees and then create a new data view that uses this view and adds the criteria of female gender. You’ve now created two views that can be used for the report at-hand AND in future reports. What’s more, if you ever need to redefine what makes an Adult Member & Attendee, you can change it in one place and all reports that are built off of that view will get the new definition. These two data views are available out-of-the-box with Rock. Consider the results of these views on the Rock sample data in the figure below.

Before starting any report, you should ask yourself:

• What, if any, criteria filter in this report might be reusable in future reports?
• What data views have I already defined that I can start with for this report?

Don't worry - we've only scratched the surface of Rock's tools. Now let's get our hands dirty learning about creating data views and reports as well as learning about Rock's other tools.

Any vs All

At the top of the filter group you’ll notice a setting that says Show if Any/All of these are true. You might be wondering what’s the difference between Any and All. Let’s define each:

• All: This means that each of the filter criteria must be true in order for a record to be displayed. One helpful trick is to read through each filter and insert the word and in between each. So for a record to display in the Adult Members & Attendee view, a record must: have a record status of active AND a connection status of member or attendee AND must be an adult in a family.
• Any: This setting means that if Any of the filter criteria is true then display the record. It's like inserting the word OR between each criterion.

Filter Groups

For complex views you may need to include both AND and OR type logic into your view. Filter groups give you this option. Say for instance you need to create a view that shows individuals who are married AND a member OR who are single AND an attendee. That data view would be configured like this:

Note how we have two filter groups where if Any of them is true the record is selected. They in turn have criteria that must All match.

Post Filter Transformations

The Post-Filter Transformation gives you the option to "flip" the results of a Data View with ease. For example, if your Data View includes the kids attending summer camp, selecting the Parent transformation will find the parents of those kids. If your Data View includes Adult Members & Attendees, choosing the Children transformation will select the children of those selected.

Securing Data Views

There will be Data Views that you want to limit access to. Note that you can control who has access to Data Views. To change the default security on a Data View click the button on the data view details screen.

You can also check the access for Data View categories. This allows you to modify the security for all Data Views in that category.

Taking Security Further

Controlling who has access to Data Views is important. But you also need to limit who can make new Data Views with certain criteria. You can limit what filters are available to specific users and groups under Admin Tools > System Settings > Data Filters. Here you'll see a list of available filters with the ability to change who has access to use each filter.

When filtering on the attributes of an entity (like person attributes), normal attribute security will be used in controlling access.

You can also control security on the Data View transforms. This can be configured under Admin Tools > System Settings > Data Transforms.

Creating A Report

Let's jump right in and see how we create a new report. For our example, we'd like to create a report we can use to learn the names and faces of our members and attendees. The figure below shows what this report would look like. The callouts for the figure explain the various steps used in the creation of our report.

1 Name / Description

First we give our report a name and description. We highly recommend that you spend some time writing a clear description that tells what the report does and how it should be used.

2 Applies To

Next, select what type of entity you are writing a report for. In most cases this will be Person but, like Data Views, you can report on anything.

3 Data View

Now that you have selected the entity, you'll see a list of Data Views you can use as the source for your report.

4 Resulting Row Limit

If needed, you can limit the number of records that are displayed on your report.

5 Sorting

You can sort your report by any field you add in either assending or decending order. You can even choose to sort on more than one field at a time.

6 Fields

Finally you'll add the fields you want to display on your report. You can determine for each field whether or not you want it to display in the grid. You might be thinking, "If it's not on the grid where else would I see it?" If you don't check this button, the field will still be available when you export the report to Excel from the bottom of the grid. This is helpful for supporting information that is not needed very often.

The Power Of Lava

You'll notice that one of your field options is Lava. Lava is a templating engine that allows you to customize the way data is presented. With this field type you can mix and match data in lots of different ways.

Here are a few examples: Last Name, Nick Name

{{ LastName }}, {{ NickName }}

<a href="/Person/{{ Id }}">{{ LastName }}, {{ NickName }} </a>

{% if BaptismDate != null %}
    <i class="fa fa-check"></i>
{% endif %}

You can find out more about Lava on the Rock RMS learning website.

If you want more information on using Lava in merge documents, you'll find that in the Admin Hero Guide.

Create Data View

The first step is to create the data view under Tools > Data Views that will drive the report. Here we have two filters: campus and our background check logic. Notice that we leave the campus blank. This basically says "show any campus," which will be our "default."

Create Report

Next we create the report under Tools > Reports. Here we add in the columns we’d like to display on the report. Nothing new here.

Setup Dynamic Report

Finally, we’re ready to add our Dynamic Report block. After creating a new page and adding the block, we can set the block's settings. Below is a screenshot of what's possible.

1 Results Title

The title text to use for the results grid.

2 Results Icon CSS Class

The CSS icon to use for the same grid.

3 Filter Icon CSS Class

The CSS icon to use for the filter panel.

4 Filter Title

The title text for the filter panel.

5 Report

The report we created in step 2.

6 Filter List

After selecting a report, Rock will look at the data view for that report and list its filters. The options include:

• Show As A Filter: This tells the block whether the filter should be shown to the user for them to modify. In our sample we let them change the campus but not the background check date.
• Configurable: Determines if the user should be allowed to modify the criteria of the filter. At first you might think this odd, as when would you ever not want to allow the criteria to change, but it'll make sense when combined with the next option.
• Toggle Filter: This setting will show the filter with a checkbox next to it. Unchecking the box will disable the filter entirely.

Extra Power from Stored Procedures

OK, we're going to geek out here for a second... By using SQL Server Stored Procedures, it can get even more powerful. When calling a stored procedure you can pass the procedure any of the query string parameters from the URL. For instance, if the page the block is placed on is accessed from the URL:

http:///page/123?GroupID=12

You can pass the value of the GroupId to your stored procedure as a parameter. The stored procedure can then use this value to help return relevant data (say group members for the group).

You can also pass in the current user's person id field. This allows you to further personalize the data to the person requesting the page. Just think of all the fun you can have with this block!

In order to make this all work, you'll need to define each of the parameters you want passed to your stored procedure in the Parameters field discussed above. Rock will look for each of these parameters in the URL’s query string and, if found, pass in the value to the procedure. If you would like the current person’s id to pass in you'll likewise need to add in the parameter CurrentPersonId.

Anatomy of a Metric

Metrics are defined very similar to data views and reports with categories. This allows you to organize them in a way that makes sense for your organization. One thing that is unique about metrics is that they can be linked to more than one category. This allows you to re-use them in several areas of your metric hierarchy.

So how do we create a new metric? Administrating metrics is done under Tools > Metrics. For our example we'll create a new metric that displays the number of adult members and attendees we’ve had each week. Here are the details of the completed metrics with callouts for each field.

1Title / Subtitle / Description:

Be sure to provide detailed titles and descriptions for your metrics that not only make sense to you, but also to those who will be using the metrics in the future.

2Icon CSS Class:

The CSS class of the icon you wish to associate to the metric. This is used in the display of the metric. Rock uses the Font Awesome icon set by default, but any CSS based icon set can be used if properly configured.

3Subtitle:

The subtitle will be shown on graphs and charts.

4Description:

You'll be tempted to just skip right over this field, but you'll regret that in the future. No matter how obvious you feel this metric is future you is screaming for you to add details about what is being measure, how it's calculated, any filter specifics (like only members), etc. Be kind to future you and document as much as you can. When you're done, go ahead and thank youself ahead of time.

5Metric Champion:

One of the keys to good metrics is good documentation. In that regard we’ve worked hard to be sure that metric become self-documenting. The metric champion allows you to note the person in your organization who is responsible for confirming that this metric is meeting its goals.

6Metric Administrator:

The metric administrator is the individual in your organization who is responsible for ensuring that the metric data is entered correctly and is valid.

7Categories:

This field allows you to link your metric to one or more categories.

8Units Label:

The y-axis label describes the units of what is being measured (people, groups, money, etc.)

9Cumulative:

Some metrics make sense to compare year-to-date, others - not so much. For instance, it's often helpful to look at year-to-date values for adult members and attendees. But a metric that tracks attendance for a service or event often does not make sense to evaluate the same way. This field allows Rock to know if cumulative comparisons make sense for this specific value.

10Source Type:

The source type defines how the metrics will be entered into the system. The options are:

• Manual: Metric values will be entered in manually.
• SQL: A SQL statement will be run to populate the values. Once you select this option a SQL entry input will be displayed. The help menu for the SQL field provides in-depth information on how your SQL should be formatted.
• Data View: A data view will be executed and the count of its values will be added to the metric with the date it was run. The help field here also displays information on configuring your dataview.

11Schedule:

The metric schedule helps determine how often this metric is calculated. When used with the SQL and data view source types, this field will actually tell Rock when to automate the harvesting of metric values.

12Series Partitions:

For simple metrics you're done, you can skip this section. Often times though you want to break your metrics down by campus, or maybe campus then service time. Setting up series partitions allows you to do just that. You always get the date partition (that's free) but you can setup as many partitions as you feel necessary (but more that 2 can get a bit complex).

Entering Metric Values

There are two types of metric values you can enter. Each one is discussed in detail below.

1Type

The first entry denotes if this is a measure or a goal. More on the difference between these two below.

2Value Date

The date that the value is for.

3Series Partitions

If you configured any series partitions you'll need to select their values here.

4Value

The metric measure or goal value.

5Note

A note for the value. This is a great place to document special outliers (like Christmas or Easter) or additional data (like the week's sermon title).

Metric Charts

The easiest way to view metrics values is to look at the charts that appear above the metric definition. This will show you all of the values entered for the metric. If you would like to have more options for displaying metrics, take a look at the metric dashboard capabilities below.

Service Metric Entry

You'll often have the need to enter metrics that follow the partition pattern of "Campus > Sunday Date > Service Time" pattern. We've created a simple entry page for you to enter these types of metrics into. You'll find this page under Intranet > Weekly Metrics. There are several block settings to allow you to configure the block and select the metrics you wish to enter.

Anatomy of a Dashboard

1Theme:

While dashboard blocks can be placed on any page in Rock we do ship with a special theme designed for dashboards called Dashboard Stark. This a very basic starter theme for you to use as a starting point for creating your own. When you do create new dashboard themes we highly recommend you prefix their name with Dashboard to help keep them grouped together in lists.

2Dashboard Widgets:

The widgets are simply Rock blocks on the page. We discuss each type of block below in detail.

3Context Setter:

While optional, a context setting on the page allows you to filter the metric values by their Series Partition. Many times this is a campus selector or group selector.

Dashboard Blocks

Rock ships with several dashboard widget blocks. Each of these blocks is shown below.

Line Chart

This block displays a line chart for the selected metric. Options include:

Title	Description
Name	This is the block name. It is not displayed anywhere on the output of the block.
Title	The title that is displayed for the metric.
Subtitle	The subtitle for the metric.
Column Width	The number of Bootstrap columns the chart should use for its width. (A Bootstrap row, by default, has 12 columns.)
Chart Style	The chart style to use. These styles are defined under Admin Tools > General Settings > Defined Types > Chart Styles.
Metric Value Types	Determines what metric value types should be displayed on the chart, goals and/or measures.
Metric	The metric to use for the chart.
Partition Filter	Determines how the chart will get the series partition value. It can be either hard coded or determined from the page context.
Date Range	Determines which values are displayed on the graph.
Detail Page	This optional setting will determine which page will be loaded when the graph is clicked.

Pie Chart

This block displays a pie chart for a given metric. Options include:

Lava

This block renders the metric values using a lava template. Options include: