Summary Report - Inspections passed/failed by year and month

01-26-2021 07:35 AM
Occasional Contributor III

Hi all,

I am trying to create a summary report that will group inspections by year and month and report how many passed and how many failed.

Report Fields: 

  • InspectionDate(Date DataType)
  • FunctionStatus(Text DataType) -- This field defines pass or fail.


I have used the following syntax which reports every inspection by every inspection date.  I'd rather group by just year and month and report the total count..  THEN I'd like to be able to know how many passed and how many failed within that year and month.

${#Emergency_Light_Inspections | stats:"count,OBJECTID,inspectionCount":"InspectionDate": "" | where:"1=1" | orderByFields:"InspectionDate DESC" }${InspectionDate| format:”YYYY MMMM”}

${inspectionCount }${/}


I'm looking for the result  to look something like:

2020 January                60 Inspected   (10 passed     50 failed)   

2020 February              15 Inspected   (5 passed      10 failed)         

2020 March                  130 Inspected  (50 Passed       80 Failed)      

Any help with Syntax would be much appreciated!!

0 Kudos
0 Replies