Summary Report - How to Leave out Rows with Null Values

AbigailEbelherr · ‎10-28-2023

Hello, all. I am attempting to create a summary report for a survey that lists projects by their year and funding source. The survey, itself, has entries for each funding source and year combination. So for our Capital Improvement Fund, we have cif_2024, cif_2025, cif_2026, etc.

For the summary report, I'd like a list of all projects by funding source with the amount of funding by year. However, I do not want projects returned if there is no funding for that source at all. I've come up with the following template:

The idea is to have a new table for each funding source. In case you cannot see it, the formula is: ${#survey| where:” cif_2024!=’’”|orderByFields:"project_name"}${project_name}

The idea is to add "cif_2024!='' and cif_2025!='' and cif_2026=!''" etc. so that the if all fields are null, it won't add the project to the list.

I'm running into two issues:

1) The formula doesn't work at all with the != no matter what's between the apostrophes. When I run a sample report, I get the following error: "An error occurred when querying data from the feature layer. 400: UNKNOWN_ERROR 'where' parameter is invalid."

2) When I remove the !, the expression works if I have a number between the apostrophes, such as "cif_2024='90000'. If I use "cif_2024='', it returns no entries, and I do have an entry where cif_2024 is blank. So I am concerned that my fields aren't actually blank even if that's how they show up in the data table. The fields are conditional in the survey on choosing the correct year and funding source.

Any help is very appreciated.

abureaux · ‎10-30-2023

In feature reports, and = &&. So more like ${if my_thing1 && my_thing2}

If you have a look at this page and search for "&&" (it's near the bottom), you will find their table of operators. Honestly, they don't go through all the possible examples, but it is the best place to start at least.

I would try something like this to start ${if cif_2024!="" && cif_2025!=""}Stuff in here${/}

View solution in original post

abureaux · ‎10-30-2023

Try IF

${if my_field}w/e you want in here${/}

An example for your case:

Header	Header	Header	Header
${#survey}${if cif_2024}${project_name}	${cif_2024}	${cif_2025}	${cif_2026}${/}${/}

AbigailEbelherr · ‎10-30-2023

Thank you for replying, and it's given me something to start with. Your example works for if cif_2024 is empty, but I want to exclude values only if ALL cif_20XX fields are empty, not just one. I looked for examples of using IF statements for multiple fields, and I haven't found any. I tried some myself:

${survey}${if cif_2024 and cif_2025}${project_name}, which gave me a syntax error.

Then I tried ${survey}${if cif_2024} and ${if cif_2025}${project_name}, but that returns no records.

Since these are integer fields, I also tried ${survey}${if cif_2024>0}${project_name}, which works on its own, but if I try ${#survey}${if cif_2024>0} or ${if cif_2025>0}${project_name}, I once again have no records returned.

Do you have any further thoughts?

Vinzafy · ‎10-30-2023

Hey there! From the documentation, the logical operator for and in feature reports is &&.

Since those are integer fields and you want to exclude values only if ALL cif_20xx fields are empty, I wonder if something like the following expression would work for you:

${#survey}${if (cif_2024 = 0) && (cif_2025 = 0) && (cif_2026 = 0)...}<body contents>${/}${/}

However, that might give you issues as if a cif value doesn't exist, it likely wouldn't be 0, but null. Maybe this one would work.

${#survey}${if (cif_2024 == null) && (cif_2025 == null) && (cif_2026 == null)...}<body contents>${/}${/}

abureaux · ‎10-30-2023

In feature reports, and = &&. So more like ${if my_thing1 && my_thing2}

If you have a look at this page and search for "&&" (it's near the bottom), you will find their table of operators. Honestly, they don't go through all the possible examples, but it is the best place to start at least.

I would try something like this to start ${if cif_2024!="" && cif_2025!=""}Stuff in here${/}

AbigailEbelherr · ‎10-30-2023

You are the best! This is what worked. I just didn't know the operators were different for reports and Suvery123 Connect.

Edit: Ok, so I jumped the gun. This formula worked as long as one of the fields in the entry had a number, but I forgot I didn't have an entry with nulls in all fields (because I changed it when I was testing). When I created an entry with all nulls, this formula didn't filter out that entry.

But this led me to the right solution. As I said before, I had integers for my entries, so I used: ${#survey}${if cif_2024>0 || cif_2025>0 || cif_2026>0 || cif_2027>0 || cif_2028>0}${project_name} That neatly filters out any entries that are null across the board.

I still don't know what you would do for a text version if you wanted to filter out null values, but this solves my problem, so I'm happy.