Skip to Content
Author's profile photo Rakesh Pattani

BOBJ Trick To Display Table Data In Hierarchy Format

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.

Pic1.png

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

Pic2.jpg

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

Pic3.jpg

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

Pic4.jpg

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

Pic5.jpg

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.

Pic6.jpg

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

Pic7.jpg

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.

Pic8.jpg

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.

Pic9.jpg

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

Pic10.jpg

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

Pic11.jpg

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

Pic12.jpg

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

Pic14.jpg

Assigned Tags

      46 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      great stuff Rakesh πŸ™‚

      Author's profile photo Former Member
      Former Member

      Good One Rakesh

      Author's profile photo Former Member
      Former Member

      Appreciations Rakesh πŸ™‚

      Author's profile photo Vinay Kumar
      Vinay Kumar

      Rakesh Cool 😎

      Author's profile photo SrinivasuluReddy T
      SrinivasuluReddy T

      Good Stuff

      Thanks

      Author's profile photo Tanveer Ahmed Mohammed
      Tanveer Ahmed Mohammed

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

      Author's profile photo Former Member
      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

      Author's profile photo Tanveer Ahmed Mohammed
      Tanveer Ahmed Mohammed

      Vasey,

      Its working fine using Outline functionality. Thanks

      Author's profile photo Former Member
      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?

      Author's profile photo Rakesh Pattani
      Rakesh Pattani
      Blog Post Author

      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.

      Author's profile photo Former Member
      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.

      Author's profile photo Former Member
      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.

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Former Member
      Former Member

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

      Author's profile photo Colleen Allison
      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?

      Author's profile photo Former Member
      Former Member

      Good Stuff

      Author's profile photo Former Member
      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!!

      Author's profile photo Former Member
      Former Member

      Nice!

      Author's profile photo Pushkar Dhale
      Pushkar Dhale

      Really Good One .... Keep It Up..... πŸ™‚

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great Webi trick !

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

      William

      Author's profile photo VENKATA SATYA PRATTI
      VENKATA SATYA PRATTI

      Thanks Rakesh, Good and simple trick.

      Venkat

      Author's profile photo Former Member
      Former Member

      Helpful information.:) πŸ™‚

      Author's profile photo Former Member
      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?

      Author's profile photo Wasem Hassan
      Wasem Hassan

      its very good doc

      Thanks for sharing πŸ™‚ πŸ™‚

      Regards

      Waseem

      Author's profile photo Former Member
      Former Member

      Nice variation of the original idea from here:

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

      Author's profile photo Former Member
      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.

      Error.png

      Author's profile photo JEET PANDEY
      JEET PANDEY

      Hi All,

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

      Thanks in Advance.

      Regards,

      Jeet

      Author's profile photo Anila Dhharisi
      Anila Dhharisi

      Hi,

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

      Grtz

      -Anila.

      Author's profile photo Former Member
      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.

      Author's profile photo Anila Dhharisi
      Anila Dhharisi

      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.

      Author's profile photo Former Member
      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 ??

      Author's profile photo Anila Dhharisi
      Anila Dhharisi

      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.

      Author's profile photo Former Member
      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.

      Author's profile photo Ramla None
      Ramla None

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

      Author's profile photo DONGA THRINAD
      DONGA THRINAD

      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.

      Author's profile photo Jean Cristian Ferreira Machado
      Jean Cristian Ferreira Machado

      Great!

      Author's profile photo Former Member
      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!

      Sample.jpgRam

      Author's profile photo DONGA THRINAD
      DONGA THRINAD

      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.

      Author's profile photo Former Member
      Former Member

      Good Stuff. I tried and it works great.

      Author's profile photo Former Member
      Former Member

      Good Trick. Thanks for sharing.

      Regards,

      Sippy

      Author's profile photo Former Member
      Former Member

      Good Trick.

      it works great.


      Regards

      Yasemin

      Author's profile photo Former Member
      Former Member

      Good One Rakesh..!!

      Thanks for sharing tricks

      Regards,

      Sushil Padhye

      Author's profile photo Former Member
      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

      Author's profile photo Former Member
      Former Member

      Hello,

      For me its working...!!

      I did the same way Rakesh explained.

      Regards,

      Sushil Padhye

      Author's profile photo OMPRASAD SARANGI
      OMPRASAD SARANGI

      Helpful....

      Author's profile photo sivanagaraju gadiparthi
      sivanagaraju gadiparthi

      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!