User Defined Object

building reports desktopintelligence
1

Hi All,

I am trying to create a user defined object wherein I need to have a decode function ( even a simple if -then -else would do).
But there exists no function which helps me do that…or I am unable to locate it. Is there a way to have a decode ( or if-then-else) ?

Thanks
Ashish

Listserv Archives (BOB member since 2002-06-25)

2

Hi,

The reason decode isn’t available as a UDO is due to the multiplicity of ways it can be used - there can be 3 params, 4, etc. This is very difficult to parse (well, BO think so).

Look in c:\Program Files\BusinessObjects\Oracle (or similar) for ORA7EN.PRM (or similar, depending on your Oracle version). In it you should find… (39)
NAME= Decode
TRAD= String
HELP= Compares and replaces strings
TYPE=A
IN_MACRO=N
GROUP=N
SQL=decode()

You need to add a new section, say 57, at the end.

(57)
NAME= Decode2
TRAD= $Source Value: $Case 1: $Result 1: $Else Result: HELP= Compares and replaces strings
TYPE=A
IN_MACRO=Y
GROUP=N
SQL=decode($A,$A,$A,$A)

$A implies alphabetic, $N numeric $D date.

You can only use this for a four parameter decode. You must declare a Decode3, Decode4 etc if you want more options. Painful but better than nothing.

Hope this helps,

Steve

Listserv Archives (BOB member since 2002-06-25)

3

Thanks steve…this should definitely help. But why doesnt the original decode (39) doesnt appear enen though it has an entry in the file ?

Ashish

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 00-03-01 09:59:48 EST, you write:

Thanks steve…this should definitely help.
But why doesnt the original decode (39) doesnt appear enen though it has
an
entry in the file ?

Ashish

[ snip ]

(39)
NAME= Decode
TRAD= String
HELP= Compares and replaces strings
TYPE=A
IN_MACRO=N
GROUP=N
SQL=decode()

The line IN_MACRO=N tells BusObj that this function entry should NOT be available for users. Since there are - as already mentioned - potentially an infinite number of ways to fill out a decode() function, it is considered too complex for users. Okay, not exactly true: it is considered too complex to validate that a user is using the syntax correctly.

Each function where IN_MACRO=Y will show up in the user list of functions.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

5

Steve,

In case I use many parameters, would the entry look something like this ?

(57)
NAME= Decode2
TRAD= $Source Value: $Case 1: $Result 1: $Case 2: $Result 2: $Case 3: $Result 3: $Case 4: $Result 4: $Case 5: $Result 5: $Else Result
HELP= Compares and replaces strings
TYPE=N
IN_MACRO=Y
GROUP=N
SQL=decode($A,$A,$N,$A,$N,$A,$N,$A,$N,$A,$N,$N)

I tried doing this …but the decode definition comes up with only 2 parameters…($A,$A,$A,$A)…i.e ( If A=B then 1 else 2)

Ashish

Listserv Archives (BOB member since 2002-06-25)

6

Ashish,

I’ve never tried a more complicated decode than a simple if-then-else using a UDO.

I have now! It doesn’t work. I seem to remember someone else on this subject about six months ago saying this was a bug, fixed in v 4.1.?

We are on 4.1.5 and Oracle 8.1.5

I have a work around - not nice, but workable. When creating your UDO, use a decode with 4 pareams (if-then-else) and make the last param another decode of the same object. For example:

Decode3 ( {Contract Details\Contract Type}
, “A”
, “A type”
, Decode3 ( {Contract Details\Contract Type}
, “B”
, “B type”
, Decode3 ( {Contract Details\Contract Type}
, “C”
, “C type”
, “Other”
)
)
)

This works, but is less than elegant.

Any comment from the BO community on how to get BO to support more fancy decodes?

Regards,

Steve

Listserv Archives (BOB member since 2002-06-25)

7

Hi Folks,
One of my user can create user defined object on his machine but can’t select that object into result objects box in query panel.This problem is only on the user’s machine.I logged into BO as the user on my machine and created UDO and able to select that into Query panel but where as the user can’t on his machine.Is this because of some setting in .prm file,If so could some one can help me in solving this issue.Any help is appreciated.

Thanks
arkay

Listserv Archives (BOB member since 2002-06-25)

8

Hi,

Just a clarification. Are you logging into your system as the same BO user as the one having problems on the other system. If so then probably, the USE USER OBJECTS may be been disabled in the Supervisor module for that user.

Rgds
Naveen

Listserv Archives (BOB member since 2002-06-25)