Enabling access filter on the report requires custom query update on other filter componets
24 June, 2014
Hi,
I am trying to enable a source access filter to my report which has couple of filter components, some of which are backed by custom query under Entry Style of the component.
When I enable an access filter (BRANCH_NAME_WITH_CODE) on the report, the filter components that have a custom query (i.e Dom/Int) indicates below message where the custom sql s are provided:
"This filter is dependant on another filter.
Your query should return these columns:
1Source Filters:BRANCH_NAME_WITH_CODE
2: Dom/Int"
(If I have more access filters enabled on the report, they lists too)
Does this mean that the query must return the text "BRANCH_NAME_WITH_CODE" apart from the actual column value for that component? Why does it say that it depends on another filter as I haven't set up any dependency between the two?
Thanks
Shanika
I am trying to enable a source access filter to my report which has couple of filter components, some of which are backed by custom query under Entry Style of the component.
When I enable an access filter (BRANCH_NAME_WITH_CODE) on the report, the filter components that have a custom query (i.e Dom/Int) indicates below message where the custom sql s are provided:
"This filter is dependant on another filter.
Your query should return these columns:
1Source Filters:BRANCH_NAME_WITH_CODE
2: Dom/Int"
(If I have more access filters enabled on the report, they lists too)
Does this mean that the query must return the text "BRANCH_NAME_WITH_CODE" apart from the actual column value for that component? Why does it say that it depends on another filter as I haven't set up any dependency between the two?
Thanks
Shanika
conti..
When I click on "Test Query" without updating the custom SQL as per the given message, it says " The result set does not match the expected schema" and the following exception is thrown when the report is run.
In this case the filter component does not display any values for user selection.
When I update the custom SQL to return "BRANCH_NAME_WITH_CODE" (as per above example) along with the actual column values(Dom/Int) , then it returns the values correctly in the Dom/Int filter component for user selection for certain users but not for some other users. This is how the "Test Query" result look like :
BRANCH_NAME_WITH_CODE DOM_INT
--------------------- -------
BRANCH_NAME_WITH_CODE Domestic [D]
BRANCH_NAME_WITH_CODE International
Those who cant see any Dom/Int filter values for selection, if run the report with other allowed criteria, they see report results which belongs to Dom or Int (report has a column to display Dom/Int value), which means the view returns results for the given criteria though the filter component doesn't show values for user selection.
Is this a bug in Yellowfin? Or how do I get around this issue?
Thanks
Shanika
When I click on "Test Query" without updating the custom SQL as per the given message, it says " The result set does not match the expected schema" and the following exception is thrown when the report is run.
In this case the filter component does not display any values for user selection.
When I update the custom SQL to return "BRANCH_NAME_WITH_CODE" (as per above example) along with the actual column values(Dom/Int) , then it returns the values correctly in the Dom/Int filter component for user selection for certain users but not for some other users. This is how the "Test Query" result look like :
BRANCH_NAME_WITH_CODE DOM_INT
--------------------- -------
BRANCH_NAME_WITH_CODE Domestic [D]
BRANCH_NAME_WITH_CODE International
Those who cant see any Dom/Int filter values for selection, if run the report with other allowed criteria, they see report results which belongs to Dom or Int (report has a column to display Dom/Int value), which means the view returns results for the given criteria though the filter component doesn't show values for user selection.
Is this a bug in Yellowfin? Or how do I get around this issue?
Thanks
Shanika
Hi Shanika,
to answer the question from your first post, that's just the way Yellowfin works, you have to return the source filter as well as the cached filter in your custom query. Regarding the situation you described in your 2nd post, when you say [i]" but not for some other users" I'm curious what is different about those users, are there source filter entries for all users or only a subset? If so, does this subset match the users who don't see any Dom/Int filter values?
regards,
Dave
to answer the question from your first post, that's just the way Yellowfin works, you have to return the source filter as well as the cached filter in your custom query. Regarding the situation you described in your 2nd post, when you say [i]" but not for some other users" I'm curious what is different about those users, are there source filter entries for all users or only a subset? If so, does this subset match the users who don't see any Dom/Int filter values?
regards,
Dave
Thanks for getting back. With regards to " but not for some other users" please see my screenshots below :
User 1 : Cached filter entries for each access filter applied in this report
The report filter components contains values for both access filter enabled components (highlighted in blue)as well as filters that has custom SQL queries (highlighted in green). The report returns results. Works as expected :
User 2 : Cached filter entries for each access filter applied in this report (I have included few from each where there are more entries)
The report filter components contains values for access filter enabled components (highlighted in blue) but not for filters that has custom SQL queries (highlighted in red). The report returns results. Marked in red in results area are the data that appears in results but do not show in filters for user selection :
The difference between two users are that the User 1 has % cached for every filter but User 2 has different values cached under access filters as shown above.
Hope this helps.
Thanks
Shanika
User 1 : Cached filter entries for each access filter applied in this report
The report filter components contains values for both access filter enabled components (highlighted in blue)as well as filters that has custom SQL queries (highlighted in green). The report returns results. Works as expected :
User 2 : Cached filter entries for each access filter applied in this report (I have included few from each where there are more entries)
The report filter components contains values for access filter enabled components (highlighted in blue) but not for filters that has custom SQL queries (highlighted in red). The report returns results. Marked in red in results area are the data that appears in results but do not show in filters for user selection :
The difference between two users are that the User 1 has % cached for every filter but User 2 has different values cached under access filters as shown above.
Hope this helps.
Thanks
Shanika
Hi Shanika,
That has definitely helped.
I am now able to replicate your issue and have raised a defect (TASK ID = 166272).
Looks like when you have multiple source filters and custom queries, it's not showing any filter values when you are not using wild cards.
Obviously this is not how it should work, as a I suspect it's related to custom queries needing to return all values from all source filters applied to the report.
I have marked the task as HIGH so it will be looked at next week and should be fixed in time for the July 7.0 release.
Apologies for the inconvenience and will get to your other requests today.
Regards,
David
That has definitely helped.
I am now able to replicate your issue and have raised a defect (TASK ID = 166272).
Looks like when you have multiple source filters and custom queries, it's not showing any filter values when you are not using wild cards.
Obviously this is not how it should work, as a I suspect it's related to custom queries needing to return all values from all source filters applied to the report.
I have marked the task as HIGH so it will be looked at next week and should be fixed in time for the July 7.0 release.
Apologies for the inconvenience and will get to your other requests today.
Regards,
David
Thanks David. Looking forward to the fix.
Hi David,
We are using Yellowfin BI 7 - build 20140730
I see the above task (Task ID = 166272) is implemented under the Yellowfin BI 7 - build which is listed under 20140721 change log :
"
[166272] Fixed an issue where Custom Query Cached Filters with multiple Source filters would not cache properly. "
How ever my original issue still doesn't seem resolved. For the above "User 2" I still dont get values for filters that has custom SQL queries.
What should I do to make this work?
Regards
Shanika
We are using Yellowfin BI 7 - build 20140730
I see the above task (Task ID = 166272) is implemented under the Yellowfin BI 7 - build which is listed under 20140721 change log :
"
[166272] Fixed an issue where Custom Query Cached Filters with multiple Source filters would not cache properly. "
How ever my original issue still doesn't seem resolved. For the above "User 2" I still dont get values for filters that has custom SQL queries.
What should I do to make this work?
Regards
Shanika
Hi Shanika,
That's definitely not good. It was such a pain to replicate and get fixed.
Let's make sure nothing differs with this user.
Did you find the other users are able to cache the filter ok, or is it still every user that previously had the problem?
Regards,
David
That's definitely not good. It was such a pain to replicate and get fixed.
Let's make sure nothing differs with this user.
Did you find the other users are able to cache the filter ok, or is it still every user that previously had the problem?
Regards,
David
Hi David,
It's looks as if a user has % for all then it works fine but not otherwise.
Basically the above issue remains the same.
Regards
Shanika
It's looks as if a user has % for all then it works fine but not otherwise.
Basically the above issue remains the same.
Regards
Shanika
This issue is now resolved.
Looks like the custom query used was not pulling in the dependant source filter results.
After modifying the custom query, all was caching correctly.
Looks like the custom query used was not pulling in the dependant source filter results.
After modifying the custom query, all was caching correctly.