dynamic prompt based on date

Bryan Mack shared this question 1 week ago
In Progress

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:


select fp_yr_mth_nm

from d_fp

where 1=1

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.

Comments (4)

photo
1

Hi Bryan,

Thanks for reaching out. You can use a 'max' as a default value when it's detected as a metric or date, but the problem is he needs a column that has both the month - year.


So assuming you already have a date column (which I assume you do), you can use the 'month start date' date function at the view level, which will convert the data to month/year (in numeric format), then just change the format to display it in text, and cache it at the report level:

/vzZooeuvObAAAAABJRU5ErkJggg==

Please let me know if you have any questions on this.


Regards, Mike

photo
1

Hi Bryan,

I just wanted to check in and see how things are going with this.

Regards,

Mike

photo
1

I haven't been able to get this to work yet. I do not have a date field, I could create one but I also have some rows that have YTD (year to date) values, so I have values for each month, and in the same field, I have "2019 - YTD". The YTD will never be default, which is fine, but I can't use that field to convert to date as the YTD records will throw an error.

photo
1

Ok, I circumvented the YTD issue by assigning the YTDs to 31-DEC-01 date, so YTD will always appear at the bottom, which is fine. However, with my custom field, which is a DATE stored in the YF format 'yyyy - MMM' it is now sorting alphabetically, and selecting the MAX selects August since that is the first alphabetical month. In theory, it should be selecting December (even though that still isn't want I want as I need to find a way for YF to limit which prompt results it shows)

I'd LOVE to be able to use SQL to develop prompts & set prompt defaults and separate everything from the graphic interface.

photo