New Topic

Community Topic Question Report Builder

Eric Hagfors

Yellowfin Support Team

Assigned

Category Report Builder

Product Version 8.0

dynamic prompt based on date

Bryan Mack shared this question ago

Answered

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.

The same question

Replies (6)

Mike Sheehan ●

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

Reply URL

Mike Sheehan ●

Hi Bryan,

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

Regards,

Mike

Reply URL

Bryan Mack ●

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.

URL

Bryan Mack ●

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.

URL

Mike Sheehan ●

Hi Bryan,

Thanks for your response. I believe this may have to do with how the field itself is configured. Did you create the DATE that you used the 'yyyy - MMM' format for using the 'Create Date Function' option I showed you earlier? If you head into the View, what actual Data Type is this date field? It should be a Data Type of Date in order for it to sort properly based on date.

I'm referring to this in the View:

Regards,

Mike

URL

Mike Sheehan ●

Hi Bryan,

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

Regards,

Mike

URL

Bryan Mack ●

Striking out all over the place. What I need is:

Prompt on Year - Month: Ex: "2019 - Aug", "2019 - Jun"

--must show in the above format, not number or date format

--must ONLY show prompt values for the month we are in and earlier (so "2019 - Sept" and later must NOT show, but on Sept 1, "2019 - Sept" should become an option without our having to intervene)

--must sort chronologically, not alphabetically

--must use the MAX value as the default -- so the most recent month is the default

What I've tried

--------------

1) Converting to date of the first of the month, in the format: YYYY - MMM in YF

----it then sorts alphabetically instead of by actual date, so this won't work

2) Using reference codes:

----sorts correctly

----cannot limit values

----cannot set MAX default (as my prompt is a character string)

3) Use freehand SQL to limit values

---limits values

---sort works -- BUT -- the YF required sort overrides my sort, screwing it up

---doesn't allow for MAX value

What we really need is

1: ability to select MAX on character fields based on a reference code value instead of the reference code display value

and one of the following 2:

a. Ability to NOT enforce a custom sort when already defining one in freehand sql

b. Ability to weed out certain values from a non-freehand-sql prompt, so not every option is visible

URL

Eric Hagfors ●

Hi Bryan,

Touched base with Nathan on this; he will be getting in touch with you regarding this.

Thanks,

Eric

URL

Eric Hagfors ●

Hi Bryan,

Just wanted to let you know I've notified Nathan of this issue, and he will be reaching out in regards to this. Please let me know how it goes.

Thanks,

Eric

Reply URL

Eric Hagfors ●

Hi Bryan,

Just wanted to confirm you've gotten assistance with this?

Thanks,

Eric

Reply URL

Bryan Mack ●

I had a call with Nathan, this has an idea that is in development right now. This can be closed. Thanks!

Reply URL

Eric Hagfors ●

Thanks for the update, Bryan. I will mark this as Answered.

Thanks,

Eric

Reply URL