Categories
- All Categories
- Oracle Analytics Learning Hub
- 17 Oracle Analytics Sharing Center
- 17 Oracle Analytics Lounge
- 224 Oracle Analytics News
- 44 Oracle Analytics Videos
- 15.8K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 84 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
In criteria tab when applying a filter only 1 out of 2 unique values appears
OAS 5.9
Hello Experts,
I have a column named 'TYPE' which has 2 unique values 'DOMESTIC' and 'INTERNATIONAL'. When I try to filter the column 'TYPE' it only shows 'DOMESTIC' and does not show 'INTERNATIONAL'. However in results tab all data comes i.e it shows both 'DOMESTIC' and 'INTERNATIONAL'. Any help in investigating why while applying the filter only 'DOMESTIC' option comes would be really helpful. When I type 'INTERNATIONAL' in filter search bar then it shows up in the search, but without explicitly searching only 'DOMESTIC' comes.
Total values in the type column are 50000 rows out of which 'DOMESTIC' comes 20000 times and 'INTERNATIONAL' comes 30000 times. There is no null value. The Data type in both rpd and database is varchar 20
I tried a workaround: When I do cast(type as char) then both the values appear in filter tab, however I cannot implement this solution. So, I need to find out why one value is not coming.
Best Answer
-
Hi @Gianni Ceresa @Christian Berg,
Thanks for your help. After reading the physical query generated in the log we noticed that OAS was not applying distinct. This was because type was a key. Now this issue is resolved. Thanks again for your help
0
Answers
-
First thing: OAS 5.9 is our of support, you should really consider an upgrade (OAS 7.6 aka 2024 was released just yesterday), 5.9 is from 2021 and OAS only has 2 years of support.
If you look at the session logs you will see the query executed to show the values of the filter. Based on that you will be able to identify what solution you could implement (the query is generated based on your RPD, therefore it is impossible to say much about that).
If the only solution is to retrieve more data, there is probably a parameter defining the number of records retrieved for filters.
2 -
Hi Gianni,
We are upgrading to cloud soon, if I run the physical query in database I get the right result i.e both International and domestic comes. Its only when I apply the filter in criteria I see only the Domestic Option. Any idea where will I find parameter detail for filters.
0 -
Your first starting point is always the log. What query does the log actually capture? What's the SQL and what's the WHERE clause? That SQL will then lead you to start investigating the next step:
The model. Your models defines everything and all the logic the engine uses to produce the SQL. If you have a TYPE column with 2 value then the SQL should result in 2 "rows" representing the 2 values. Doesn't matter if each value exists once, 12 times, 40000 times or 2 billion times.
1 -
Do you see the query fired when you click on the column in the filter and go to select the values? Because there as well a query is executed, and the result is used to show you the values. It is visible in the session logs or the log files.
1 -
Good to hear: the answer is (almost) always in the query :D
0 -
And when the physical query is no the answer, then it's almost always because the log level isn't high enough ;)
1
