Using Reports to Compare Data
- 08 Nov 2023
- 5 minute read
- Print
- DarkLight
- PDF
Using Reports to Compare Data
- Updated 08 Nov 2023
- 5 minute read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
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
In this example, we will be comparing application status data from year to year.
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:
Click Edit.
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.
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 (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.
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?
