Display value and label in OBIEE prompt

Misstram94

Hi guys,

I'm facing the issue:

I want to display value in prompt is Name, but I want to value will be selected is ID.

example:

Value displayed:      7, It's fashion

Value selected:       7

if you have any idea on this, please help me!

Thanks,

Tram

[Deleted User]

OBIEE 11G - Double column (Descriptor Id) [Gerardnico]

Misstram94

I appreciate your help in advance. But I didn't find out the feature like screenshot:

Gianni Ceresa

What is your version? As there are something like 58 different versions of OBIEE around it would help if you provide it ....

In 12c there is a kind tabbed visualization for the options and the checkbox is on the second tab (can't remember if it's "More" or something else the name of it).

Misstram94

Thanks @Gianni Ceresa, my version is 11c. It's not support features to do like that.

asim cholas

Try a substring where u use the valuee(presentation variable).

Gianni Ceresa

Descriptor Id columns are there in 11g, they weren't in 10g (can't confirm if only in 11.1.1.7 and after or directly in the first 11g release, can't remember).

The link @Christian Berg posted is about 11g.

So if you don't see that part of the options on your column prompt it's maybe because your column doesn't have a descriptor Id column set in the RPD? Or you are aren't using a column prompt.

As soon as the RPD columns have a descriptor id and you use a column prompt you are supposed to have that option in your prompt (and when filtering in analysis as well you have the extra options to decide if filtering on the column or the descriptor id one).

Misstram94

Hi asim,

How can I do like that?

Gianni Ceresa

There is one thing we didn't asked you ...

Does your physical source (or a logical column formula on the RPD) generate the "7, It's fashion" value?

So you really have things like "7, It's fashion" stored in your source and want to only get the number out of it? (in this case 7)

If that's the case you don't have tons of options, the way @asim cholas suggested is the only one: find position of the first comma "," and substring from the beginning of the string till the comma and cast that thing as number (assuming you want a number and not a string representing a number).

I would also suggest to look in your data source if you don't have the number along the description so you can setup the descriptor id feature in the RPD.

Gianni Ceresa

Something like:

CAST(LEFT('@{your_variable}', LOCATE(',', '@{your_variable}')-1) as INTEGER)

If your variable isn't always in the format " number, ..." this formula will return you an error because the 2nd attribute of LEFT must be greater than 0, so if your data can be in a different format you must manage that with a CASE when or setting a default value for your variable like: CAST(LEFT('@{your_variable}', LOCATE(',', '@{your_variable}')-1) as INTEGER)  

Misstram94

the "7, It's fashion" value which is combination of ID and Name. The formula like: CONCAT(TRIM(CAST("Item As-Is"."Subclass Number" AS char)), CONCAT(', ',TRIM(CAST("Item As-Is"."Subclass" AS char)))).Sorry I couldn't understand what you mean , I expect the selected value is ID  and displayed value is "ID, Name", the selected value will be mapped to analysis.

Thank you very much!