Skip to Content

We all know that implementation of hierarchy is easy with Bex Queries but what about using universe as source. This blog would help you to construct a hierarchy easily in BO using universe.

Will explain you in a step by step procedure.

Pull in all the dimensions and measure to the report that you need in hierarchy. Here I have taken Year ,State City,Storename and three measures.

Pic1.jpg

Click on each dimension and apply break.so if there are 4 levels in your report then apply break only for first 3 levels. As shown below break has been applied for Year, State, City. Also in break options check break header so that we can see 4 headers for this table including the table header.

/wp-content/uploads/2014/07/pic2_494433.jpg

  On The store name column headers place year, State, City dimensions as highlighted below.

/wp-content/uploads/2014/07/pic3_494434.jpg

Also place Measure values next to dimensions column as highlighted below.

/wp-content/uploads/2014/07/pic4_494438.jpg

Hide the dimensions on which ever break is applied. We would get a table as below. We could see “California” text moved a bit. It is due to spacing applied. So we can adjust the spacing and show them as hierarchy.

/wp-content/uploads/2014/07/pic5_494439.jpg

Click on the table and go to analysis pane and click outline. The table will be highlighted as below. This feature helps us to fold and unfold the table. The numbers on the left end below helps us to fold the table .On clicking on each number we can fold it’s below level. Ie On clicking on 3 “e-fashion los Angeles “will be folded.

/wp-content/uploads/2014/07/pic6_494440.jpg

Below screenshot shows the table being folded at first level. Not only on the numbers below we can also use the arrows seen near to the text to use for folding and unfolding.

/wp-content/uploads/2014/07/pic7_494444.jpg

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Jose Ig Mendez

    Hi Janani,

    When you say “Also place Measure values next to dimensions column as highlighted below.” ?


    Waht do you mena ? Which measures are you adding ?


    I’m trying to follow your steps (with another data) I cannot figure out what to do.


    I only have one measure column (image attached, 5 columns table).


    My hierarchy is made of “year”, “month” and “level”. (3 breaks)

    Do I have to create a new measure for each “column” and then place them the way you say ?


    I have place de SUM of my measure column but when I hide the “break dimensions” I don’t get the table you say in your explanation. (image attached, 3 columns table).

    Thanx in advance

    P.S. I’m facing this issue with  BO4.1 SP5

    Regards

    H_example_BO.GIF

    H_example_BO_2.GIF

    (0) 
    1. Janani Krishna Dass Post author

      Hi,

      “Also place Measure values next to dimensions column as highlighted below.” this means place the value field ie (=[Measure]) in the adjacent column.

      In picture 3 i have placed the dimensions ie [Dimension] and not the

      (=Name of [Dimension]) on the last column. so i get year as 2004 (have a look at the pic 3)

      Next in pic 4 adjacent to 2004 you could see some amount figures. There I have placed

      (=[Sales revenue]) measure field.

      Hope you got me.Please do ask if you need further clarifications

      Thanks,

      Janani

      (0) 
      1. Jose Ig Mendez

        Hi Janani,

        unfortunately I guess I havent understood your comments yet.

        Let me explain again what I’ve done, trying to ffollow your steps.

        I created a table with 5 colums; year (Ejercicio), Month (Mes Ejercicio), Level (Profundidad var), Account-key (Cuenta Contable – Clave), Amount (PO Signo).

        I make 3 breaks for the 3 first columns (red arrows) and I place at the fourth column the 3 first dimesions at the header of the table (green arrows), at the 5th column I write at the header the same dimension, the amount dimension (the 5th column) and I get this

        [image: 00_Hier_1Red_2Green.png]

        00_Hier_1Red_2Green.png

        If I hide the breaks headers I get this

        [image: 01_Hier_1Red_2Green_NO HEAD.png]

        01_Hier_1Red_2Green_NO HEAD.png



        If I hide the dimensions and at the last column I get (“multiple value” message, because there more than one value for that row, I can avoid the warning message change the value of the column for “=SUM([measure])” I get this

        [image: 02_Hier_1Red_2Green_HidDim.png]

        02_Hier_1Red_2Green_hidDim.png




        If after, I hide the breaks headers, I get this:

        [image: 02_1_Hier_1Red_2Green_noheader.png]

        02_1Hier_1Red_2Green_hidDim_noheader.png



        Where am I doing wrong ?, I dont get what I was expecting, I dont get the same table as you did.

        Thanx in advance.

        (0) 

Leave a Reply