Relative positioning of a block w.r.t 2 (or more) blocks OR Container in Webi

Many of us might have come across a requirement in Webi, where we needed to relatively position a block with respect to, not One, but Two (or more) other blocks. We can also say that we needed a Container (component of Dashboard (Xcelsius)) in Webi.

Check out the below image to understand the requirement using 2 different scenarios:

We need to develop logic, using which, Block 3 will be vertically positioned to be right below either Block 1 or Block 2, depending on whichever one has more data, so there is no overlap between the blocks on top and bottom. Just so you know, the current relative positioning feature can only position a block with respect to 1 block only (when setting position from left or position from top).

Let’s check out this cool logic to address the requirement: (Note: This logic works 90% of the times (or more, but not 100%, I’ll explain the reason behind it, later.)

Step 1:

• Create a simple Report with Excel as data source (Data I used is attached, with just few columns from the Employee table (Xtreme Sample Database), in addition to fake data column Achievements.)
• After pulling data in the report, create a Section on Name, remove the Section Title cell and generate the report structure as below.

Report Structure Details: Separate Horizontal tables used to show each row (Name, Position, Hire Data, Salary, and Achievements), to address the scenario, what if one of the Employees doesn’t have an Achievement? In that case, we need to hide it and that will affect the relative positioning of Block 3.

• • Employee Details header is a Free Standing Cell with properties:
    • Cell Width 5.5 cm and Height 0.45 cm
      
    • Font Arial, Size 8, Style Bold, Aligned Left
    • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right
    • No Borders
    • Layout -> Relative Position -> Horizontal: 0.5 cm from Left of Section and Vertical: 0.25 from Top of Section
      
  • Notes header is a Free Standing Cell with properties:
    • Cell Width 8 cm and Height 0.45 cm
    • Font Arial, Size 8, Style Bold, Aligned Left
    • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right
    • No Borders
    • Layout -> Relative Position -> Horizontal: 0.25 cm from Right of Employee Details block and Vertical: 0 cm from Right of Employee Details block
  • Name, Position, Hire Date, and Salary blocks are Horizontal tables as mentioned above, with Properties
    • Header Cell
      • Cell width 1.5 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
      • Font Arial, Size 7, Style Regular, Aligned Left
      • Alignment -> Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
      • No Borders
    • Body Cell
      • Cell width 4 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
      • Font Arial, Size 7, Style Regular, Aligned Right
      • Alignment Right and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
      • No Borders
    • Layout -> Relative Position: Block for Name is vertically positioned to be 0.1 cm below Employee Details header cell and horizontally positioned to be 0 cm from the left of Employee Details header cell. Blocks for Position, Hire Date, and Salary are vertically positioned to be 0 cm below the block above and horizontally positioned to be 0 cm from left of Employee Details header cell.
  • Achievements block is also a Horizontal table, with Properties
    • Header Cell
      • Cell width 1.5 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
      • Font Arial, Size 7, Style Regular
      • Aligned Left and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
      • No Borders
    • Body Cell
      • Cell width 4 cm and Height 0.4 cm (Note: Height 0.4 cm is very important)
      • Font Arial, Size 7, Style Regular
      • Alignment Right and Padding 0.05 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.05 cm is very important)
      • No Borders
    • General -> Display -> check the box for Hide when following formula is true, write formula as =Isnull([Achievements])=1, click Apply and OK, as we want to hide the this block if there is no data or if an Employee doesn’t have an Achievement. (Note: This is very important)
  • Notes text is a Vertical table, with properties
    • Table Header is hidden
    • Cell width 8 cm and Auto Height is checked and minimum Height 0.4 cm (Note: ***Width 8 cm is very important, you’ll see in Step 2, why.)
    • Font Arial, Size 7, Style Regular
    • Alignment Horizontal Top and Vertical Left, Padding 0.05 cm each on on Top, Bottom, Left, and Right, and Display as Wrap Text (Note: Wrap Text and Padding 0.05 cm is very important)
    • No Borders

Step 2:

The first step in the idea behind vertically positioning Block 3 dynamically below Block 1 or Block 3, is to find out the number of lines of data we are showing in each of the Blocks 1, 2. (Lines of data is an important term, please keep that in mind)

Finding Lines of Data is easy for Block 1, which is Employee Details, we can create a variable as below using the formula

• v_Lines in Employee Details = 4 + (If(IsNull([Achievements])=1) Then 0 Else 1)

Here, 4 specifies the 4 lines of data (Name, Position, Hire Date and Salary) which is shown for all Employees. The next part of formula is to add 1 to that 4 (if an Employee has any Achievement), or add 0 (if an Employee has no Achievements), as we won’t be displaying that block.
The fun begins here: Now, let’s try to calculate the height of lines of data in Employee Details (height of data). What do I mean by that? Checkout the below pic:

From the pic, we see that the height of blank cell with char(13) padded 3 times, is almost (if not exactly) equal to the 2 lines of data in Employee details (Name and Position), which is great, and can be used as a scale in the logic. So, for every 2 lines of data in Employee Details, we want char(13) padded 3 times, and for an additional line of data, we need char(13) padded 2 times. Make sense? Lets create a variable with generic formula which works for any number of lines in Employee Details.

• v_Height of Employee Details =If(Mod([v_Lines in Employee Details];2)=1) Then ((Floor([v_Lines in Employee Details]/2)*3)+2) ElseIf(Mod([v_Lines in Employee Details];2)=0) Then (([v_Lines in Employee Details]/2)*3)

(Height of Data/whatever block is an important term, please keep that in mind)

Finding the Lines of Data of Block 2, which is text for Notes, is tricky, because this block has data coming from one row of one column (unlike Block 1). As we know that the text is wrapped, we need to make an assumption by calculation, how many rows is the data going to be displayed in after Wrap Text? Look at the below image:

To our eyes, we see that its 8 lines, but how can we calculate that, this is where the importance of Width 8 cm comes into play (font size, padding are equally important). After wrapping the text, count the number of characters in each line, after that, I came to a conclusion that 63 characters per line might be a good idea, then create the below variable to get the approximate number of lines in notes after wrap text. We get that number by ceil of number we get by dividing the length of Notes by 63. (Note: This is why I said, this logic works 90% of the times (and not 100%). I dare say, this is never going to be perfect (except if you’re lucky)). Because, we can’t assume how many characters are going to be displayed in a line after text wrap, as it depends on various factors. Ex: If the last word in a line is lengthy, it may go on to the next line and the number of characters in that line may be far less than our assumption, as you see in the image that words like International (line 2), company (line 4), Seattle (line 7), and Association (line 8) are moved to next lines. That is why, I did few tests and found 63 to be a good idea in this case.

• v_Approx Number of Lines in Notes after Wrap Text =Ceil(Length([Notes])/63)

Now, lets calculate the height of lines of data in Notes (Height of Notes). This formula for this is a little different that (v_Height of Employee Details), because the lines (data) in wrap text are much closer to each other when compared to Employee Details. So, what I figured is:
If lines of data is 1, then we need char(13) padded 2 times (if n=1, then n+1)
If lines of data is 2, then we need chart(13) padded 3 times
If lines of data is 3, then we need chart(13) padded 4 times
so on until
If lines of data is 9, then we need chart(13) padded 10 times and
If lines of data is 10 (or more), then we need chart(13) padded 12 times (if n=10, then n+2)
and so on (hopefully, we won’t have Notes going over 15 lines).
Using that idea, I created the variable for Height of Notes (variable is names Approx as its never going to be perfect)

v_Approx Height of Notes after Wrap Text  =If([v_Approx Number of Lines in Notes after Wrap Text] Between (1;9)) Then ([v_Approx Number of Lines in Notes after Wrap Text]+1) Else ([v_Approx Number of Lines in Notes after Wrap Text]+2)

Step 3:

Why did we go through all the hassle of steps 1 & 2? To create only ONE background cell or Container (Container as in dashboards) for both Employee Details and Notes, so we can relatively position Block 3 using that background cell or container.

(Background cell, Container are important terms, please keep them in mind)

As I said in the beginning, that the idea is to find which block (1 or 2) has more data, so we can position block 3 depending on that, as we have the heights of data in both the blocks, let’s create a variable to get the greater one as below (which will be the height of background cell or container):

• v_Height of Background Cell for Employee Details and Notes

=If([v_Height of Employee Details] > [v_Approx Height of Notes after Wrap Text]) Then [v_Height of Employee Details]
ElseIf([v_Approx Height of Notes after Wrap Text] > [v_Height of Employee Details]) Then [v_Approx Height of Notes after Wrap Text]
ElseIf([v_Approx Height of Notes after Wrap Text] = [v_Height of Employee Details]) Then [v_Height of Employee Details]

Step 4:

Let’s use a Free Standing Cell to create a background cell or container, with properties as below:

• Cell Width 14.75 cm and Auto Height and minimum Height of 0.13 cm (Note: Checking Auto Height box is most important, change width to 0.1 cm when report creation is done)
• Font Arial, Size 6, Style Bold, Aligned Left (Note: Font Size 6 is very important)
• Alignment -> Padding 0.00 cm each on on Top, Bottom, Left, and Right (Note: Padding 0.00 cm is very important)
• No Borders
• Appearance: Make the background color as light grey (Note: change the background color to White when report creation is done)
  
• Layout -> Relative Position -> Horizontal: 0.0 cm from Left of Section and Vertical: 0.00 cm from top of Name (horizontal table) (Note: 0.00 cm from top of horizontal table for Name is very important)

And use the below variable in the background cell

• v_Formula for Background Cell for Employee Details and Notes =RightPad(“”;[v_Height of Background Cell for Employee Details and Notes];Char(13))

As it’s called the background cell, lets send it to back by, right click on it -> Order -> Send to back. This is how the report looks now.

Step 5:

Create a dummy table to be used as block 3, as in the image (the text in it is a free hand text) and set it relative position as, horizontal position to be 0.00 cm from left of Employee Headers table and vertical position to be 0.15 cm below the Background cell we created in Step 4.

I’ve removed the borders of all the blocks, background color of the background cell and this is how the report looks now.

We’ve 90% successfully positioned a block with respect to 2 different blocks or created a Container in Webi. This logic can be used to relatively position a block with respect to more than 2 blocks as well. Just so you know, the scenario (requirement) that gave me an inspiration to create this logic is much more complex, and has few other cool things, will share them sometime later or if someone specifically requests. Please let me know of any typos and/or other corrections to improve this blog post.

Hope you have enjoyed this blog and please let me know your comments and questions.