Create a Canceled Installments Report

A6fa62462c25b50f5b8655240c67ec6d

Written by Lauren Neulinger

Last published at: January 20th, 2020

If you have canceled installments for requests in GLM/SLM, the steps below will help you run a report to view that information in one place, and to view the remaining installment balance for a process(es). 

For a refresher on the basics of building a data set and running a report, refer to the Reports & Data Sets section of the Knowledge Base.  

  1. Build your data set to pull in the appropriate process(es) and request statuses. 
  2. On the Choose Data Fields page, change the output to one row per installment.
  3. Select all fields you’d like included on the report, including the Installment Canceled field. 
    • Other recommended fields include Installment Amount, Installment Paid, Installment Balance, Installment Due Date, as well as identifying fields such as Project Name and Organization Name. 
  4. Run the report. 
  5. Click the Gear icon. 
  6. Click Group then select Project Name in the Grouping Column dropdown. 
  7. Click Add to finish grouping the report.
    • This grouping step is especially helpful if a request could have more than one installment. 
  8. You will now see one row of data for each installment, organized by request (Project Name). If an installment is canceled, it will display Yes in the Installment Canceled column. 
  9. At this time, the balance of a canceled installment does not display as $0 in reports. Applying a formula will correct the installment balance in the report. 
    • Click Formula.
  10. Give the formula a name, such as "New Installment Balance." This will become the name of the new column. 
  11. Copy and paste this formula into the Formula box: IIF([Installment Canceled]="Yes","0.00",[Installment Balance])
  12. Change the Display Format dropdown to the currency format. 
  13. Click Add.
    • Now any canceled installments will correctly show $0 as the balance in the new column of data.  
  14. Click the Gear icon. 
  15. Click Aggregate. 
  16. Select the new column created by the formula (in our example, this is labeled New Installment Balance) in the Data Column dropdown.
  17.  Select Sum in the Aggregate Function column.
  18. Click Add.
  19. Now you'll see the overall remaining balance for the requests in this report. 
    • You'll also see the remaining balance for each request.