Increase the width of the "Total" Text field automatically in a SSRS/BIDS report?

Question

Hi I am designing a sales report using Business Intelligence Development studio. I have a table that shows the sales quote line items - such as product ID, Unit price, Quantity and line total and at the bottom of this table, I display the totals. The totals are displayed in another table just below as shown before. 

Now, sometimes, I have larger values - and I want the width of the columns (especially the Totals) to increase automatically to the left (and reducing the adjacent column say "product description" column proportionately such that the total width of the table remains the same). For all columns in the table, I set the "Can Increase to Yes and Can Shrink to No". {should i set Yes to Can Shrink} 

I notice that the total column in the report doesn't increase it's width when the numbers are too large and the digits are displayed in the next row!! 

The Results display like below 

Any help is highly appreciated. Thank you 

Answer 1

Hi CRM,

Reporting Service does not allow you to dynamically adjust the width of the textbox, only option you can control is "CanGrow" and "CanShrink" only affect the vertical size.

In order to fix the data in the same cell, you can dynamically adjust the "Font Size" based on the expression passed.(Reduced the Font size if more number of digits present). 

Regards

Harsh

Answer 2

Thanks Harsh. I appreciate it. Could you point me some examples of how I can change the font size - is there a sample code i can refer to? or could I be able to configure in the text box property? 

Answer 3

Hi,

Yes it text box property and press F4 to open the properties window, Under Font you will find the FontSize, set it based on expression.

For Example :FontSize =IIF(<<Value>> < 9999.99, 10, 8) etc

Regards

Harsh

---

Regards Harsh

Answer 4

Hi Harsha, Thanks but I need clarification.  the <<value>> refer to the font size or the width of the text box?

Current Settings for the text box (table): 1 inch; Cambria font size 10; the size of the text box is 1 inch.

I could hardcode something like this: 

=IIF(len(Fields!totalamountValue)>10,"8Pt","10pt"). But what if, after changing to 8pt, it still exceeds the width of the text box? 

Could you elaborate on this? Thank you 

Answer 5

Hi,

<<Value>> refers to field value coming from dataset , Your example use the expression prefectly, Since the ssrs does not allow to write expression in width column (Therefore you can't set it dynamically), we can try the other hacks like settting the font size,changing the font  or setting the font weight based on the value coming in the field.

Otherwise you have to live with CanGrow and CanShrink options, but it only allow textbox to grow vertically.

Regards

Harsh

---

Regards Harsh