Analytics Software

I have a report that has a single filter which is an "is prompted" date filter. I use it in conjunction with a calendar dashboard prompt when displaying the reports on a dashboard, and the dashboard prompt has a default value that references a repository variable called "yesterday", which is a datetime datatype. Well, I want to schedule this report to be executed and emailed by an ibot, and the ibot execution of the report should always be for "yesterday". Furthermore, whatever solution I come up with to accomplish this, it must be able to be reapplied to report after report after report by much less technical BI end users than myself. So I'm thinking that if I can create a saved filter, no matter how complex to create, that can be easily added to all such reports by the end users, that would seem to be ideal.

i've tried to exhaustively go down a road of effecting a logical NVL() in an "Advanced SQL Filter" of the report using syntax along the lines of this, but I can't quite get it working. This is the closest I came to getting it to work:

"Dates and Times"."Transaction Date" = @{VALUEOF("TransactionDate")}{VALUEOF("yesterday")}

In the above example, "TransactionDate" is a presentation variable set by the dashboard prompt, and "Dates and Times"."Transaction Date" is a DATE datatype in the repository. Anyway, the above generates no error in either the dashboard with a prompt, nor in the "Results" tab of the Answers report-writing webpage (which I'd imagine simulates an iBot since the dashboard prompt is missing). However in both cases, "yesterday" is unconditionally used, and the "TransactionDate" presentation variable is ignored. In other words, everything works fine, except that when I select different values from the dashboard prompt, yesterday's data is still displayed. Argh!

I've seen a lot of threads & blogs online that seem close to this problem, but no cigar in the long run. any and all suggestions are so welcome. I'm ready to tear my hair out... :)

-Jim