nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

Alison_Gou_

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

All comments

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_

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.

Hope this helps.

Regards,

Alison

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.