dynamic prompt based on date
We have a filter that is used on most all of our dashboards & reports. "Year - Month". Examples:
2019 - Jul
2018 - May
We do not want to use 2 prompts to accomplish this.
I need a way to dynamically assign the default value to each report's prompt based on the result of a query, specifically:
and d_fp_ky = (select max(d_fp_ky) from d_fp where fp_validn_flg = 'Y');
I still need a list of all of the available YEAR - MONTH values in the prompt (sorted appropriately), but I need the one returned by the above SQL to be the default.
In our old reporting tool, we had a dynamic variable that stored the result of that SQL, it executed every 10 minutes to see if the value changed. Then on all reports/dashboards, the result of that variable's query was assigned to the default value of each report.
How can I accomplish this same end-result? I'm thinking something along the lines of using freehand SQL to generate my list of prompt values, and then using some form of MAX value somewhere to select the proper value from my list, but I have no idea how to accomplish this in YF.