Skip to main content
Topics#Reports & DashboardsGreg Coogan's Question
Greg Coogan asked in #Reports & Dashboards
Feb 21, 2017, 4:37 PM

How do I build a matrix report that shows open and closed cases for this month but also shows the total active cases of all time?

Goal: A matrix report that shows open and closed cases for this month but also shows the total active cases of all time. The Y Axis is the User assigned to the Case. The X Axis is the This Month's Active Cases, This Month's Closed Cases, and Total Active Cases of All Time.

 

I devised a solution but it is more complex that I'd like:

  1. Have a checkbox on Case called "ActiveThisMonth" which is set using a scheduled job that runs daily. The scheduled job would set the checkbox based on if the Case is Active and was Created this month.
  2. Have a checkbox on Case called "ClosedThisMonth" which is set using a scheduled job that runs daily. The scheduled job would set the checkbox based on if the Case is Closed and was Created this month.
  3. The matrix report would be for cases from all time and create report formula fields for the Cases Closed This Month and the Cases Active This Month. Since I cannot add a filter of only Active cases, the Grand Total wouldn't be the correct numbers I wanted to display. I'd have to create a report formula field that counts the Active cases only, possibly using CASE.

Knowing the solution I thought of so far, is there something else I can do that won't require creating coding an Apex class, creating a scheduled job, and the checkbox fields on the Case object?

13 answers
  1. Steve Molis (You Owe Me a Beer, Inc.)
    Feb 21, 2017, 6:48 PM

    For that I think you'd want to create a custom field, like a Formula(Checkbox) to exaluate the status and the created date, like this:

    DATEVALUE(CreatedDate) < DATE(YEAR(TODAY()), MONTH(TODAY()), 01)

    or maybe this

     

     

    AND(
    IsClosed = FALSE,
    DATEVALUE(CreatedDate) < DATE(YEAR(TODAY()), MONTH(TODAY()), 01)
    )

     

     
Write an answer...
0/9000