Auto Fit Contents in Excel output with RTF template in BIP
Auto Fit Contents in Excel output with RTF template in BIP

 877 total views

In this article we will look into how to set the auto-width for a column in Excel output using RTF template in BI Publisher.

Want to learn Fusion Technical tools? Checkout this article

We create RTF templates for most of the reports and the output can be retrieved in PDF, Excel, HTML, etc.

Most of the end users are well versed with excel and they want the data in excel format to apply filters and manipulate data as per their convenience.

Hence getting data in excel format with formatting correctly is of importance in BI Report.

In one of our previous article, we have looked into how to prevent merging of columns in Excel output using RTF template?

Apart from Merging of columns, the end user will also be interested in if the columns text is clearly visible to him. Most of the columns will get smaller width if we download the excel output directly from RTF template.

There is a setting that we can use to set the auto-width for a column based on the data in the column. With this, we are not setting the fixed width for a column but based on the data dynamically. So this approach will give better experience to the end users to view data in excel.

Navigate to BI Report
Click on Edit
And then click on Properties

BI Report Properties
BI Report Properties

Then go to Formatting tab and scroll down to EXCEL 2007 Output section

Table Auto Layout setting for excel output in bi publisher
Table Auto Layout setting for excel output

We are going to use the Table Auto Layout feature to achieve our requirement.

Table Auto Layout Setting explanation:

Valid Values: Specify a conversion ratio in points and a maximum length in points, for example 6.5,150

Note: For this property to take effect, the property “Keep values in same column” must be set to True which will set the column value into a single cell instead of merging columns.

This property expands the table column width to fit the contents. The column width is expanded based on the character count and conversion ratio up to the maximum specification.

Example: Assume a report with two columns of Excel data — Column 1 contains a text string that is 18 characters and Column 2 is 30 characters long. When the value of this property is set to 6.5,150, the following calculations are performed:

Column 1 is 18 characters:
Apply the calculation: 18 * 6.5pts = 117 pts
The column in the Excel output will be 117 pts wide.

Column 2 is 30 characters:
Apply the calculation: 30 * 6.5 pts = 195 pts
Because 195 pts is greater than the specified maximum of 150, Column 2 will be 150 pts wide in the Excel output. And the data will appear in two lines

Auto Layout setting filled in
Auto Layout setting filled in

Once we set this Table Auto Layout, the columns will get expanded based upon the field lengths dynamically upto the extent listed in the setting.

We can also set the “Minimum column width” and “Minimum row height” in the settings if there is a requirement to increase the widths.

Minimum column width: When the column width is less than the specified minimum and it contains no data, the column is merged with the preceding column. The value must be set in points. The valid range for this property is 0.5 to 20 points. Default value: 3 (in points, 0.04 inch)

Minimum row height: When the row height is less than the specified minimum and it contains no data, the row is removed. The value must be set in points. The valid range for this property is .001 to 5 points. Default Value: 1 (in points, 0.01 inch)

Hope these settings help when you have similar requirements. Even though these are simple settings, they make wonders to the user experience.

Tip: Table Auto Layout setting will not work individually. It works in conjunction with “Keep Values in Same Column” value of True. Use 6.5,150 without worrying about any logic.

Join our fastest growing technology social groups to interact with experts in the Fusion space and get your queries resolved – Telegram group, Facebook Page and LinkedIn Group

The following two tabs change content below.
Sricharan is a Fusion HCM Solution Architect with over 13+ years of overall experience and 5+ years of Fusion experience. He is passionate about technical aspects of Fusion HCM and writes interesting articles on HCM Extracts, HCM Data Loader, Fast Formula, BI Publisher, Integrations and Automation etc.

Leave a Reply