topic Re: Summary Report: by Day of the Week in ArcGIS Survey123 Questions

Summary Report: by Day of the Week

DanteLee — Tue, 24 Aug 2021 16:05:35 GMT

Is it possible to filter a summary report by day of the week? I've tried the following "legal SQL" in the where clause, but I get an error when generating the report

An error occurred when querying data from the feature layer. 400: Cannot perform query. Invalid query parameters. 'where' parameter is invalid The expression leads to this error: ${# layername | orderByFields:"objectid ASC" | where: "DATEPART(WEEKDAY, contact_date) = 'Monday'"}

Re: Summary Report: by Day of the Week

alison_gou — Wed, 01 Sep 2021 07:33:01 GMT

Hi @DanteLee

Of course you can!

Since I don't really see your survey, please take a look at my syntax and table.

Here is my syntax in a summary section in feature report :

${Form_14 | stats:”sum, pump1”|where:”dayofweek=’Monday’”}

My goal for this syntax is to sum up the value for pump1 when it is Monday.

Besides syntax, I think it could also help you to understand better if I posted my table for the summary report as below:

Give it a go to see if this can help you.

Regards,

Alison

Re: Summary Report: by Day of the Week

DanteLee — Wed, 01 Sep 2021 14:49:30 GMT

Ah, looks like you calculate the day of the week into a separate field in the survey form? I was hoping to run that on the fly in the summary using the SQL DATEPART() function

Re: Summary Report: by Day of the Week

alison_gou — Wed, 08 Sep 2021 08:42:15 GMT

Hi @DanteLee

I tried to reproduce with SQL DATEPART() function, however, I don't think this is supported, to be specific, not by feature server.

I tried to use SQL DATEPART() function directly on feature layer where parameter, feature layer returned error "'where' parameter is invalid".

In order for SQL DATEPART() to work in Survey123 report, it has to be supported by feature layer.

I would encourage you to contact Esri Support to proceed on confirming whether SQL DATEPART() is supported by feature layer. If it is supported, I would love to hear back from you to work it out on survey123; if it is not supported and needed for your workflow, then you may ask Esri Support to log an enhancement.

Hope this helps.

Regards,

Alison