Using Reports to Compare Data

This article will provide guidance on how to build the foundation of a report that allows for data to be compared by date, either year-over-year or weekly within the same year. 

Traditional Year-Over-Year

In this example, we will be comparing application status data from year to year.

mceclip0.png

The report above displays aggregate data for the years 2024, 2023, and 2022. Each of the tables includes funnel data per year, beginning with a count of applications, and then providing a count for number of admits, then deposits, and finally the yield rate.

Set Up Part

Each year is contained within its own separate "Data/Charts" part. We'll begin with the most recent term (in this example, it's Fall 2024).

  1. Click "Data/Charts" to create the part.
  2. Enter the year in the Name field (for example, "2024").
  3. Choose a base of Configurable Joins - Records - Application.
  4. After creating the part, add filters to select the relevant population. For example, to find submitted Fall 2024 first-year applications that aren't test records, add the following in the Filters section:
    • Add a direct filter for Term IN "Fall 2024".
    • Add a direct filter for Submitted Status = Submitted.
    • Add a direct filter for Student Type IN "First-Time Full-Time, Fall".
    • Add a join to Person.
    • Create a subquery filter. Inside the subquery, choose the "Not Exists" aggregate, join to Tags, and add a direct filter for Tags / Name IN Test Record.
Set Up Columns

One Data Column is created for each Population and relevant Formula.

The Applications column is defined by selecting the following configurations in the Edit Column window:

  • Name - Applications
  • Type - Population
  • Column Group - 2024
  • Column Width (optional)
  • Variable name - @apps
  • Filters
    • No filters are needed here, since this column is simply counting all records in the overall report part. (That's why it's important that your part filters are set up correctly.)

Completed Apps is defined by selecting the following configurations in the Edit Column window:

  • Name - Completed Apps
  • Type - Population
  • Column Group - 2024
  • Column Width (optional)
  • Variable name - @comp
  • Filters
    • Status IN Awaiting Decision, Awaiting Confirmation, Decided

Important!

Formulas can be used within rows and within columns. Formulas cannot calculate variables between rows and columns.

Completion Rate is defined by selecting the following configurations in the Edit Column window:

  • Name - Completed Rate
  • Type - Population
  • Column Group - 2024
  • Column Width (optional)
  • Formula - @comp / nullif(@apps, 0)
  • Number Format - #.00%

Admits is defined by selecting the following configurations in the Edit Column window:

  • Name - Admits
  • Type - Population
  • Column Group - 2024
  • Column Width (optional)
  • Variable name - @admits
  • Filters
    • Create a subquery filter. Inside the subquery, choose the "Exists" aggregate, join to Decisions, and add a direct filter for Decisions / Code IN Admit.

Continue to build out the remaining data columns, altering the filters to fit the respective populations.

Set Up Data Table

Only one data table is needed to extract the totals of the data contained within the data columns:

  • Click "Data Table".
  • For the Heading, type "Total".
  • Leave the other settings as-is.
Copy Out Report Parts

Once the first data table has been created:

  1. Click Edit.
  2. Choose Copy.
  3. For the Report Part Name, enter the next year for comparison (e.g. "2023")

This will copy the entire report part, including the data table, and move you into the copied part. Start by clicking Edit and updating the high-level part filters:

  1. Update the Term direct filter to match the new year for comparison.
  2. Delete the Submitted Status filter. 
  3. Add the Submitted Date filter, and set it to "< today - 1 year".
  4. Click Save.

Next, update each column:

  • Columns based on decisions (like Admits and Deposits) are easy to update. Inside the subquery filter you already created, add a new Released Date filter and set it to "< today - 1 year".
  • Columns based on application status (like Completed Apps) are a bit more complicated. There's no direct filter for status change date, so a subquery is necessary instead:
    1. Delete the existing Status filter. 
    2. Add a subquery filter and give it a descriptive name (like "Application Status Changed 1 Year Ago"). Leave the aggregate as "Exists".
    3. Inside the subquery, join to Statuses.
    4. Add a direct filter for Statuses / Status IN "Application Status - Awaiting Decision".
    5. Add a direct filter for Statuses / Change Date < today - 1 year.

  Tip

The Statuses table has one row for each time a status changes on a Person or Application record. Using a subquery, we can check to make sure the status changed on or before the relevant point in time.

Once you've set up this report part, you can copy it again for the next year. Although the point-in-time logic will already be in place, make sure to check the part and every column:

  • Each part needs the high-level Term filter to be updated.
  • Each column needs all date logic to be updated (for example, from "today - 1 year" to "today - 2 years"). Make sure to check inside subqueries!

Data Comparison (Same Year)

Some institutions like to be able to track their applications according to application status within different time frames throughout the year. In this example, application submission and completion are tracked by week over the previous month.  

mceclip1.png

Each status is contained in its own "Data/Charts" report part, with each part only containing one row. The columns denote a specific time frame of each week within the past month.

To accomplish this format, follow these steps:

Set Up Part

Each status is contained within its own Part. 

  1. Click "Data/Charts".
  2. Enter a descriptive Name (for example, "Applications Submitted")
  3. Choose a base of Configurable Joins - Records - Application.
  4. After creating the part (or clicking "Edit" at the high-level part), add filters to narrow down the population. This helps your report run efficiently. For example, add filters to limit this part to just Fall 2024 first-year applications. 
Set Up Columns

One Data Column is added for each time frame. Pick a descriptive name (like "Last Week" or "Two Weeks Ago") and leave the other settings in their default values.

The filters in each column will vary, depending on which status this part represents.

Applications Submitted

  • Last Week column filters: 
    • Submitted Date > today - 1 week
  • Two Weeks Ago column filters:
    • Submitted Date <= today - 1 week
    • Submitted Date > today - 2 weeks
  • Three Weeks Ago column filters:
    • Submitted Date <= today - 2 weeks
    • Submitted Date > today - 3 weeks

Applications Completed

Checking for application completion means checking the application status. We'll need to use the Statuses table, which contains one row for each time any Person or Application status changes. Since this is a one-to-many relationship, we'll need to use a subquery filter:

  • Leave the aggregate as "Exists".
  • Inside the subquery, join to Statuses.
  • Add a direct filter for Statuses / Status IN "Application Status - Awaiting Decision".
  • Add a direct filter for Statuses / Change Date. Depending on which column you're in, you may need more than one of these filters inside your subquery.
    • In the Last Week column, your subquery filter just needs one Change Date filter, set to "> today - 1 week".
    • In the Two Weeks Ago column, your subquery filter needs two Change Date filters: one set to "<= today - 1 week" and one set to "> today - 2 weeks".
    • In the Three Weeks Ago column, your subquery filter needs two Change Date filters: one set to "<= today - 2 weeks" and one set to "> today - 3 weeks".
Set Up Data Table

Only one data table is needed to extract the totals of the data contained within the data columns:

  • Click "Data Table".
  • For the Heading, type "Applications Submitted".
  • Leave the other settings as-is.
Copy Out Report Parts

Once the first data table has been created:

  1. Click Edit.
  2. Choose Copy.
  3. For the Report Part Name, enter the next status that you'd like to include (e.g. "Applications Completed")

This will copy the entire report part, including the data table, and move you into the copied part. You don't need to edit the high-level part filters, so instead you can update each column based on the logic described in the "Set Up Columns" section.

Was this article helpful?
9 out of 12 found this helpful