Summary Report: by Day of the Week

[DanteLee]

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'"}

[alison_gou]

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

[DanteLee]

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

[alison_gou · Accepted solution]

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