Export to Excel

Clarity reports can be exported as Microsoft Excel files (XLS). Three different output types are available for Excel export:

XLSReport, which presents the report in a pictorial format, with the emphasis on preserving the appearance/layout of the report. To export to this format, select Export to Excel > Export to XLS Report on the Clarity Report Parameters window.
XLSData, which presents the report in a strictly columnar format, with the emphasis on ensuring the data is formatted to allow further data manipulation. This format preserves the formatting of the original of the original report, including column widths, colours, text formatting and any data formatting like the number of decimal places or percentage signs. To export to this format, select Export to Excel > Export Data to Excel 97 – 2003 (XLS) on the Clarity Report Parameters window.
XLSXData, which functions in the same way as the XLSData format, but produces an .XLSX file, rather than an .XLS file. To export to this format, select Export to Excel > Export Data to Excel 2007 and later (XLSX) on the Clarity Report Parameters window.

Setting up Excel Output

You can set either of the XLS formats as the default by setting the report's DefaultFileDeviceType property to "XLSReport" or "XLSData". This property is available under Generation on the Design tab.

Additional settings that relate to XLS output are available in the XLSSettings section under Output - File.

XLSReport Settings

The XLSReport format uses the setup on the Design tab. When printed, the XLS output replicates the original report, including merged cells.

The file type to export to is specified by the DeviceType property under Generation.

The default filename and location for the exported file are specified by the TextFileName property under Output - File.

XLSData Settings

The XLSData and XLSXData formats are set up using the Print to File Setup window. This window is opened by selecting Print to File Setup from the File menu when on the Design tab.

To specify which columns should appear in the Excel file when the report is exported in the XLSData format:

Select a region of the report from the Bands section. All report elements that appear in that region appear below.
Select the columns to appear in the XLSData export and use the left and right arrow buttons to move them to the list on the right.
Repeat for each region that has columns you want to include in the XLSData export.

You can use the Auto Layout button to automatically populate the Selected Controls pane. The system analyses the report, then selects and orders columns for optimal output. The report data columns are sorted to the top of the pane (meaning they will appear on the left of the Excel file) and the titles, labels, etc. are sorted to the end. You can then delete any unnecessary columns or re-order them as necessary.

Note: If a report is exported in the XLSData format, and the Print to File Setup has not been configured, the system will perform this analysis at the time the export is performed.

The and buttons let you add and remove blank columns in the Excel output. This can be useful for rows that contain section totals - you can include only those columns that you want to be totalled, and tell the output to skip the other columns by adding blank columns in their place.

For reports that export to the XLSData format, the SaveLength property on the Design tab under Output - File can be used to specify the column width in characters. If this property is left at the default value of zero, the column will autosize based on the maximum content length. If the property is set to a value that is shorter that the maximum content length, the content will appear truncated in Excel (the content is not actually truncated – it can be viewed by resizing the column in Excel). The minimum (non-zero) value for the property is 8 characters.

GL Reports

When exporting a GL Report to Excel (i.e. when the Deliver to Excel option is ticked on the Run GL Report window), if the selected Clarity report’s DefaultFileDeviceType property is set to “XLSData”, the report will be exported to XLSData; otherwise it will be exported in the XLSReport format.