Need Help With Survey123 Summary Report Syntax for Dates

[Anonymous User]

 I'm working on a summary report for pump station inspections.  I'm using a repeat tag to fill out the table which will insert a row for each pump station with it's summed inspection data.  I was able to get the syntax correct to sum the data for each question for each individual pump station.  But I'm struggling with how to show the most recent inspection date for each pump station.  

You'll see in my syntax I tried to use 'stats: max,REPDATE,InsDate' but that gives me the most recent inspection date of ALL the inspections.  I only want the most recent inspection date per pump station. I've tried shifting things around, but I keep getting errors and when I don't get an error I get the most recent date overall.

I inserted what I have so far for the report template table below.

$<$summary>

Station No.	Last Inspection Date	Pump #1 Run Hours	Pump #2 Run Hours	Total Run Hours	Bioxide Usage in/gals	Fuel Usage in/gals	Generator Run Hours	Electric Usage kWh	Water Usage gals
${#PumpStationLogs | stats:"count,FACILITYID;max,REPDATE,InsDate;sum,P1_RNHrs,SumP1;sum,P2_RNHrs,SumP2;sum,BIOXIDE,SumBiox;sum,GENERATOR,SumGen;sum, ELCTMTR_R18,Electric;sum, H2OMTR_R18,SumWater":"PSNAME": "" | where:"1=1" | orderByFields:"PSNAME ASC" }${PSNAME}	${InsDate | format:"DD/MM/YYY"}	${SumP1|round:2}	${SumP2|round:2}	${SumP1+SumP2}	${SumBiox| round:2}		${SumGen|round:2}	${Electric|round:2}	${SumWater|round:2}${/}

$</>

[BarbaraWebster1]

Hi Katie,

At first glance I'm not seeing any issues with the syntax here. The only thing I noticed is that the where filter should be before the stats filter, but I don't think that would cause the issue you're seeing. Would you be able to send your XLS form so I can test it a little more?

-Barbara

[Anonymous User]

Barbara,

I'll send you the XLS form in a direct message!

Thank you so much, I've really been struggling with this one, I'd like to have it wrapped up by Tuesday.

[BarbaraWebster1 · Accepted solution]

Hi Katie,

Thanks for sending the XLS form. When I test it, it looks like the max,REPDATE,InsDate stats parameter does return the most recent date for each station number, but since the outStatisticsFieldName variable (InsDate) is automatically converted from a date format to a string, there isn't a good way to convert the value from the epoch date format to a human-readable date.

I am logging an enhancement for improving date question handling in the stats filter. If you submit this issue to Esri Support (https://support.esri.com), they will be able to provide an official enhancement number.

In the meantime, a temporary workaround might be to create a calculate field (with the bind::esri:fieldType set to esriFieldTypeInteger) that formats the date into a YYYYMMDD format. Then you could use the max statistic in the report to find the most recent date based on that field (see screen caps).

Thanks,
-Barbara