Survey123 report template: return count of question within a nested repeat using if statement

187
0
06-29-2022 09:45 PM
CharlottePeters95540
New Contributor II

Hi,

I have a survey form with nested repeats structured as:

  • Project
    • Plot (repeat)
      • Feature (repeat within Plot)
        • Survey type (question)
        • Rating (question)

I created a report template that will provide totals for all Projects surveyed in a year.  Part of the report contains a section that tallies the # of different ratings (e.g. excellent, good, etc.) for each survey type (e.g. CB, CD, FB, etc.).

CharlottePeters95540_5-1656563001390.png

If a survey type does not exist for a particular year then I want the ratings table to be hidden on the report. 

I've tried using:

${#rptFeature}${if SurveyType=="CB"}

Survey Type

CB

# of Features Monitored

${rptFeature | where:"SurveyType='CB'" | stats: "count,objectid"}

# Excellent

${rptFeature | where:"SurveyType='CB' and FeatRating='excellent'" | stats: "count,objectid"}

etc......

etc........

# Failed

${rptFeature | where:"SurveyType='CB' and FeatRating='fail'" | stats: "count,objectid"}${/}${/}

also tried ending with ${/}${/rptFeatures}

but end up with a table being repeated for each of the selected survey type records

CharlottePeters95540_1-1656562200312.png

If I remove ${#rptFeature}${if...}....${/}${/} and use the following syntax, I get the correct totals for each survey type.

Survey Type

CB

# of Features Monitored

${rptFeature | where:"SurveyType='CB'" | stats: "count,objectid"}

# Excellent

${rptFeature | where:"SurveyType='CB' and FeatRating='excellent'" | stats: "count,objectid"}

etc....

etc....

# Failed

${rptFeature | where:"SurveyType='CB' and FeatRating='fail'" | stats: "count,objectid"}

CharlottePeters95540_4-1656562929567.png

However, if the survey type does not exist (i.e., FB), the table for that non-existent survey type still persists in the report.

CharlottePeters95540_3-1656562779885.png

I cannot seem to get the syntax right for creating a table that tallies the total ratings for each existing survey type from the nested repeat. Any suggestions?

I'm using Survey123 Connect v 3.14.281 to create the form. I'm attaching my xlsxForm, report template, and 2 generated reports with different survey type scenarios. I've pared down the questions from the original to focus on the trouble spots.

0 Kudos
0 Replies