Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to create links with multiple values in a column?

Received Response
81
Views
15
Comments
Rank 6 - Analytics Lead

Dear Gurus,

I am currently using OBIEE 12.2.1.4.

I came across a requirement where the user needs to create links for a few of the columns.

pastedImage_0.png

Now the user wants to create links on these 2 columns to get further details about the product and Department.

For e.g., link on  'a' --> goes to Product 'a' details, link on 'b' --> goes to Product 'b' details and so on

link on '10' --> goes to Department '10' details,  link on '20' --> goes to Department '20' details

Welcome!

It looks like you're new here. Sign in or register to get started.
«1

Answers

  • Rank 2 - Community Beginner

    Edit: Original post was updated to provide real context with information that was necessary for a real problem description.

  • Rank 2 - Community Beginner

    Oh great, NOW the image is added and the text adjusted. Would have been nice to include it in the original posting right away or add a second post. Modifying the original post makes any subsequent response look out of context.

  • Rank 6 - Analytics Lead

    Hello Christian,

    It seems the image was not pasted somehow after posting the question.

    I have updated the original post with the image.

    The problem is that I have to create links in such a manner that the column holding multiple values, should get links for each of the values.

  • Rank 2 - Community Beginner

    That's horrible data. Comma-separated strings which should by all rights be rows.

    Have fun substring-ing and then href-ing things. This is analytics, not web page programming.

  • Rank 6 - Analytics Lead

    My initial response was the same...

    But I can't help as this is the data we are getting and the requirement is like that only.

    I even don't know how many values can come in a column, so cannot use substrings as well.

    Unfortunately, this is the scenario. Let's see if there is some other Genius mind who have already encountered something similar.

  • Rank 2 - Community Beginner
    SonPat99 wrote:I even don't know how many values can come in a column, so cannot use substrings as well.

    You can, but it's atrocious spaghetti code and depending on how much data comes back and needs to be parsed - also a performance concern.

    SonPat99 wrote:Unfortunately, this is the scenario. Let's see if there is some other Genius mind who have already encountered something similar.

    I did and the proper solution was re-design rather than hacking. It's perfectly do-able but the question is SHOULD it be done.

  • Rank 6 - Analytics Lead

    I did and the proper solution was re-design rather than hacking. It's perfectly do-able but the question is SHOULD it be done.

    How did you re-designed? This is the data coming from the source and we do not have any control over there.

    Plus, the customer is not in agreement to show multiple rows if there are multiple values to the column.

    I can try for a re-design if you can help me understand what you have done and how.

  • Does your source really store "a, b, c" , "f, g", "10; 20", "40; 50; 10" as values? (so a single string)

    "a, b, c" doesn't mean Product ID "a", Product ID "b", Product ID "c", it means a string "a, b, c".

    If somebody somewhere is making up those strings (and I'm quite sure they are: somebody discovered the power of LISTAGG in a view?), they have to do the job completely and concatenate URLs instead of just simple values.

    A simple solution to what you ask doesn't exist.

    You could do the job in the DB has it comes with support for REGEX, but in OBIEE's front-end it's by far not simple. Reusing Christian words, it's an atrocious spaghetti code.

    Somebody did a mistake somewhere (and didn't got any data modelling lesson in his life), there isn't a function named DOTHENEEDFUL("Your table"."Product ID"). It will be messy in OBIEE, fix it back to the source or as close as possible to that.

  • Rank 2 - Community Beginner

    Beaten by Gianni. You'd basically need to loop through the whole string and to a regex replace of each element between commas with an href.

  • Rank 6 - Analytics Lead

    Hello Gianni,

    Does your source really store "a, b, c" , "f, g", "10; 20", "40; 50; 10" as values?

    No, this is a UI and the UI is showing multiple values for a specific field; which again stores the data in column format (and not row-wise).

    We are using PIVOT to convert the data in ROW Wise format and in the same process, using LISTAGG to club all the values for a column (like Department).

    Yes, you are correct that "a,b,c" will mean a string and not separate values of a, b and c.

    Our DB is 12c and supports REGEX, but still the problem is how to display it as 3 different values in OBIEE UI (even if I create concatenated URLs using 3 values).

    How OBIEE will differentiate with the URLs as these will be again coming as a single String?

Welcome!

It looks like you're new here. Sign in or register to get started.