nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Case Insensitivity in OBIEE 12c

User_IP06I

Hello All,

We have a requirement where users want to filter a column irrespective of the case they Enter.

For Example, Member Name in Database is entered in Uppercase (JOHN). Users will drag the member name field in criteria and might enter john or John or JoHn. Irrespective of what case they enter OBIEE should return the data.

I know how to make this work on Dashboard but not in analysis where users shouldn't have to do some work around every time.

Please Help. Thanks In Advance.

Find more posts tagged with

Accepted answers

All comments

User_IP06I

@Gianni Ceresa @Christian Berg Any thoughts? You're the two people i see in this community who can answer any question. Can you please Provide your inputs?

Gianni Ceresa

If you use the "search" windows of OBIEE when selecting the value to filter by, by default OBIEE does a case insensitive search in the values you can then select what you were looking for.

I believe you want to add a filter in the criteria as you say you know how to deal with that in a Dashboard, or what else do you mean exactly?

User_IP06I

Thank you for Your response Gianni.

Yes, I need to enter the filter values in the Criteria just like in the Image below. Database will have the values stored as JOHN and JENNY but users want to see the data even when they enter it as John/Jenny or john/jenny.

Using Search option right next to Value box and searching for values one by one is not acceptable by business as they might enter more than 100 values sometimes separated by semi colon.

We need to make change at the server level so that user don't have to do these steps manually in the criteria.

Also, we tried changing the CASE_SENSITIVE_CHARACTER_COMPARISON in NQSConfig.INI file to OFF. It didn't help.

Let me know if you need additional information.

Gianni Ceresa

The config file has an explicit message next to that flag: "Case sensitivity should be set to match the remote target database".

Is your database case insensitive? If it isn't, you can't expect OBIEE to do miracles as it isn't a database.

User_IP06I

Oracle is our database which I think is case sensitive.

I came across this Blog which talks about the similar issue in OBIEE 10g.

https://shivabizint.wordpress.com/2010/11/04/enable-case-insensitive-search-in-obiee11g/

We tried to follow this approach but when we are restarting the services it is throwing the below error.

Unknown element 'CaseInsensitiveMode'^M

Element 'CaseInsensitiveMode' is not valid for content model: 'All(MaxConcurrentJobs,UsePlanCache)'^M

Gianni Ceresa

That config node is gone in 12c, and it would anyway impact the search window which does already work as you want but you don't want to use.

All in all, based on everything posted, there is an obvious data quality issue and you can't expect OBIEE to just deal with that and solve all the problems. Sure you can turn your Oracle database to behave as a case insensitive database, but do you want to do that? Not really, solving an issue by adding more issues everywhere else around isn't a solution.

The tool cover the need when using the search window, if your users don't want to use it, they should query the database and generate the list of values with the right case.

Of course you can cheat and transform your column in all uppercase or lowercase in the RPD and tell your users to do the same with their filters, but that's adding logic on queries making everything slow, and doesn't solve the original data quality issues but just hide it.

[Deleted User]

https://community.oracle.com/tech/apps-infra/discussion/comment/16777794#Comment_16777794

You're thinking way too much and too logically about this. Just enable case insensitivity across everything and performance, quality, cleanliness etc be damned. ;-))

Gianni Ceresa

Damn Christian, at least add a "sarcasm" hint to your post: there are people who are just going to do what you said 🙄

😂😂😂

[Deleted User]

User_IP06I

Thank you Both for your valuable inputs.

We have decided to use alter session set NLS_COMP=LINGUISTIC as Execute before query with in the connection pool in RPD. Seems like this resolves the issue we have.