Using Conditional "where" function inside an "if" statement in Survey 123 reports to give count only if greater than zero

[ChristopherDawe]

Hi,

I have a building survey with a repeat that has information on features on the building. One of the questions in the repeat is type and has a domain with 30 or so options.

In my report, I am trying to get a count of the number of features of each specific type, but only return the text and number if that number is greater than zero. 

I can successfully return the count of each feature type if there are one or more features using :

${if (Features_repeat| getValue:"count")>0}

number of Option1: ${Features_repeat | where:"FeatureType='Option1'"|getValue:"count"}

number of Option2: ${Features_repeat | where:"FeatureType='Option2'"|getValue:"count"}

number of Option3: ${Features_repeat | where:"FeatureType='Option3'"|getValue:"count"}

...

${/}

which would return something that looks like: 

number of Option 1: 0

number of Option 2: 1

number of Option 3: 0

...

however with over 30 options, I only want to show the counts of those where the feature type is present (Option 2) and discard any that don't have any (Option 1 & 3) so i don't have lots of rows with zero counts. So the above would become

number of Option 2: 1

I have tried including the "where" function inside the if and having one statement per choice as below but it returns invalid syntax

${if (Features_repeat| where:"FeatureType='Option1'"|getValue:"count")>0}number of Option1: ${Features_repeat | where:"FeatureType='Option1'"|getValue:"count"}${/}

${if (Features_repeat| where:"FeatureType='Option2'"|getValue:"count")>0}number of Option2: ${Features_repeat | where:"FeatureType='Option2'"|getValue:"count"}${/}

...

It seems that the where function is not valid within the if statement.

Does anyone know of a way to show the count for select features within a repeat, and only show if they are present. Theoretically i would also like to use this for stats to calculate averages, minimum, maximum etc but from filtered lists and only show those where the count of type is greater than zero.

Any help would be greatly appreciated

Thanks

[Neal_t_k]

Try this out.

${#Features_repeat | where:"FeatureType='Option1'"}
number of Option1: ${Features_repeat | where:”FeatureType = 'Option1'" | stats:"count,objectid"}
${/}
${#Features_repeat | where:"FeatureType='Option2'"}
number of Option2: ${Features_repeat | where:”FeatureType = 'Option2'" | stats:"count,objectid"}
${/}
${#Features_repeat | where:"FeatureType='Option3'"}
number of Option3: ${Features_repeat | where:”FeatureType = 'Option3'" | stats:"count,objectid"}
${/}

Your other option could be to do the aggregation in the form in some hidden questions, then call those fields in the report.