I have a table that has a table-level filter. For some prompt values there will be no data in the table and just be blank (with column headers). My requirement is to display N/A in each data cell when this happens (so one row with N/A for each column). I have tried a few different ways to display N/A but can’t figure this one out. One idea was to use conditional formatting and and second was to create a variable (if isNull then N/A else y). The problem is when there are no rows in the table none of these formulas work. Any ideas?
I’ve done something similar in the past by putting the N/As in the table footer. Created a variable along the lines of:
= if count( [FieldToCount] ; distinct ) = 0 or isnull( count( [FieldToCount] ) ) then “N/A”
Sometimes it is not always null, try this variable: = If ([Field] = “”; “N / A”; [Field])
If there is no data returned by a query or by report/table filters, there are no values to compare. The values don’t exist vs being compared to null or “” or anything else, so you can’t use comparison logic.
I have to report nulls as zeros myself, and the only way to do it when the row and and column are null is to have a unique count value for each cell in the table:
count r1c1 = 0 + (count object where r1 condition = y and column 1 condition = y)
That forces a value of 0 in the cell even when the data does not contain any example of row 1 and column 1 conditions. You can then conditionally format the 0 to N/A.
It’s tedious to set up, but once you’ve created one count variable, it’s easy to copy the formula and change one condition as you go along the rows and down the columns. (I have to do this for statutory reporting - all I do now is export the tables to Excel and copy and paste values into the proforma.)
1 Like
I solved this by changing the query.
This same situation happened on another report and I used the work-around count(object) + 0 which forces a 0 when there are no rows in the table. Then I use conditional formatting to insert the N/A.