Cell Width and Height

[Former Member]

Hello

We are in BI 4.1 sp6.

Is it possible to dynamically change the particular cell width and height by formuals/ cnditional formating?

I understand conditional formatting we can change cell background, font size, but how to handle cell height , cell width.

Regards

Ben

[mhmohammed]

Hi Ben,

Yes, it is possible. Check out the below blog I wrote, go through the whole thing so you understand the concept and idea behind the trick.

In Step 1, variable v_LinesToSkip, I'm calculating the vertical position of the comments dynamically, which can help you calculate the height of the cell. And for the width of the cell, replace with one of two spaces instead of char(13).

Let us know if you've any issue.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob

I checked the blog. I could see the height and width

• Format Cell -> General -> check Autofit Height
• Format Cell -> Alignment -> Update Horizontal as Center and Vertical as Top (Note: This is very important.)

Are you referring to this point

• v_PositionedCustomComments =RightPad("";[v_LinestoSkip];Char(13))+[v_TradedAsandStockPrice]

(To show Custom Comments in their accurate position)

Can you please add more details

Ben

[mhmohammed]

Hi Ben,

Yes, I'm referring to the variable v_PositionedCustomComments, but the dynamic position vertically is calculated by v_LinestoSkip. Makes sense?

You should go through my blog post to see what I did, why I did, and hope you'll get the concept and idea. The idea is to see the value of Revenue and using that determine the height of the bar, and then, depending on the height of the bar, skip number of lines to determine where to display the comments.

The more the Revenue, the higher the bar and we'll need to skip less number of lines, and vice versa.

Off topic: I remember helping you with a report, Multiple Measures in Waterfall Chart, how'd it go?

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob,

Appreciate your support.

Let me rephrase the question. I would like to reduce the height of particular row based on the condition.  I understand the excellent blog which you have written will help to determine the relative height .

Ex.

Row1

Row2

Row3

Row4

Row2 height should be reduced (its not the font size)

Thanks for your support for waterfall chart it worked like charm

Ben

[mhmohammed]

Hi Ben,

There's a little limitation to that workaround because of your requirement, you can't really reduce the height of the cell if it's at default height. You can add cell height dynamically. So, keeping that in mind, you'll have to increase the height of Rows 1,3 & 4 should be increased which will make it seem like Row2 height is reduced. Makes sense? So, you can write a formula for that column as

=If([Column] <> "Row2") Then (Char(13)+[Column]+Char(13))

Else [Column]

Here, Row2 is the value, whose cell height should be reduced. And this formula will add a blank line above and below the actual values for Rows 1,3, & 4.

Let me know if you've any questions. Also, the other thing you might want to reduce is the Alignment, to see what it does, create a table with just one column (any column), right click on it -> Format Cell -> Alignment -> change top and bottom as 0.05cm, and click OK and see what happens.

Also, for the Waterfall Chart, mark the discussion as answered, so people have better visibility about answered or unanswered discussions.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob,

Appreciate your support.

I tested out same. however it doesnt fit what i am looking for.

The report started to have too big for cell.

If there is any other solution please update

Ben

[mhmohammed]

Hi Ben,

I can't think of anything right now, I doubt if a way exists.

Thanks,

Mohammed

[mhmohammed]

Hi Ben,

As I say, "There is always a manual way of doing things," how about multiple tables? Per your example about Rows 1, 2, 3, & 4. How about creating one table for Row 1, one table for Row 2 and one table for Rows 3, & 4. You'd reduce the alignment (top and bottom in format cell) and set it to auto height, so the cell height appears a little less that the other rows.

Thanks,

Mahboob Mohammed