Summary Report: by Day of the Week

449
3
Jump to solution
08-24-2021 09:05 AM
DanteLee
Occasional Contributor II

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

1 Solution

Accepted Solutions
alison_gou
Esri Contributor

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

View solution in original post

3 Replies
alison_gou
Esri Contributor

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:

alison_gou_0-1630481515622.png

 

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

 

Regards,

Alison

0 Kudos
DanteLee
Occasional Contributor II

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

0 Kudos
alison_gou
Esri Contributor

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