Using Reports to Compare Data

Similar to data and document exports, reports should be configured after having gone live with some or all aspects of Slate. A solid foundation in how data is stored in Slate is essential for constructing reports. Typically, institutions begin building reports that represent the enrollment funnel (from the time the student is a prospect to the time they enroll at the institution). As a result, institutions often begin using Report Builder after completing the Roadmap (i.e. decision data exists in the database).

This article will provide guidance on how to build the foundation of a report that allows for data to be compared by date (i.e. Year over Year, Weekly). 

Traditional Year-Over-Year Report Configuration

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 2016, 2017, and 2018. 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.

While following the same instructions that are provided in the Creating, Editing, and Rendering Reports article, the configuration of this report includes the following further steps:

Set Up Part
  • Each year is contained within its own Part.
  • Begin with the most recent term. In this example, Fall 2018 is the current term and the term to which all other terms will be compared.
  • The filters that are built in "Edit Report Part" as high-level settings are:
    • Student Type - "First-Time Full-Time, Fall"
    • Tag - NOT 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 - 2018
  • Column Width (optional)
  • Variable name - @apps
  • Filters
    • Term - Fall 2018
    • Application Status - Awaiting Materials, Awaiting Decision, Awaiting Confirmation, Decided

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

  • Name - Completed Apps
  • Type - Population
  • Column Group - 2018
  • Column Width (optional)
  • Variable name - @comp
  • Filters
    • Term - Fall 2018
    • Application Status - 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 - 2018
  • Column Width (optional)
  • Formula - @comp / nullif(@apps, 0)
  • Number Format - #.00%

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.

Total is created by selecting the following configurations in the Edit Row Group window:

  • Type - Data Table
  • Heading - Total
  • Row Type - Aggregate
  • Function - Count
  • Number Format - #
  • Variable Name - leave blank

  Tips

Should you desire to export this data to a query (e.g., to produce a list of records contained within the report's parameters), choose the corresponding exports within the Value section of this configuration box. Some common exports are: Ref/Slate ID, Name, Round.

Copy Out Report Parts

Once the first data table has been created:

  1. Click Edit.
  2. Choose Save as Copy. This will copy the entire report part, including the data table. To access the copied table, return to the "Edit Report Details" screen by clicking the Edit Report breadcrumb towards the top of the report.
  3. Double click on the copied data table to access the data columns and tables.

This Part will be the data for Fall 2017:

  • Replace the criteria found in all 2018 filters with 2017 filters.
  • Replace all Application Status filters with Application Status Change Date.

  Tips

The Application Status Change Date Filter is the most commonly used filter when building a comparison report. This filter reports on the first date that the application status was set on the record.

For example, for the Completed Apps column, we configure the Application Status Change Date Filter as follows:

  • Status: Awaiting Decision
  • Start Date: (blank)
  • End Date: today-1year

Utilize the Start Date (Optional) and End Date (optional) fields to insert point-in-time logic. For example, to extract the number of applications that were completed as of this date last year, insert "today-1year". Other options include:

  • Two weeks ago: today -14
  • One month ago: today - 1 month
  • Two years ago: today - 2 years

  Best Practices

The filter date does not have to be altered to account for leap years when using "1 month" or "2 years" because Slate is already taking that into account when calculating.

Data Comparison Within the 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 creation, submission, and completion are tracked by week over the previous month.  

mceclip1.png

This table contains multiple rows: one for each status to be tracked (Applications Started, Applications Submitted, Applications Completed), and multiple columns, each one denoting a specific time frame of each week within the past month.

To accomplish this format, follow these steps:

Set Up Part

The filters that are built in "Edit Report Part" as high-level settings are:

  • Entry Term - Fall 2018, Spring 2018
  • Tag - NOT IN Test Record.
Set Up Columns

One Data Column is added for each time frame.

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

  • Name - Applications
  • Type - Population
  • Column Group - leave blank
  • Column Width (optional)
  • Variable name - leave blank
  • Filters (these filters will account for each type of application status and the date parameters for "Last Week")
    • Application Created > today -7 OR,
    • Application Submitted > today -7 OR,
    • Application Status Change Date - IN Awaiting Decisions; Start Date - today -7

Two Weeks Ago is defined by selecting the following configurations in the Edit Column window:

  • Name - Applications
  • Type - Population
  • Column Group - leave blank
  • Column Width (optional)
  • Variable name - leave blank
  • Filters (these filters will account for each type of Application Status and the date parameters for "Two Weeks Ago"
    • Application Created > today -7 AND
    • Application Created > today -14 OR,
    • Application Submitted > today -7 AND
    • Application Submitted > today -14 OR,
    • Application Status Change Date - IN Awaiting Decisions; Start Date - today -14; End Date - today -8 

Repeat the above steps to set up the column for Three Weeks Ago.

Set Up Data Table

Three Data Tables will be added, one for each application status: Application Created, Application Submitted, Application Completed.

Application Started is created by selecting the following configurations in the Edit Row Group window:

  • Type - Data Table
  • Heading - Application Started
  • Row Type - Group By
  • Null Values - Check this box if you would like null/missing values to be shown in the results.
  • Series Total - Check this box if you would like to show the series total in the results.
  • Number Format - #
  • No Exports need to be added in the Group By section.
  • Add a Filter for Application Status - IN Awaiting Submission. This value will match the corresponding value in the Data Column to produce a result.

Repeat the above steps for Application Submitted and Application Completed rows.

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

Comments

0 comments

Please sign in to leave a comment.