Choice list interactive filters based on database views take their choices from the base table. Even when choices are defined for the view table of a database view, the interactive filter does not recognize them.
Steps to Reproduce
- Navigate to Reports > Interactive Filters.
- Click New.
- Create a filter based on Choice list.
UI control type: Single Input.
Table: Incident SLA [incident_sla] (a database view created on Incident)
- Click Submit.
- Add the interactive filter to a dashboard.
The available choices are All, None, 1-Critical, 2-High, etc.
- Navigate to the Incident table (the base table for Incident_sla).
- Filter the columns on Priority and view the dictionary entry for Priority.
There are ~90 choices configured for Priority.
- Add Table to the view (Cog > Personalize List Columns > Move Table to Selected column)
All the tables in that column extend the Task table.
- Right click on Task [task] in the Table column and select Show matching and then right click on en in the Language column and select Show matching.
There are 5 choices left. These five are the same that appear when you look at the available choices on the dashboard.
- Filter the Choices list on Table=Incident and click Run.
There are no choices on Language=English or Table=Incident. When there are no choices, the list in the interactive filter is populated by the choices in the base table.
- Add a choice on the Incident table (1 - Major, for example).
This should appear in the interactive filter and replace the choices from the base table.
- Navigate to the Incident table. Right click a priority to see its options.
The choice list now shows only one - the choice you created in the previous step.
The personalize_choices security role can be used to let non-administrators modify the options for Choice elements on all tables.
If there is a choice created on a table, the interactive filter should show only the choices created for the table. If no choices are created for a table, the displayed choices should be those configured for the base table. However, in releases through Jakarta choices created for database views are ignored in favor of those created for the base table.
There is no workaround applicable. This issue is fixed in the Kingston release.
Related Problem: PRB1021036