How to Build a Custom Report: Total Client Service Units by Service

From the Custom Reports tab in cb Reporting, follow the steps below to create a report to show data relating to the number of service units delivered to a client. 

  1. Navigate to the + icon in the bottom right-hand corner to create a new report. Screen Shot 2021-11-18 at 1.27.41 PM
  2. Select Visualization
  3. Select the data source that you use, Casebook Platform3-1
  4. Navigate down to the Views tab and select the Service_enrollments database, then click Select DataScreen Shot 2022-05-20 at 9.08.13 AM
  5. You are now brought to the report builder. Begin by adjusting your visualization type to Grid Data. To create the report, drag and drop the fields that you wish to report on into the report builder section. These might include, person_name, service_name, service_type, service_start_date, service_end_date, enrollment_status, provider_type, and provider_name amongst others.
    Screen Shot 2022-05-20 at 9.13.44 AM
  6. Add a new Calculated Field by selecting the + Icon in the Fields List, then selecting Calculated Field
    Screen Shot 2022-04-01 at 12.53.20 PM
  7. Name the Field, then add the following formulas: Please note that you will have to repeat this process for each formula
    1. The first calculated field will be called Days enrolled in a service and will return the number of days that this client has been enrolled in a service based on the start and end dates: if(isempty([service_end_date]), today()-[service_start_date], [service_end_date]-[service_start_date])
    2. The second calculated field will be called Days helped (this is the amount of days the client actually received a service) and will return the number of days the client received a service based on the frequency: if([frequency]="Daily", [Days enrolled in a service], if([frequency]="Weekly", ([Days enrolled in a service]/7), if([frequency]="Bi-weekly", ([Days enrolled in a service]/14), if([frequency]="Monthly", ([Days enrolled in a service]/30), if([frequency]="Quarterly", ([Days enrolled in a service]/120), if([frequency]="Annually", ([Days enrolled in a service]/365), 1))))))
    3. The third calculated field will be called Total service units and will return the number of service units that the client has received based on the days enrolled in the service and the frequency: [Days helped]*[service_units]
  8. Drag these new fields into the Grid Data section under Columns.
  9. Save the report by selecting the checkmark icon in the upper right-hand corner.