Hi, I have a query that returns results for yesterday and had the first occurrence where there were no results. The corresponding result in the visualization is this:
Is there a way to display something else?
This site is in read only mode. Please continue to browse, but replying, likes,
and other actions are disabled for now.
No. The database does have entries for yesterday but none that match the query. Sundays are typically light for us and with yesterday being Easter, a zero result is reasonable. We’d also have zeros on major holidays where all our locations are closed.
jesse
4
I understand now. You don’t want the chart to seem broken to an end user if there isn’t any data to chart. To my knowledge, it isn’t possible to add that kind of an indicator. One solution is to write a note describing the circumstances under which you expect the query to return empty and save it in the query description.
We came up with a workaround… not elegant but does the trick… don’t ask me how it works (our DBA came up with it)… basically it runs the query, if it doesn’t find anything it returns “total” with a value of 1 and “hostname” with value of “no results found for yesterday”. We did total of 1 because total of 0 ends up with an invisible slice.
SELECT COUNT() AS total, hostname FROM table WHERE
DATE BETWEEN (CURDATE() - INTERVAL 1 DAY ) AND CURDATE()
AND (NAME != ‘Temp folder’ AND hostname NOT LIKE ‘%-MAN-%’)
AND ACTION NOT LIKE ‘would%’
GROUP BY hostname
UNION ALL
SELECT DISTINCT 1 AS total, ‘no results found for yesterday’ AS hostname FROM table
WHERE NOT EXISTS
(SELECT COUNT() AS total FROM table WHERE
DATE BETWEEN (CURDATE() - INTERVAL 1 DAY ) AND CURDATE()
AND (NAME != ‘Temp folder’ AND hostname NOT LIKE ‘%-MAN-%’)
AND ACTION NOT LIKE ‘would%’ GROUP BY hostname )
LIMIT 5
1 Like