Cognos Report studio is a powerful tool for designing reports with many options to format the report layout. But preserving the formatting when we export to excel is a real challenge.
Most of the time we would have came across issue in excel such as columns getting merged or columns getting stretched or shrunk. Hence some of us might have created a separate copy of the report page for excel output and applying excel specific formatting and dynamically rendering it. But having two copies of the report page would be costlier on maintenance.
Below are few techniques to format the report in an effective way for Excel output:
- Excel columns with inconsistent width:
We could specify fixed column width using ‘Size & Overflow’ property to have a uniform look, but the moment we export to excel, the fixed width applied in the report does not reflect in excel. Columns may stretch or shrink inconsistently.
To avoid this behavior, do not use ‘Size and Overflow’ on column header or body, but use it on the data item inside the column body by unlocking the report.
Before:
After:
- Excel columns getting merged:
When we use ‘Size and Overflow’ to apply fixed width on the columns it could result in having merged columns in excel output, merged columns could be annoying for data manipulation or external access.
We need set the following flag value as ‘True’ in Cognos Advanced setting to avoid this behavior:
RSVP.EXCEL.XLS2007_COLUMN_WIDTH_CONTROL
Before:
After:
- Excel column not wrapping:
In some cases the column name could be lengthy and wrapping is necessary, but excel exported from Cognos doesn’t allow to wrap column headers, use the below technique to wrap the column name:
Unlock the report objects and insert table with 1 column and 2 rows in the column header, now break the caption into two parts and place them in the two rows:
Before:
After:
