Skip to Content
0
Former Member
Jan 07, 2013 at 11:05 AM

Want to Concatenate Parent Node's Text in Leaves of Row Header using Common Formula.

70 Views

Hi All 😀

I'm using BO 4.0 SP04 Patch 6 Version. Created my Webi Report using BICS Connection.

I have Crosstab which has BW_Hierarchy as one of the Row Header, and its Text as another Row Header and Month as Column Header and Revenue in the Body.

Now, my Requirement is to concatenate the Parent Node of BW_Hierarchy with Text of BW_Hierarchy.

For Example:

Source looks like:

JAN REVENUE FEB REVENUE

------------------------- -------------------------

[BW_Hierarchy] [BW_Hierarchy].Text

------------------------ ------------------------------

Apple

-North Region NORTH 102030 203040

-South Region SOUTH 112233 223344

-East Region EAST 123456 654321

-West Region WEST 111111 222222

Orange

-North Region NORTH 112233 223344

-South Region SOUTH 111111 222222

-East Region EAST 102030 203040

-West Region WEST 123456 654321

Expected Result is:

JAN REVENUE FEB REVENUE

------------------------- -------------------------

[BW_Hierarchy] Concatenated[BW_Hierarchy].Text

----------------------- ------------------------------------

Apple

-North Region Apple - NORTH 102030 203040

-South Region Apple - SOUTH 112233 223344

-East Region Apple - EAST 123456 654321

-West Region Apple - WEST 111111 222222

Orange

-North Region Orange - NORTH 112233 223344

-South Region Orange - SOUTH 111111 222222

-East Region Orange - EAST 102030 203040

-West Region Orange - WEST 123456 654321

I tried with Ancestor, [BW_Hierarchy].Parent functions. Using these functions, concatenated the Revenue with Text using Sum aggregate function.

If i'm able to convert String(Parent node words) into Number, i will concatenate easily. Here, converting Parent Node text into number using ToNumber function is resulting error. Please suggest me Other ways to achieve this.

Help me. 😊

Thanks in Advance 😀

-Parthiban

Message was edited by: Parthiban Ganesh