What is it?
Cached Values On Demand & Custom Query Cached On Demand are filter format options that were introduced in Yellowfin 7.1 + back in May 2016.
These options are available at both the report & view level.
How does it work?
These options allow Yellowfin to return the filter values on report load, and cache them into memory, rather than cache to the DB. When you close the report, the filter values are not longer user. Launch the report again and the values are once again returned and cached into memory. This does mean that loading said reports will most likely be slower that without having this option enabled. .
When should I use this?
Do you really need this? In most cases, the short answer is 'no' .
This option was primarily implemented with client orgs & data source substitution in mind.
Under normal circumstances, when you have data source substitution enabled, any filter that lives on the default org that is also accessed from the client will cache a list of values for ALL possible orgs in the Yellowfin DB.
However, in these scenarios what you should be doing is scheduling the filter so that while it can take time to run, it runs behind the scenes (preferably out of normal business hours).
If you were to use the cached values on demand option, when you open a report at the client org, only results from that client org are returned. Filters are not refreshed for any other org, as the results are not stored in the DB.
It also means your filter values are always the latest, with the downside that it needs to fetch the results from the DB on every report load.