BusinessObjects Board

Row to column converstion at Universe level

building semantic layer
#1

HI

I am using BO 6.5 and Sql server.

I have a object in Universe B_first. Based on condition I have to convert the rows into columns.

Currently I am making it at report level by changing the query

B_first is the main field.

SELECT
b1.lnkey, b1.B_FIRST,
b1.WHICHBORR,
Fborr = (SELECT B_FIRST FROM .BOR Y WHERE Y.LKEY = b1.LKEY AND Y.WHICHBORR = 1),
Sborr = (SELECT B_FIRST FROM BOR x WHERE x.LKEY = b1.LKEY AND x.WHICHBORR = 2)
FROM
BOR as b1
where b1.whichborr in (1,2)and
b1.B_FIRST is not null

How Can I define Fborr , Sborr at Designer level.

Hope I am clear.

rajx72 (BOB member since 2006-08-02)

Query regarding a merge report
#2

Use CASE statements to define those objects in the universe.

BO_Chief :us: (BOB member since 2004-06-06)

#3

hi

I tried using Case statement


Fborr
====
case when WHICHBORR = 1 then bfirst End

SBorr
=====
  case when WHICHBORR = 2 then b_first end

but when i pull these on report it is showing wrong result

Eg i want this

Fborr Sborr

A B
AA BB

but it is showing
Fborr Sborr

A Null
Null B
AA Null
Null BB

I am writing the Case statement in Select of object.

where i am going wrong

rajx72 (BOB member since 2006-08-02)

#4

rajx72,

Try this…


SELECT 
Fborr = (SELECT max(y.B_FIRST) FROM BOR Y WHERE Y.LKEY = b1.LKEY AND Y.WHICHBORR = 1), 
Sborr = (SELECT max(x.B_FIRST) FROM BOR x WHERE x.LKEY = b1.LKEY AND x.WHICHBORR = 2) 
FROM	BOR b1 
where 	b1.whichborr in (1,2)
and	b1.B_FIRST is not null

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

#5

Hi Johan

Thanks for the reply.

I can not use this at Universe level. I am looking for a solution at Universe level.

thanks

rajx72 (BOB member since 2006-08-02)

#6

rajx72,

But you can ! :yesnod:

Define the objects like:


(SELECT max(y.B_FIRST) FROM BOR Y WHERE Y.LKEY = b1.LKEY AND Y.WHICHBORR = 1)

and


(SELECT max(x.B_FIRST) FROM BOR x WHERE x.LKEY = b1.LKEY AND x.WHICHBORR = 2)

or


max(SELECT y.B_FIRST FROM BOR Y WHERE Y.LKEY = b1.LKEY AND Y.WHICHBORR = 1)

and


max(SELECT x.B_FIRST FROM BOR x WHERE x.LKEY = b1.LKEY AND x.WHICHBORR = 2)

Hope this helps,
Johan

JdVries :netherlands: (BOB member since 2006-02-06)

#7

I agree with Johan…

Rajx72:
With Johan’s earlier post you might be thinking that you have to create a FH SQL report…? No… I assume he is suggesting you that BO generated SQL should look like his.

Create the universe objects using MAX() function around the database columns… you will get single value…

BO_Chief :us: (BOB member since 2004-06-06)

#8

HI

Thanks for the reply

but can you tell me where is B1 table in the query


(SELECT max(y.B_FIRST) FROM BOR Y WHERE Y.LKEY = b1.LKEY AND Y.WHICHBORR = 1)

I mean how to define the B1 table. B1 is also pointing to Bor table. How do i define it.

Thanks

rajx72 (BOB member since 2006-08-02)

#9 

Fborr = max(case when table.WHICHBORR = 1 then table.bfirst End)

Use the above code, and use your tablename where is used…

BO_Chief :us: (BOB member since 2004-06-06)

#10

HI

Thanks. It worked for me.

But can you explain one thing. Why did you use Max function in it.

rajx72 (BOB member since 2006-08-02)

#11

HI

I tried without Max and got the answer.

If i dont use max() it will give me 2 records. wherein I want one.

thanks

rajx72 (BOB member since 2006-08-02)

#12

Raj:

Yes, In order to avoid two records we use max…

Glad It worked…

BO_Chief :us: (BOB member since 2004-06-06)