How to Create Your Organization’s Roll-Up Report: A Step-by-Step Guide
- Creating and Updating the Weekly Roll-up Report
- Using Data Exports for Weekly Roll-up Report
- Summarizing Organization’s Snapshot Table
- Preparing Your Organization’s Dashboard Report
- Creating Your Organization’s Service Episodes by Source Table
Creating and Updating the Roll-up Report
WAServes generates this report in Microsoft Word and Excel. The WAServes team produces and maintains a weekly and monthly roll-up report to monitor the network’s performance. This post will use a weekly approach to share the steps in the report creating process.
The WAServes Roll-up report is a manually generated report. Excel is used to calculate the Weekly percentage changes for each table and then updated in Word. This report is updated on Fridays, reporting on the previous seven days, see image below as an example of the weekly roll-up spreadsheet. Data from the data exports are used for this report.
Using Data Exports for Weekly Roll-up Report
The following data must be exported from the UniteUs platform: service case, referral, and contact data exports. See below for the location of the UniteUs Dashboard. For the step-by-step guide on how to use the data exports, see post “How to Analyze Your WAServes Provider Results With Data Exports.”
Summarizing Organization’s Snapshot Table
The Total Clients data contains unique clients in the Contact data export, which you saved as an Excel spreadsheet. After opening this file, delete test data. Second, insert a column, and then title this new column “Duplicate.” This filter allows you to filter our duplicate clients from the Total Clients. The goal is to report the unique number clients using the WAServes network.
Perform an ascending sort on the last name, allowing you to view duplicate client names. Type an “x” or other symbols in the Duplicate column next to duplicate names. Using the “Remove Duplicates” function in Excel will not suppress all duplicates, so you must perform this manually with the Duplicates column.
Filter the data by displaying unique clients only. Use the record count for all unique clients for the Total Clients data in WAServes Snapshot table. This data is cumulative; therefore, report of unique clients in this spreadsheet.
The Total Referrals row in the WAServes Snapshot table is the number of referrals in the Referral data spreadsheet. This data is cumulative; therefore, report all referrals listed in this spreadsheet. This information allows you to report the number of clients referred to the WAServes network.
Top 3 Service Categories
Use the Service Case spreadsheet to identify the Top 3 Service Categories. Apply data filter on “Created at” column, displaying only the week needed for the report. After using this data filter, you will be viewing service cases for the week. Apply the data filter for Case Source column. Use this data filter to identify counts for each Service Types, see image below.
After identifying the counts for each Service Type for the week, use the top three service types for the WAServes Snapshot. There is no need to filter out duplicate clients since you are identifying service types of all service cases for the week.
Add these data elements to your Weekly Roll-up spreadsheet, allowing you to calculate Weekly Change percentage. Update the Word version of the Weekly Roll-up Report with this table data, and then perform quality check verifying counts or inaccuracies due to filtering errors. This allows you to report the top three service categories for the week.
Preparing Your Organization’s Dashboard Report
Unique Service Requests for your Organization
The Unique Service Requests is a cumulative data metric for the week from service case spreadsheet. Filter the Organization column by selecting your organization’s name. This allows you to view and report on unique service requests for your organization. No need to filter duplicate clients, since all service cases are being tracked in this table. In the example below, the Organization column is filtering the WAServes (GPS) Coordination Center.
Closed Cases is also a cumulative data point. Filter data using the Case Status column heading. Select “Closed,” reporting the count for all service cases closed. After filtering Case Status, the closed cases for your organization will be displayed.
Unique Service Request this Week
Use the Service Case spreadsheet for the Unique Service Requests this week metric. Filter the data using the Created at the column to display the data for the week. Use the service cases count after applying this filter.
Unique Clients this Week
Use the contacts spreadsheet for Unique Clients this week. Use data filter on Duplicates column you inserted into this spreadsheet, allowing you the filter out duplicate names for the week. Use Date Created filter, enabling you to display data for the week. After applying these two filters, use the count for remaining contacts listed.
Closed Cases this Week
Use the service cases spreadsheet for Closed Cases this week. Filter data, displaying only service cases for the week. Filter data using the Case Status column heading. Select “Closed,” reporting the count for all service cases closed for the week.
Use the service cases spreadsheet for the Open Cases metric. View all service cases. Filter data on Case Status, selecting “Open.” The data listed is for all open cases for the week.
Add these data elements to your Weekly Roll-up spreadsheet, allowing you to calculate Weekly Change percentage. Update the Word version of the Weekly Roll-up Report with this table data, and then perform quality check verifying counts or inaccuracies due to filtering errors.
Creating Your Organization’s Service Episodes by Source Table
To identify the service cases Created by Network this week metric, use the service case spreadsheet. Apply the Created At filter, to list data for the week. Then use filter utilizing the Case Source, select Network Organization. Use the service case count listed for the Created by Network this week metric.
Repeat this second filter to identify service case counts for Created by Coordination Center this week and Created by Self-Referral this week.
Bringing It All Together: Adding Data to the Weekly Roll-Up Report
The WAServes generates a Weekly Roll-up report provides cumulative and week-by-week data, allowing WAServes to monitor the health of its network. You too can create a Microsoft Word document for your Organization’s Weekly Roll-up Report.
Create tables from your Microsoft Excel worksheets. The first table will be the WAServes Snapshot table lists four key metrics: Total Clients (cumulative), Total Referrals (cumulative), and Top 3 Service Categories, see example below. Copy and paste or add the data from your data exports.
Create a second table called the Dashboard Report table summarizes the following cumulative and week-by-week data: Unique Service Requests (cumulative), Close Cases (cumulative), Unique Service Requests this week, Unique Clients this week, Close Cases this week, and Open Cases. See image below for details. Copy and paste or add the data from your data exports.
The last table is the Service Episodes by Source (Case Source), detailing the following data: Created by Network this week, Created by Coordination Center this week, and Created by Self-Referral (Assistance Request) this week, see image below. WAServes combines Assistance Requests and Coordination Center data since both metrics are the clients requesting services from the network. Copy and paste or add the data from your data exports.
Congrats! You have just created your first WAServes Roll-up Report for your organization.
You have access to all the data used by the WAServes Op Team. Now you can monitor other providers in your service category or view other emerging clients needs that your organization may be able to serve.