Reporting FAQ

Forms Reporting FAQ

What’s valuable about Reporting?

Reporting allows users to have access to their data so that they can have full visibility of the clients they serve and the work they do in Casebook. We provide a robust number of curated datasets so that customers can mix and match data to build their own reports and streamline operations or respond to donors. Casebook also provides an ever-growing library of prebuilt reports that allow a strong foundation for getting started for organizations that don’t have the time or resources to build their own custom reports. Our BI tool can handle a variety of visualizations so that you can view your data in a way that makes sense to you and dig into the data that matters most.

What are pre-built reports? Can I customize them?

Pre-built reports are a series of reports Casebook has created to provide a report library customers can access immediately with their information. Our current reports include: 

  • Workflows

    • Track time spent on workflows and workflow steps, rate of timely completion, and identify how many are currently active/pending.

  • Providers Summary

    • Track your providers, their license statuses, and service capacity.

  • People Reports

    • Track client demographics, and pull information on all clients in the system.

  • Notes Reports

    • Analyze notes for all clients by note type, contact method, and date, among other data.

  • Intakes Summary

    • Track your intakes by time, type, status, and more. Identify referral sources, check how many cases have been created per intake, and more.

  • Cases Summary

    • Track your cases and clients, and understand case workloads by worker and programs.

  • Application Portal Summary

    • Track applications you receive from providers, including current status, demographics, and average time to complete steps.

  • Services Notes Summary
    • Track your service notes by month, type, provider, service recipient, and lastly case/intake. 

If you want to customize any of the pre-built reports, you can make a copy of them. The copy will show up in your Custom Reports, and you can edit it freely. It is important to note that this will not change the main Pre-built Report.

Why does it look like there are duplicate records in my dataset?

In data development, it is important to identify the most granular level of data (the grain) in a table to help define what that table shows you. For example, is there one row per case or one row per case per caseworker? In the second example, you may see multiple rows for one case because you have a row for each unique combination of case and case worker. In order to know which dataset to start with you need to think about what question you’re trying to answer: do you want to know how many cases there are or how many case workers?

Why is reporting so hard?

Creating custom reports can be very challenging. Without a background in data analytics understanding how to break down a question into the requisite parts can be overwhelming. It is deceptive when a question is simple to ask like “How many people did we serve last week?” You need to be able to then identify what it means to serve someone, who is included in that list of people, is it based on the start date? End date? What happens if someone starts receiving services before the week but ends mid-week? There are many scenarios to consider and being able to identify all of those and then find the corresponding datasets for that is challenging. 

Ultimately, our user base is generally case workers who specialize in human services and direct support of their participants, not data scientists. By asking them to create their own custom reports we are asking them to develop a new skill set, and that is always hard. 

Do we have subdomain-specific reports?

Not at this time. It is on the roadmap to identify what subdomain-specific reports would look like and what would be necessary to build those and supply them to specific customers. Currently, many of the subdomain-specific reports require dynamic fields which are not automatically set up or passed from tenant to tenant. This remains a blocker for us at this time, and we are in the very early days of setting up templates for people to start from for industry standard reports. This is not on the near-term roadmap, as it has many dependencies but it is on our radar. 

Will I get notified when reports are updated?

Great question! We don’t offer any notifications when your data is refreshed.  Data from Casebook native fields and dynamic fields should be available within 30 minutes. Our form response data in reporting is refreshed every evening between 8p and 9p EST. 

How can I count my services? Intakes?

In order to count things in Reporting it’s recommended you use an ID, not a string. This means if you’re trying to count the number of cases you’re going to start with the Cases dataset and count the number of case_id. It’s recommended that you do a distinct count whenever you’re counting to be safe. If you want to count the number of Intake Reports you would start with the Intakes dataset and count the number of report_id. 

Things get a little trickier if you want to filter on a value that’s not in your dataset. For example, if you want to count the number of women in your active caseload. You need to be able to get the demographic information from the People Dataset and the caseload information (both active and primary assignee=you) from the Case Involvement Dataset. As a rule, I like to start with the dataset I’m counting - in this example, because we’re counting people I start with the People dataset and then join the Case Involvements dataset in. Remember to always join on an ID, in this example, Person ID will do the trick. From there you can add in the fields you’re going to want to filter on and create your chart or visual. 

 
Datasets - Data View ID List

Pivot Tables, what are they and how do I use them? 

There are a number of visualizations available using our Reporting tool. Two of the most popular are Grid and Pivot Table, though the default is Column when you open a new visualization. What is the difference and what are they?

2

Grid is essentially a typical table, and it allows you to list out the values of each column you drag into the Columns section. There is limited capability beyond applying data filters to the Visual or the Data. 

The Pivot Table is more complex and can be intimidating. The main distinction is that a Pivot Table allows you to aggregate data. You are immediately given more options and ways to add data: Rows, Values, and Columns. For Pivot Table beginners I recommend starting with Rows and Values. What are the specific things you want to see? If I want to see the ethnicity of all the people we serve, I would start by dragging Ethnicity into the Column area. This breaks it into every ethnicity that’s been recorded. You always need something in the Values field, so if our goal is to count the number of people, you can add person_id to the values. It defaults to a count of all people broken into the ethnicities listed. There are many kinds of aggregation you can do on Values using Pivot Tables. You can do basic math like summing and averaging numbers, you can do basic statistical analysis and return the minimum or maximum values as well as the standard deviation and variance. You can also count rows, count non-empty rows, or count distinct rows.

TL;DR: Grids list all of the information of each column and should be used like a basic excel table. Pivot Tables allow advanced data aggregation and analysis.

I'm using a Pie Chart, and I see the others category. How do I change this to see each specific category?

Casebook defaults to grouping categories that make up 3% or less of the whole. You can change this by navigating to Settings, and then adjusting the %, as shown below. Screen Shot 2023-03-29 at 2.16.16 PM

Can I create my own custom age groupings, such as “0-11 months”, “12-29 months”, etc.? 

No, it is not currently possible. To get a little bit technical, the IF() function within cb Reporting requires that inputs and outputs be the same type of data. Since age is stored within Casebook as a number, the output must be a number. That is a different type of data than human-readable name like “0-11 months” which is stored as text.

My number fields are showing as whole numbers, but I entered them as decimals. How do I fix this?

Casebook will default to showing whole numbers, but you can change this to show decimals. To do so, click on the number field, as shown below. Screen Shot 2023-03-29 at 2.07.49 PM

Next, select Fraction Digits and change this number to the number of decimal places you wish to see. For example, if you wanted to reflect cost information, you would change this to 2. Screen Shot 2023-03-29 at 2.08.22 PM

Why are reports showing a different time? 

In the Casebook platform, all times in Reporting are stored in UTC Time Zone. To convert UTC to your local time zone, please visit https://savvytime.com/converter/. Alternatively, you are can convert UTC into your local time zone in Microsoft Excel with the formula, =A2+ (x/24) with x being the delta in hours between the time zones and 24 being the hours in a day. This formula can be applied universally in every time zone conversion. 


Example: 
  1. Calculate the delta in the two time zones you want to convert. To convert Pacific Standard Time to Eastern Standard Time, the delta is 3 hours. 
  2.  Using the formula =A2+ (x/24), with x being 3, you can convert PST to EST in Microsoft Excel. 

We have, for example, ten potential options to choose from for this drop-down menu. Why do I see only, for example, two options when I filter in the cb Reporting visualization? 


When data is selected in a drop-down menu or entered in a text field, it is then added to the reporting database. So all possible options for a dropdown menu will not appear in reporting only the ones that are selected by a user on a record at least one time. 

How much time passes between when I enter data in a field or select data in a field and when it shows up in cb Reporting? 

    • Data from Forms is updated once a day around 8 pm ET.
    • All other data entered in Casebook is updated within 30 minutes of being saved in a business application.

Why are so many fields appearing twice? 


One-to-many relationships are the most common type in relational database design (versus one-to-one or many-to-many relationships). Casebook includes one to many relationships that describe the complexity of work done on a case. A client on a case may have multiple involvements on a case, expressed as multiple involvement records. A case for a family will have multiple people on a case or multiple services that a client may be enrolled in.  It can be thought of as displaying one row for each variation of the information that has been stored historically.  The value of this type of database structure is that it can enable a more insightful and powerful analysis to be conducted over time as it captures the history and allows you to study what happened when.

Reporting database lists out the one-to-many relationships (rather than aggregating data values into one cell) to support sorting and searching within the dataset. Where possible, we are creating smaller tables to support distinct counts of items in the reporting database or summing up numerical values and other operations. 

Take for example:

ABC Services has a caseworker named Yolanda Gutierrez, who is assigned as the lead social worker on the McGonigle family case.  Yolanda is supported by her co-worker Leticia, a life skills specialist, who is also assigned to support the family.

Two clients are in different phases of the programs offered.

  • Cerys McGonigle has completed the Intake & Assessment phases (involvement types) and is now in a “Service Delivery” involvement.
  • Patrick McGonigle is still being assessed but has completed the Intake phase.

In cb Reporting, when you pull a Current Involvement Report, you should expect to see the following rows:

Case Name Assignee Client Name Current Involvement Type
McGonigle Case Yolanda Gutierrez Cerys McGonigle Service Delivery
McGonigle Case Leticia Morin Cerys McGonigle Service Delivery
McGonigle Case Yolanda Gutierrez Patrick McGonigle Assessment
McGonigle Case Leticia Morin Patrick McGonigle Assessment

 

In cb Reporting, when you pull a Case Involvements Report, you should expect to see the following rows:

Case Name Assignee Client Name Current Involvement Type
McGonigle Case Yolanda Gutierrez Cerys McGonigle Service Delivery
McGonigle Case Yolanda Gutierrez Cerys McGonigle Intake
McGonigle Case Yolanda Gutierrez Cerys McGonigle Assessment
McGonigle Case Leticia Morin Cerys McGonigle Service Delivery
McGonigle Case Leticia Morin Cerys McGonigle Intake
McGonigle Case Leticia Morin Cerys McGonigle Assessment
McGonigle Case Yolanda Gutierrez Patrick McGonigle Intake
McGonigle Case Yolanda Gutierrez Patrick McGonigle Assessment
McGonigle Case Leticia Morin Patrick McGonigle Intake
McGonigle Case Leticia Morin Patrick McGonigle Assessment

For more information on this, check out this article.

What dynamic field types will be represented in Reporting?


Any dynamic fields added to a case, intake, or added to a person profile are now available in Reporting. Dynamic fields entered on a case are available on the case_involvements view and cases view. Dynamic fields entered on a person are available on people view and dynamic fields entered in Intake will show in the Intake view (coming soon). 

What if I want to count items on a case? 


Pull fields from the cases view to create a data grid wherein you can count items associated with a case. The cases view includes the following fields plus any dynamic fields that are populated. 

  • case_name
  • case_description
  • case_type
  • case_status
  • parent_case_status
  • county
  • goals
  • primary_assignee
  • primary_assignee_email
  • primary_assignee_phone
  • date_inactive_status (created field detailed in this ticket 179997647)
  • legacy_id
  • case_id
  • user_id
  • created_at
  • tenant_id

Forms Reporting FAQ

How do I tell what data was entered in the previous form or the new version of the form I just published? 

All form data appears in one table. The table will include a column, form_version_number. The more recent form versions have the highest number. For example, let’s say a form template is published. Then changes are made to the form template, and it is published again one time. Users fill out data in both versions of the form before and after updating. The data entered in the form will appear in that table for two versions of the form. In some rows, the value for form_version_number is 1, and in some rows, the value for form_version_number is 2. Screen Shot 2021-11-30 at 1.01.02 PM

What happens if I change the name of a form? 

If a user creates a new version of a form and changes the name of the form, the form name will be updated in Reporting. When a user opens up Reporting, and clicks on tables, the users should see the new name. Words in the form title are linked together with underscores.

What happens if I change the label of a form component? 

A form component may be a text field or a select component. If a user changes the label, saves that label, and republishes the form, that change will not be reflected in Reporting. Reporting will still show the previous name. This is a forthcoming feature.

What happens if I add more form components to a form and then publish a new version? 

The new data entered/selected in new form components will appear in new columns at the end of the table. Recommendation: make the form labels descriptive. For example: if you decide to update a form and add e-mail addresses for clients and for caseworkers, then the field labels should be Client e-mail address and Caseworker e-mail address to distinguish them in the form reporting table. 

When does this information get updated in reporting?

Form response data in reporting is refreshed every 4 hours at 3 AM, 7 AM, 11 AM, 3 PM, 7 PM, and 11 PM EST.

Can I join forms data with other datasets?

Yes, Form datasets have the ID of the unit of work they’re on. So if the form is on an intake, it has a report_id. If it’s on a case, it has a case_id; if its being joined to a people report, people_id, etc.