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

Sort columns with Links in OBIEE and case-insensitive

SonPat99

Dear Experts,

We are on OBIEE 12c (Version: 12.2.1.4.19087)

Problem Statement:

I have created a report where one of the columns is having HTML link enabled and the display column is different as below:

"'<a target="__blank" name="'|| "COLUMN_TO_BE_DISPLAYED" || '" href='||"LINK_COLUMN"||'>'||"COLUMN_TO_BE_DISPLAYED"||'</a>'"

the "name" tag helps me to sort the column. However, the issue is that this sorting is done as A B C .... Z a b c...z

I want the sorting to be like A a B b C c... Z z or a A b B c C.... z Z

In short, the SORTING should be CASE-INSENSITIVE (but the user should see the original data, i.e. case sensitive)

Please suggest if this is possible in OBIEE.

Also, I want this applicable in such a way that the user can sort the report as and when required directly (instead of modifying the criteria).

Regards,

SonPat

Find more posts tagged with

Accepted answers

All comments

Jerry Casey

How about hidden sort columns? One with a UPPER fnx applied, so it is case-insensitive, with two views,each using the separate sort columns.

A view selector could provide the sort options.

SonPat99

Hello Jerry,

I have already tried that and the user complains that he will lose the control to sort the report on the go.

Unfortunately, this will not work.

Gianni Ceresa

The UI gives you 2 options for sorting: ascending or descending.

What you ask is, for that given column which is a custom formula, to have another kind of sort based on rules you set yourself. Obviously you can't get that "for free".

The hidden column Jerry mentioned is the way to go for these situations.

If you want to give this back to users to freely sort, it means you have to get OBIEE doing your kind of sort by just picking "ascending" or "descending".

If you create a column in RPD with the link inside (you move your formula from the analysis into the RPD), you create another column being COLUMN_TO_BE_DISPLAYED all uppercase or all lowercase, to remove the A vs a difference in sorting. You will be able to set for your link column the other second one as a sort column.

It's what you generally do with the column having the month names and that you still want to sort Jan to Dec and not A-Z, in this case you use it to force OBIEE to trick your link column on your custom condition.

SonPat99

Hello Gianni,

Thanks for the explanation.

Just for my understanding, so in this case, if the USER sorts the link column dynamically, will it work properly?

As my sort is done on COLUMN_TO_BE_DISPLAYED, which is UPPER (let's say), then the data will all come in UPPER Case only and not as original (which is mix of upper and lower), right?

The user wants the Original data with Sorting freedom to himself...

Gianni Ceresa

The sorting column, your COLUMN_TO_BE_DISPLAYED, which you transform either all upper or all lower case, is not going to be visible. It's a technical column in the RPD, you use for sorting your link column and that link column is the one you will expose in addition to the existing content of the subject area.

Setting a "sort column" on the business column will impact the generated queries in the background, nothing will be visible on screen. If there aren't bugs, the user will simply sort the link column ascending or descending. In the background OBIEE will deal with it.

You just have some RPD work to do, because your user isn't ready to do any effort.

SonPat99

Hello Gianni,

In the below formula:

"'<a target="__blank" name="'|| "COLUMN_TO_BE_DISPLAYED" || '" href='||"LINK_COLUMN"||'>'||"COLUMN_TO_BE_DISPLAYED"||'</a>'"

COLUMN_TO_BE_DISPLAYED is the column that will be displayed with a hyperlink linking back to LINK_COLUMN.

Per your suggestion, I created a duplicate of COLUMN_TO_BE_DISPLAYED as "COLUMN.... FOR SORT" with UPPER function and sorted "COLUMN...DISPLAYED" with "COLUMN... FOR SORT".

When I ran the report again, I do not see any difference in the generated query.

Not sure if I have to enable sorting on COLUMN_TO_BE_DISPLAYED or LINK_COLUMN in RPD.

Please suggest.

Gianni Ceresa

It isn't the "COLUMN_TO_BE_DISPLAYED" that you must to sort, but you aren't using this column in your analysis.

You must create a new column in the RPD containing the full link, the whole piece of HTML, and that one is the one to sort based on the technical column you created already.

SonPat99

Thanks Gianni,

Please allow me some time to implement and check this. It will be like a day or so to completely test this.

I have a positive feeling that this will definitely work...

SonPat99

This is working fine.

What if there are multiple columns in different reports?

I found this the Doc ID 2614514.1 as well. What will be the impact if I am following this?

Gianni Ceresa

SonPat99 wrote:What will be the impact if I am following this?

You will be changing the behaviour on the whole system. 'A' isn't 'a', if they would be the same why didn't your ETL fixed that by either turn everything uppercase or lowercase?

The sort you asked here isn't the normal natural sort : things are sorting in a case sensitive way in many many many places, it's just the normal way of sorting things.

You must ask all the users of your platform if they agree to switch to a case insensitive sorting, it's their decision.