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; } } }

Feature Reports

JohnLucotch2

Good Morning,

We have been playing around with the new Summary Feature Reports. Based on the documentation you can apply filters. Can this be done on all data fields in a feature report?

${Inspections_June_2020|where:"1=1 !important"}

Find more posts tagged with

Accepted answers

IsmaelChivite

Hi John Lucotch‌

Correct, you can use fields from your survey to perform the queries.

From the help at Feature report queries—ArcGIS Survey123 | Documentation

where—A where clause for the query. Any legal SQL where clause operating on the fields in the layer is allowed. For example, where:"weather='sunny'" will cause the filter to only use responses where the weather field is exactly the value sunny. if where is used with other filters, they should be implemented in the order where, stats, orderByFields, resultRecordCount.
!important—This qualifier can be added to any filter but resultRecordCount to override any constraints on the values that the filter returns. For example, if you were printing reports from only the last seven days, where:"weather='sunny' !important" would cause the filter to only use responses where the weather field is exactly the value sunny, but pulls from all applicable responses in the feature layer, rather than just the responses selected to be printed.

Below is an example showing how you can use fields from your survey in the queries. Look at the queries to define the contents of the red and green tables.

Hope this helps.

All comments

JohnLucotch2

@IsmaelChivite In the summary is there syntax to summarize by the current date? I can't find any syntax.

Thanks

BenBaker1

That resolved the issue on the first and part of the second items. I had been dumping a new row into a tab by month of an Excel file using Integromat, then had a set of formulas that was summarizing each month into a table. I'm trying to recreate that table in a summary report that I can send to administrators once a month. Here's the query I'm using:

${FireInspections_pts|where:"InspType=100 AND ReceivedDt BETWEEN DATE '2020-01-01 00:00:00' AND DATE '2020-02-01 05:00:00'"|stats:"count,OBJECTID"}

The InspType field is a coded domain (select_one in S123) and 100 corresponds to "Annual".

I tried Jan - March, which should show these numbers:

Instead, I'm getting this in the feature report:

When I filter the data in Survey 123, I get the the same (14), but when I look at the feature layer, there are 19 inspections. So, now I am not sure 1)how I should be filtering the data to include all results and 2) why the same formula with dates changes for February and March are just showing 0s.

IsmaelChivite

Hi Ben.

Question 1: I pasted your instruction in notepad and found that you are using cursive quotes. Can you try making them all straight quotes? Microsoft Word tends to insert cursive quotes and this can create syntax validation errors. Use " and instead of “. Same for single quotes. Use ' instead of ’. Lets see if that resolves the problem.

Question 2: I also see cursive quotes. Can you try changing them to straight?

BenBaker1

This is a really helpful feature for reporting workflows that require a "hard copy" medium in addition to a Dashboard!

Ismael Chivite‌, I'm having a couple issues formatting the queries in my Summary Report Template. The first one is formatted like others that works, so I don't understand why it's failing. The other is related to filtering by dates. I'm not clear on the proper syntax to achieve what I'm wanting to.

First, this query if producing the following error when I try to create a sample report.

${FireInspections_pts |where:“ActionTaken= ‘approved’ !important”|stats:"count,OBJECTID"}

Next, I'm trying to populate a table showing a total of each inspection type by Month. I have the query working to summarize total inspections by type, but I have not been able to get an expression to work that will parse the counts by a specified date range.

The "Total" (far right) query is:

${FireInspections_pts|where:“InspType=100 !important”| stats: “count,OBJECTID”}

I've tried including date ranges for the January cell, but the syntax does not seem to be correct:

${FireInspections_pts|where:“ReceivedDt = BETWEEN DATE ‘2020-01-01 00:00:00’ AND DATE ‘2020-01-31 00:00:00’”| stats: “count,OBJECTID”}

What am I missing with these two queries?

JohnLucotch2

Thanks so much is this great.

IsmaelChivite

Hi John Lucotch‌

Correct, you can use fields from your survey to perform the queries.

From the help at Feature report queries—ArcGIS Survey123 | Documentation

where—A where clause for the query. Any legal SQL where clause operating on the fields in the layer is allowed. For example, where:"weather='sunny'" will cause the filter to only use responses where the weather field is exactly the value sunny. if where is used with other filters, they should be implemented in the order where, stats, orderByFields, resultRecordCount.
!important—This qualifier can be added to any filter but resultRecordCount to override any constraints on the values that the filter returns. For example, if you were printing reports from only the last seven days, where:"weather='sunny' !important" would cause the filter to only use responses where the weather field is exactly the value sunny, but pulls from all applicable responses in the feature layer, rather than just the responses selected to be printed.

Below is an example showing how you can use fields from your survey in the queries. Look at the queries to define the contents of the red and green tables.

Hope this helps.

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.