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

[Former Member]

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

[Henry_Banks · Accepted solution]

How about just merging 2 columns, or formatting the alignment so that they "look" concatenated?

regards,

H

[Former Member · Accepted solution]

Hi Henry,

I tried/want to get the parent node in separate column using another object. How do i get it? help me.

BI team cant able to provide separate object. Please give your instructions.

Please note: Parent node is G/L Code(or Description) and Child nodes are Cost centers.

Thanks

[jyothirmayee_s · Accepted solution]

Hi,

Did you try..

if ([G/L Code].depth = 0) then

[G/L Code]

** you will get parent of [G/L code]

Depending on it you can concatenate the values..

There are few functions to play around on heirarchies.

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4_ffc_en.pdf

I did tried but did not get 100% result though. if you find answer please post back.

Thanks,

Jothi

[Former Member · Accepted solution]

Hi Jothi,

Thanks a lot . i got the parent node text at its level but i need the parent node text for its children.

Used the formula '=If([CostGL Hierarchy].Depth=4) Then([CostGL Hierarchy].Depth=3)' but it returns 0 instead of the text. i'm confused

Using your formula, i 'm showing in separate column like:

                                                                                      JAN REVENUE     FEB REVENUE

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

[BW_Hierarchy]    Formula                [BW_Hierarchy].Text

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

Apple                   Apple

-North Region                                        NORTH                       102030               203040

-South Region                                       SOUTH                       112233               223344

-East Region                                         EAST                          123456               654321

-West Region                                        WEST                         111111               222222

After the formula '=If([CostGL Hierarchy].Depth=4) Then([CostGL Hierarchy].Depth=3)', it ilooks like:

                                                                                        JAN REVENUE     FEB REVENUE

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

[BW_Hierarchy]    Formula                [BW_Hierarchy].Text

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

Apple                   Apple

-North Region         0 NORTH                      NORTH                    102030               203040

-South Region        0 SOUTH                      SOUTH                    112233               223344

-East Region          0 EAST                         EAST                       123456               654321

-West Region         0 WEST                        WEST                      111111               222222

Atleast i should show like:

                                                                                      JAN REVENUE     FEB REVENUE

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

[BW_Hierarchy]    Formula                [BW_Hierarchy].Text

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

Apple                   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

Why am i getting 0 instead of parent node? help me

Thanks,

Parthiban.

[jyothirmayee_s · Accepted solution]

Hi,

I know this is tricky and even i'm looking for a similar solution. Tried on almost all the functions in the document .. except errors i'm not getting desired result.

I'm still trying to figure the available functions under "Set" in formula editor of WebI.

Please share if you find a solution.

Thanks,

Jothi

[Former Member · Accepted solution]

Hi Parthiban/Jothi ,

Were you able to acheive this....I'm kind of a stuck with the same requiremnt..Please let me know if you were able to find a solution...

Thanks,

Anil Zacharia Chacko.

[Former Member · Accepted solution]

Hi Anil,

Sorry, i didn't achieve this. Instead of using BW Hierarchy/BEx Structure, i have created Separate objects using ABAP Routines.

Example:

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

Fruits     Region

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

Apple     North

Apple     South

Apple     East

Apple     West

Thanks,

Parthiban

[Former Member]

Hi All,

Is it possible to concatenate Parent node with Children?

Thanks,

Parthiban

[Former Member]

Hi All, Can any1 explain why i'm getting 0 when i used the formula: '=If([CostGL Hierarchy].Depth=4) Then([CostGL Hierarchy].Depth=3)' as mentioned above.

Thanks in Advance

[Former Member]

[CostGL Hierarchy].Depth will return the depth of the current level (when the formula is included in a block with the [CostGL Hierarchy] object. It returns an integer.

So the formula [CostGL Hierarchy].Depth=3 is actually a boolean. You're saying does the depth equal 3. Webi gives a 0 for no and a 1 for yes. When the depth is 4, it isn't 3. Hence your zeros.

[Former Member]

Hi David,

Thanks a lot

Also, Please help me to get the nodes at third level. i need to display in the fourth level as mentioned above.

-Parthiban