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

[IsmaelChivite · Accepted solution]

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.

[JohnLucotch2]

Thanks so much is this great. 

[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?

[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]

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.

[BenBaker1]

After a closer look, I had applied a Date Filter within Survey123 that was hindering the queries in the template:

Once I removed the date filters and re-ran the report, the numbers match up.

[JohnLucotch2]

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

Thanks

[RandyWeaver]

Instead of a summary report, is there a way to use a WHERE clause for individual feature reports? I know that there is the "Filter" capabilities when setting up the data for reports, but we have several criteria that need to be met in order for a response to be included in a specific report. We have to report on both "All" of the features from a specific timeframe or attribute, as well as a specific sub-set of those "All" features that need to meet certain criteria based on the responses.

Since we cannot use images in summary reports, I have no way other than having to keep adding all of the criteria to the "Filter" section of the Survey123 Manage site each time I want to pull that specific sub-set of records. Have you come across scenario's like this? Is there a way to use WHERE clauses in individual feature reports?