Divide string value and show related data in Universe design

building semantic layer
1

Dear Experts,

I have 2 tables

Table 1

ID Name

1 “12,18,19”
2 “7,9,11”

Table 2

ID Value

7 data7
9 data9
10 data10
11 data11

By Using Universe design i want to generate a report. In that report i want to display the Table 1 ->Record 2 i.e ID=2 & Name filed value
“7,9,11” as “data7,data9,data11”

I hope every one understand the above scenario !!!

I am assuming it can be done using “Functions” in Universe design

Please someone explain me how to handle this scenario ?

Thanks in Advance.

santhosh_03 :australia: (BOB member since 2009-07-08)

2

You’d need to build a database function that can be accessed by the user defined in the universe connection.

The code should, at its highest level:
Count the number of commas in the string
Contain a cursor that loops through chopping up the string and outputs the results.

This obviously depends upon your rdbms - the solution will, for example, look different in SQL Server and Oracle.

3

Mark,

Can you please provide the code if you have for any database.

Rakesh_K :india: (BOB member since 2007-12-11)

4

Rakesh,

I don’t have code but in SQL Server I’d:

Declare variables for manipulating the string:
input_string, output_string, string_post, etc.

Declare a cursor to loop through the input string and write out the output string.

I’d have to test that it would work with BusinessObjects - it’s something that I’ve done a few times in ETL processes and data take-on routines though.

5

You can, if you are on version 3.0 and above also use a stored procedure in the universe to do this…

Mak 1 :uk: (BOB member since 2005-01-06)

6

I’m using SQL server 2005 , So please can you provide the full function code.

Thanks

santhosh_03 :australia: (BOB member since 2009-07-08)

7

Am I correct in saying that you want to turn:
“7,9,11” into “data7,data9,data11”?

Run the following in a query window (doesn’t matter which database)


declare @instring varchar(50)
declare @outstring varchar(50)

set @instring = '7,9,11'

select @outstring = 'data'+replace(@instring,',',',data')

select @outstring

The object code would simply be

'data'+replace(column_name,',',',data')