Implementing Hierarchy in BO
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.
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.
On The store name column headers place year, State, City dimensions as highlighted below.
Also place Measure values next to dimensions column as highlighted below.
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.
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.
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.
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
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
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]
If I hide the breaks headers I get this
[image: 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]
If after, I hide the breaks headers, I get this:
[image: 02_1_Hier_1Red_2Green_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.