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

[CharlottePeters95540]

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.).

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

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

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

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.