Refine Value List Selection List in Text Type User Prompt Filter

Amulya Sharma shared this problem 3 years ago
Resolved

Hello,

On a Freehand SQL Report, I'm trying to make it easy for my end users to only have the option of selecting employees who were actually active between specified date ranges (Date ranges can be selected using another User Prompt Filter).

When trying to use a Custom Query under Value List Selection, I can only write a SELECT query which would return a single column of ALL the employees. Not the ones actually active between the date ranges specified since I cannot enter a WHERE clause here (See attached image). Basically, I'm unable to link the Employee UPF with the Date UPF's


I can make the list refined in the Freehand SQL code but I don't want the end user selecting from a list of ALL employees when they aren't even going to be on the report based on the service date ranges entered. I hope you can see the disconnect. How do I solve this? Is this feature possible at all in Yellowfin? Thank you.

Best Answer
photo

Hi Amulya,


Thanks for the clarification, as we discussed earlier this will not be possible, and judging by the response I received from the product team I am now hesitant to say it ever will be.


Sorry for the inconvenience here, and let me know if you have any questions!


Regards,

Nathan

Comments (3)

photo
1

Hi Amulya,


Using a WHERE clause in a custom query filter should be possible. If you are not having luck, it might be worth trying to use the WHERE clause in a nested SELECT such as:


  1. SELECT row FROM (SELECT * FROM ____ WHERE ____)


If you are not able to get this to work, it would be helpful if you could tell me what version and build you are using (this can be found in the system information section of the admin console). Please let me know if you have any luck with this.


Regards,

Nathan

photo
1

This is Yellowfin BI 7.2. I'll try finding out the build number.

I apologize I wasn't clear before. What I would want is to obtain this Employee list based on what the user selects first as StartDate and EndDate. Then the Employee List would dynamically populate based on the selected User Prompt Filter values just before that. (See attached image for the query and filter list)

photo
1

Hi Amulya,


Thank you for the clarification. That will be a bit harder as Freehand SQL bypasses all objects created in Yellowfin to query the underlying database directly. I will continue to investigate this issue and let you know if I am able to get this to work.


Regards,

Nathan

photo
2

Thank you. I can go down the Drag and Drop route too if it has additional features I'm unaware of and lets me implement this feature.

photo
1

Hi Amulya,

Unfortunately after looking into this one for a while, I have to say that this will not be possible. The timing of when filter values are cached versus when a filter is applied is just not conducive to the functionality you are seeking. I am hesitant to say that it will be possible to include as an enhancement, but if you are interested I can convert this question into an idea so that the product team has a chance to review this?

Regards,

Nathan

photo
1

Yes. Please do. I was wondering if I could put in a handy SQL trick to sort of find a "duct tape" solution which I could code while writing the main report code but it wouldn't help in any case since the list of selectable values in the filter are populated not from the main report SQL code but in the SQL entered in reporting layer during the Filter configuration stage.

photo
1

Hi Amulya,


I have converted this into an idea.


One route I was exploring, that ended up not working, but you may still find useful elsewhere, is creating a view from a stored procedure. This allows you to pass your filter values as parameter arguments to a stored procedure, and then return data that to the report builder that has these filters applied.


This did not work because you cannot cache additional values from a stored procedure view, but you may find other uses for this.


Regards,

Nathan

photo
photo
1

Hi Amulya,


After speaking with the product team it seems that the only way to achieve this will be to use a filter hierarchy with date part formatters so:


Year: 2017, 16, etc

Month: March April

Field to have limited results


Unfortunately the only way to limit the filter result set in the near future will be through a filter hierarchy, so hopefully this is applicable to your needs.


Let me know if you have any questions here!


Regards,

Nathan

photo
1

Hi Nathan,

Thanks for still working on this. I am not sure how date parting the date filter would help because we're still not defining a connection with the limited result field in the report filter definition. The query that drives both the filters are entered separately while populating the value list and do not cross-reference, which is why you'd anticipate the whole unrefined result set as opposed to restricted by dates.

photo
1

Hi Amulya,


Thanks for the clarification, as we discussed earlier this will not be possible, and judging by the response I received from the product team I am now hesitant to say it ever will be.


Sorry for the inconvenience here, and let me know if you have any questions!


Regards,

Nathan

photo
photo
1

Hi Amulya,


I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan