BOBJ Trick To Display Table Data In Hierarchy Format

[former_member189638]

This blog shows a small trick on how the table data can be displayed in a hierarchy format. As shown below, the first screenshot is a basic WebI report created on eFasion Universe. The second screenshot shows how the same data is displayed in a hierarchy format.

To achieve this, first drag the State, City, Store name and Revenue into the report.

Now create a Break on State Column with the following settings.

Once the break is created, get rid of the unwanted blank and Total Columns. So, now your table should look like as shown below.

Similarly create a break on the City and delete the unwanted rows as we did it for the State.

Now select the top Column Cell and change the NameOf([Store Name]) to [State], so that it will display the State values instead of the Object name.

Similarly do it for the City and Sales Revenue. Replace the Object name with the Values respectively.

Now the first two columns are unwanted columns too. So we need to get rid of them. We cannot delete them because that will disturb the entire table structure. What we will do is hide them by removing the borders, making the text and background color as white. Also make the width of the Cell to 0.

Inorder to make the values appear like hierarchy, we will put some space before City and Store Name. Also, we will do the formatting of the table by making the background color to white and removing the Bold from text and adding proper table/cell borders.

So, now our table will look like as shown below.

Right click on the table -> Select Table Format and tick the checkbox Show Table Headers.

After adding the table header, we get the required hierarchy structure of the table.

We can also add the background color to distinguish between the different levels.

[manna_das]

great stuff Rakesh :smile:

[Former Member]

Good One Rakesh

[Former Member]

Appreciations Rakesh :smile:

[former_member196240]

Rakesh Cool :cool:

[SrinivasReddy]

Good Stuff

Thanks

[tanveer1]

Good way ...but would have been better it we find out a way to achieve tree hierarchy button to expand and collapse.

[Former Member]

Hi Tanveer,

If you select the Outline functionality
this functionality is possible, its proved very useful over a number of requirements.

Cheers,

Matt

[tanveer1]

Vasey,

Its working fine using Outline functionality. Thanks

[Former Member]

This is almost great!  Last piece of the puzzle for us is how to sort the highest level of the hierarchy (State, highlighted green) in descending order by Sales Revenue (i.e. Texas should be above California).  Any ideas?

[former_member189638]

Did you try selecting the Revenue in the State column and applying the sort on that? I don't have access to system right now..will try and let you know if it works.

[Former Member]

I don't see Revenue under the original State column (hidden).  Do you mean the "State" row we renamed?  If so, that doesn't seem to work.

[Former Member]

Good Stuff

[Former Member]

Yes, the green dollar amounts. You may have to define these as a variable, .e.g. State Revenue to get them to sort.

[Former Member]

Could I get step by step instructions on how to set a "State Revenue" variable? Thanks!

[Former Member]

=Sum([Revenue]) in ([State]) should do the trick Andrew.

[Former Member]

How would one enable a Hierarchy to duplicate the blank cells? I know by default when dragging a Hierarchical object into a WEBI report block, the aggrgation is handled completely different than a standard dimension. Great doc!!

[Former Member]

Nice!

[pushkar_dhale]

Really Good One .... Keep It Up..... :smile:

[wmarcy]

Great Webi trick !

You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

William

[former_member211127]

Thanks Rakesh, Good and simple trick.

Venkat

[Former Member]

Helpful information.:) :smile:

[Former Member]

Very helpful.

Some doubt, ican change the setting for state and city is ok..

But when i comes to sales revenue i get mutlivalue

why?

[wasem_hassan]

its very good doc

Thanks for sharing :smile: :smile:

Regards

Waseem

[Former Member]

Nice variation of the original idea from here:

http://www.perpendulum.com/2011/12/hierarchy-in-one-column/

[Former Member]

I am unable to change the background color ?? I know i got the wrong output. As i am aware of dashboards. I am new to webi. can anyone help with correct steps, please. Thanks.

[former_member196948]

Hi All,

could you please suggest me how i can achieve this approach in "cross table".

Thanks in Advance.

Regards,

Jeet

[former_member211235]

Hi,

Can you share the snapshot of your requirement so that we can help out?

Grtz

-Anila.

[Former Member]

Hi Anila... This is srikanth from Banglore...

If you are aware. can you help me with this Question please ??

Currently we are using BO 6.5 Application. It is windows authenticated.

Directly with there OPD ID only they can open BO Application.

Recently few users are upgraded from windows 7 OS to Windows 8. Now, when they are opening the application it is asking username and password. Please help me. If u r not aware pls ask and help.

[former_member211235]

Hi Srikanth,

As you told about Windows upgradation for specific users, you need to check for Win AD authentication for these users. OPD ID for BO applications prompts the users for credentials only when the authentication doesnt match up. If it is in sync then users can view it as usual.

Hope it helps..!!

Grtz

-Anila.

[Former Member]

Anila thanks for your help.

Thank you very much. But if you know can you tell me how we can check for Win AS authentication for users ??? From my side what i should check in BO 6.5 Supervisor ??

[former_member211235]

Hi,

Enable Windows authentication on this OS and then give a try. Because the users authentication is already set to Win AD itself.

Grtz

-Anila.

[Former Member]

Hello Anila :

That is not working . I am reapeating my Question, can you please check with your friends and reply me. Thanks for your time.

we are using BO 6.5 Application. It is windows authenticated. Users no need to give any username and password.

Now, few users moved to windows 8 OS from windows 7. they are not able to open BO 6.5, can you please help me.

[former_member183161]

Hi Rakesh,

that's really a great stuff, you showed us the out of box work around, which will be very beneficial for the report developers.

regards,

thrinad.

[jean_machado]

Great!

[Former Member]

Hi!

I need some suggestion with respecting to splitting a column of data into multiple columns in the same page. For example, the result should be like shown in the attached screenshot. Do you think it is possible with BO and if yes, how?

Thank you!

Ram

[former_member183161]

hi ram,

in webi drop the object required  times and apply block level filters with in listing the required account numbers for each collumn.

regards,

thrinad.

[Former Member]

Good Stuff. I tried and it works great.

[Former Member]

Good Trick. Thanks for sharing.

Regards,

Sippy

[Former Member]

Good Trick.

it works great.

Regards

Yasemin

[Former Member]

Good One Rakesh..!!

Thanks for sharing tricks

Regards,

Sushil Padhye

[Former Member]

Hi Rakesh,

Nice Blog!!

I was able to replicate the steps and went one step forward by exporting the output of report, to my surprise i didn't get the spacing before city and store name in excel output.

Any suggestion how to achieve this!!

Regards,

Vamsidhar

[Former Member]

Hello,

For me its working...!!

I did the same way Rakesh explained.

Regards,

Sushil Padhye

[colleen_allison]

I tried using a variable, but it didn't work. This is a great tutorial, but the ability to sort the revenue is keeping me from using it. Any other ideas?

[omsarangi]

Helpful....

[Former Member]

Hi,

This is very much helpful for my current project.

Also, I have a scenario, please help me if you can. This is an extension to your solution. Lets say you have a Country, State Name, Branch Region and Employee strength. A state can have single Branch or it can have multiple branches. what if my state name and Branch Region same (only if a state has single branch) then, I want to display the strength at state level and not at Branch level. if those are different then, I should show in Branch level.

 

Please help me with this.

 

Thanks!

[former_member727460]

Hi Jeet, did you achieve this on a crosstab, i have a similar requirement.