Accurately positioned Custom Comments in Column Charts

Many users/developers want to see/display custom comments in a Column chart. Not just displaying custom comments, accurately positioning them is a challenge as well. I’ve seen people use workarounds which are GREAT, but users/developers end up compromising (on few things) due to limitations in Web Intelligence.

Let’s consider the below scenario of a Column Chart with Company Names and their Revenue in $B (dummy numbers over a period of time), plus the Custom Comments to be shown on top of each bar with Traded As names (of those companies on NASDAQ/NYSE), their Stock Price (at a point in time) and also conditional formatting of those custom comments depending on the Revenue (in $B).

Let’s see how to do it.
Below is the data I used to develop this report.

Step 1: Create the below variables
v_MaxRev =Max([Revenue]) In Block
(To find out the maximum Revenue out of all Companies)
v_PctofMaxRev =[Revenue] / [v_MaxRev]
(To find what Percent is the Revenue of a Company when compared to the maximum Revenue out of all the Companies)
v_TradedAsandStockPrice =[Traded As]+” – $”+[Stock Price]
(To concatenate Traded As name and Stock Price of a Company, which we want to show as Custom Comments)
v_LinestoSkip (Main variable of this trick, to skip number of lines (or move down) depending on the Revenue (measure) value)

Update as of 08/02/2016 10:22 am EST:
Thanks to Mark Prosser for giving a tip to add an alternate (below) short formula for v_LinestoSkip. Please feel free to use whichever one (or both if) you want to.

Alternate (and much easier) formula:
=Ceil((1-[v_PctofMaxRev])/0.025)

Original formula: (update ends here)
=If([v_PctofMaxRev] <= 0.0249) Then 40
ElseIf([v_PctofMaxRev] Between (0.025;0.0499)) Then 39
ElseIf([v_PctofMaxRev] Between (0.05;0.0749)) Then 38
ElseIf([v_PctofMaxRev] Between (0.075;0.0999)) Then 37
ElseIf([v_PctofMaxRev] Between (0.1;0.1249)) Then 36
ElseIf([v_PctofMaxRev] Between (0.125;0.1499)) Then 35
ElseIf([v_PctofMaxRev] Between (0.15;0.1749)) Then 34
ElseIf([v_PctofMaxRev] Between (0.175;0.1999)) Then 33
ElseIf([v_PctofMaxRev] Between (0.2;0.2249)) Then 32
ElseIf([v_PctofMaxRev] Between (0.225;0.2499)) Then 31
ElseIf([v_PctofMaxRev] Between (0.25;0.2749)) Then 30
ElseIf([v_PctofMaxRev] Between (0.275;0.2999)) Then 29
ElseIf([v_PctofMaxRev] Between (0.3;0.3249)) Then 28
ElseIf([v_PctofMaxRev] Between (0.325;0.3499)) Then 27
ElseIf([v_PctofMaxRev] Between (0.35;0.3749)) Then 26
ElseIf([v_PctofMaxRev] Between (0.375;0.3999)) Then 25
ElseIf([v_PctofMaxRev] Between (0.4;0.4249)) Then 24
ElseIf([v_PctofMaxRev] Between (0.425;0.4499)) Then 23
ElseIf([v_PctofMaxRev] Between (0.45;0.4749)) Then 22
ElseIf([v_PctofMaxRev] Between (0.475;0.4999)) Then 21
ElseIf([v_PctofMaxRev] Between (0.5;0.5249)) Then 20
ElseIf([v_PctofMaxRev] Between (0.525;0.5499)) Then 19
ElseIf([v_PctofMaxRev] Between (0.55;0.5749)) Then 18
ElseIf([v_PctofMaxRev] Between (0.575;0.5999)) Then 17
ElseIf([v_PctofMaxRev] Between (0.6;0.6249)) Then 16
ElseIf([v_PctofMaxRev] Between (0.625;0.6499)) Then 15
ElseIf([v_PctofMaxRev] Between (0.65;0.6749)) Then 14
ElseIf([v_PctofMaxRev] Between (0.675;0.6999)) Then 13
ElseIf([v_PctofMaxRev] Between (0.7;0.7249)) Then 12
ElseIf([v_PctofMaxRev] Between (0.725;0.7499)) Then 11
ElseIf([v_PctofMaxRev] Between (0.75;0.7749)) Then 10
ElseIf([v_PctofMaxRev] Between (0.775;0.7999)) Then 9
ElseIf([v_PctofMaxRev] Between (0.8;0.8249)) Then 8
ElseIf([v_PctofMaxRev] Between (0.825;0.8499)) Then 7
ElseIf([v_PctofMaxRev] Between (0.85;0.8749)) Then 6
ElseIf([v_PctofMaxRev] Between (0.875;0.8999)) Then 5
ElseIf([v_PctofMaxRev] Between (0.9;0.9249)) Then 4
ElseIf([v_PctofMaxRev] Between (0.925;0.9499)) Then 3
ElseIf([v_PctofMaxRev] Between (0.95;0.9749)) Then 2
ElseIf([v_PctofMaxRev] >= 0.975) Then 1

v_PositionedCustomComments =RightPad(“”;[v_LinestoSkip];Char(13))+[v_TradedAsandStockPrice]
(To show Custom Comments in their accurate position)

Step 2:

• Insert a Vertical table on top left of the report, with just 2 rows and update Format Table for the below options
  • Format Table -> General -> Update Name as Custom Comments Table
  • Format Table -> Layout -> Relative Position -> Horizontal as 0.2 cm from Left of Report and Vertical as 0.2 cm from Top of Report.
  • Format Table -> Appearance -> In Alternate Color, update Frequency as 0 and Color as White
  • Click Apply and OK
• Insert Company Name in first row and v_PositionedCustomComments in 2nd row as shown below.

• Select all 4 cells in the table and go to Format Cell to update below options
  • Format Cell -> General -> check Autofit Height
  • Format Cell -> Alignment -> Update Horizontal as Center and Vertical as Top (Note: This is very important.)
  • Format Cell -> Alignment -> Update Padding as 0 cm for Top, Bottom, Left and Right.
  • Format Cell -> Font -> Update Font size as 7
    
  • Format Cell -> Border -> Remove all borders
  • Click Apply and OK.
• Select the 2 cells Body Cells (not header) and right click on them to go to Format Cell
  • Format Cell -> set Width as 2.12 cm
  • Click Apply and OK. This is how the table will look with data.

• Select the 2 Header cells and right click on them to go to Format Cell
  • Format Cell -> Appearance -> under Background Image, update Color to No Color and Pattern to None.
  • Click Apply and OK.
• Right Click on one of the company names from this vertical table and go to Hide -> click Hide Dimension as we want to hide the Company Name. This is how the table looks now.

Step 3:

• Create a Column Chart with Company Name on Category Axis and Revenue on Value Axis 1 as shown below.

• Go to Format Chart and update below options:
  • General -> Width = 22 cm and Height = 13.25 cm
  • General -> Data Values -> Check on Data Displaying Label Mode, update Data Postion = Inside, change Font size to 7, Font color to White and Orientation as Horizontal
  • General -> Background -> Update Background Color’s Opacity to be 0%, so it becomes transparent
  • Legend -> Design -> Update Layout -> Location as Bottom
  • Legend -> Title -> Uncheck Visible
  • Category Axis -> Title -> Uncheck Visible
  • Value Axis -> Title -> Uncheck Visible
  • Value Axis -> Scaling -> Update Maximum Value to Fixed Value and use formula =[v_MaxRev]
  • Plot Area -> Background -> Update Category Axis Grid Color Opacity to 100%, so it becomes transparent.
  • Click Apply and OK.

We’ve created the Table and the Chart, now we need to align/place them in a way, they look like just ONE chart.

• Right Click on the Column Chart, from Order -> select Bring to Front.
• Go back to Format Chart and update these options
  • Format Chart -> Global -> Layout -> Relative Position -> set Horizontal as 1.35 cm from Left edge of Custom Comments Table and Vertical as 1 cm from Top edge of Custom Comments Table.
  • Click Apply and OK. This is how the Chart looks now.

Step 4:

• Create the required Title and Subtitle as needed.
• Create a conditional formatting Rule as below:
  • If [Revenue] Greater or equal to 75, then Font color should be Green
  • If [Revenue] Between 25 and 74.99, then Font color should be Blue
  • If [Revenue] Less or equal to 25, then Font color should be Red

• Save this rule and apply on the Column in Custom Comments Table.

Voila, we did it. This is what the result looks like.

The result of this method may be 95% of what users/developers want. Rest 5% is just adjusting the Chart width and Column width (that shows custom comments) manually by looking at it.

Note:
This trick will surely help until the below BI Ideas are delivered as feature(s). I request all of you who checkout this document, please checkout and vote up on the below ideas.

comments in charts : View Idea
Webi Chart with Data Table : View Idea

Also, this workaround can be used with Combined Column, Stacked Column, Bar, Line… and few other types of Charts available.

Update as of 05/04/2016 10:52 EST: Attaching the .txt file of the webi document, download it, rename it by removing .txt and/or .zip from the name.

Enjoy and please let me know your feedback.