I have several reports that have multiple queries. I'd like to direct to one page if a specific query in the report is empty, but the rownumber() report function seems to not work if one query is blank and another is not. I've also tried using a field from the query in question and the IS NULL, IS MISSING, = "" operators, but it doesn't seem to resolve to true. Does anyone know how to test a query in a report with many queries to see if the query results are empty? The examples already demonstrated here (that I saw via a search) seem to only apply to a single list.
I have the exact same question. Please share the result.
Thank you.
Try adding a data item to all your queries and define them as "Running-count([fieldName])" and name it something unique in each query (ex. rowCntSales, rowCntEmp, etc)
Then define your variable:
(rowCntSales = 0) OR (rowCntEmp = 0) OR ...
HTH. Please keep us posted.
That sounds like a good suggestion, but unfortunately it doesn't seem to work. If I add that data item into the list and there is data, it looks fine - but if I put a filter on the data and the query returns no rows, then nothing is put into that data item at all, not even a 0 - no rows exist for the query, so no number is displayed.
I guess this would be like in SQL where you create a query with a derived column - even if you hard code something in that column, there won't be any rows if the WHERE clause restricts there from being any rows returned.
This is quite frustrating! It would seem logical that there would be some way to determine if a particular query in a report returned rows or not, regardless of whether other queries in the report did. Oh well, still hunting...I guess I may have to punt and create a single stored procedure or UDF that evaluates the same filter(s) and returns a scalar value.
use the FOR report syntax?
total(SalesAmount for Report)?