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
Then go to Formatting tab and scroll down to EXCEL 2007 Output section
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
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.
Latest posts by Sricharan Monigari (see all)
- Renaming HCM Extracts is a possibility from 20D - November 24, 2020
- Sangam20 – Oracle Users Group Conference - November 23, 2020
- Passing Parameter values from Flow Instance to HDL Transformation Formula - November 23, 2020
- Solve manual FTP server update issue for HCM Extracts post every P2T refresh - November 16, 2020