Skip to main content
Pentaho Documentation

PDF and Excel export customizations

Analyzer allows you to customize the appearance and content of your PDF and Excel outputs.

The complete list of properties that you can change are in the file comments. You can find the file here at server/pentaho-server/pentaho-solutions/system/analyzer/analyzer.properties.

You can test your customizations to analyzer.properties in real time by refreshing the cache for the Analyzer report you are modifying using the More actions and options Administration Clear Cache menu action.

PDF and Excel settings

You can customize the appearance of your company's PDF and Excel exports from Analyzer in a number of ways. For example, Analyzer PDF exports can be personalized with a company logo and a cover page, and you can change the default font used to generate PDFs.

Customizing the PDF cover page

You can customize the PDF cover page with the following analyzer.properties.

PropertyDescription
renderer.pdf.cover.hideGenerates the PDF export without a cover page. (Default value: false)
renderer.pdf.cover.imageGenerates the PDF export with a custom cover image.

The gray boxes in the title background of the report’s cover page can be hidden by setting renderer.pdf.cover.hideTitleBackground=true. When the background is hidden, the title font color is black instead of white.

The cover image is anchored in the bottom left corner of the page. The image is not scaled based on the export page size. Specify the file name of the image and type, no path, and place the image file in the /pentaho-solutions/system/analyzer/resources directory.

If you want to design a cover page with a specific page size and image orientation, then you can override the default renderer.pdf.cover.image. For each page size and orientation (see below), the provided image width and height completely fill the cover page. When setting the property to use for the size and orientation, you should replace the “width x height” value in the property with the file name and type of the image, for example renderer.pdf.cover.image.letter.landscape= my_favorite_image.jpg.

  • renderer.pdf.cover.image.letter.landscape=792x612
  • renderer.pdf.cover.image.letter.portrait=612x792
  • renderer.pdf.cover.image.legal.landscape=1008x612
  • renderer.pdf.cover.image.legal.portrait=612x1008
  • renderer.pdf.cover.image.note.landscape=720x540
  • renderer.pdf.cover.image.note.portrait=540x720
  • renderer.pdf.cover.image.tabloid.landscape=1224x792
  • renderer.pdf.cover.image.tabloid.portrait=792x1224
  • renderer.pdf.cover.image.ledger.landscape=792x1224
  • renderer.pdf.cover.image.ledger.portrait=1224x792
  • renderer.pdf.cover.image.postcard.landscape=416x283
  • renderer.pdf.cover.image.postcard.portrait=283x416
  • renderer.pdf.cover.image.executive.landscape=756x283
  • renderer.pdf.cover.image.executive.portrait=522x756
  • renderer.pdf.cover.image.a3.landscape=1191x842
  • renderer.pdf.cover.image.a3.portrait=842x1191
  • renderer.pdf.cover.image.a4.landscape=842x595
  • renderer.pdf.cover.image.a4.portrait=595x842
  • renderer.pdf.cover.image.a5.landscape=595x420
  • renderer.pdf.cover.image.a5.portrait=420x595
  • renderer.pdf.cover.image.b4.landscape=1000x708
  • renderer.pdf.cover.image.b4.portrait=708x1000
  • renderer.pdf.cover.image.b5.landscape=708x498
  • renderer.pdf.cover.image.b5.portrait=498x708

Customizing the PDF logo

You can customize the PDF logo with the following analyzer.properties.

PropertyDescription
renderer.logo.imageAdds a custom logo image to a PDF export. The logo image is placed on the bottom left corner of every page. This image should have no padding and a white background. If the PDF includes a cover page, the logo is placed bottom center on that page. You need to specify the image file name and type, no path, and place the image file in the /pentaho-solutions/system/analyzer/resources/ directory.

If you want to set a logo image specific to a page size, then you can override the default renderer.logo.image property with the desired size value:

  • renderer.logo.image.letter
  • renderer.logo.image.legal
  • renderer.logo.image.note
  • renderer.logo.image.tabloid
  • renderer.logo.image.ledger
  • renderer.logo.image.postcard
  • renderer.logo.image.executive
  • renderer.logo.image.a3
  • renderer.logo.image.a4
  • renderer.logo.image.a5
  • renderer.logo.image.b4
  • renderer.logo.image.b5
renderer.logo.image.hideOnCoverHides the logo on the cover page when set to true. Useful when the cover page already includes a custom background image. (Default value: false)

Set default fonts for PDF export

When displaying data in Analyzer, your reports will use the default browser fonts. However, the PDF export function may not have the same fonts available to it when creating a PDF from your Analyzer report, resulting in output that doesn't look the same way in PDF format as it does in the browser. The default font for PDFs is Helvetica, but you can specify any TrueType font or collection to replace it. Follow the instructions below to specify a different font for PDF exports.
NoteIf you have localized your schema in a language that uses a multi-byte character set (most Asian languages fit into this category), this process is required to make PDF output appear without errors.

Procedure

  1. Stop the Pentaho User Console and Pentaho Server.

  2. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/analyzer/ directory and open the analyzer.properties file with any text editor.

  3. Uncomment the renderer.pdf.font.path line.

    renderer.pdf.font.path=C:/WINDOWS/Fonts/MSGOTHIC.TTC,1
  4. Replace the value of this line with the TrueType font or collection that you want to use as the default. If you are specifying a collection, you must put a ,1 after the font name, as shown in the above example. This does not apply to individual fonts (TTF files).

    renderer.pdf.font.path=/usr/share/fonts/truetype/freefont/FreeSans.ttf
  5. Save and close the file, and restart the Pentaho Server.

Results

Your PDF exports from Analyzer should have the font you specified.

Removing report attributes

PDF and Excel exports from Analyzer, by default, include information such as the report creator, report name, folder location, filters used, and fields used. As shown in the table below, you can exclude some or all of this information from an export by setting the value of its property to true in analyzer.properties.

PropertyDescription
renderer.metadata.hide.aboutThisReport Removes the “About this report” section. (Default value = false)
renderer.metadata.hide.filterSummaryRemoves the “Filter summary” section. (Default value = false)
renderer.metadata.hide.fieldsUsedRemoves the “Fields used” section. (Default value = false)

You can retain the "About This Report" section and customize the items it contains by setting the value for the associated property, as described in the following table.

PropertyDescription
renderer.metadata.hide.aboutThisReport.name Removes the “report name” field. (Default value = false)
renderer.metadata.hide.aboutThisReport.descriptionRemoves the “report description” field. (Default value = false)
renderer.metadata.hide.aboutThisReport.creatorRemoves the “report creator” field. (Default value = false)
renderer.metadata.hide.aboutThisReport.locationRemoves the “report location” field. (Default value = false)
renderer.metadata.hide.aboutThisReport.createdOnRemoves the “creation date” field. (Default value = false)
renderer.metadata.hide.aboutThisReport.cubeRemoves the “cube annotation” field. (Default value = false)

Removing a data table and report worksheet

PDF and Excel exports of an Analyzer chart report, by default, also include a data table and report worksheet. You can exclude the data table and / or the report worksheet from PDF and Excel exports of a chart report by setting the value of the associated property to true, as described in the following table.

PropertyDescription
renderer.pdf.dataTable.hideExcludes the data table from a chart report exported to PDF. (Default value = false)
renderer.excel.dataTable.hideExcludes the data table and report worksheet from a chart report exported to Excel. (Default value = false)

Creating custom headers and footers

Headers and footers can be customized to include static text such as disclaimers and copyright information, or dynamic text such as the current user and date. These header and footer templates, defined in Analyzer's resource message files, can be localized into different languages. Analyzer's resource message files are in the pentaho-solutions/system/analyzer/resources directory. The base resources file is messages.properties. The table below describes each message file.

NoteFor testing custom headers and footers, you can set localizationService.cache.resource.bundle=false in the analyzer.properties file for immediate pickup by Analyzer of changes to the message*.properties files. For performance reasons, this setting should only be performed in development environments.

Message fileDescription
RendererPDFHeaderTemplateHeader template that appears in top-left corner of a PDF. (Default value = %report)
RendererPDFFooterTemplateFooter template that appears in bottom-right corner of a PDF. (Default value = Page %pageCurrent of %pageTotal)
RendererExcelHeaderTemplateHeader template that appears on the first line of each Excel worksheet.
RendererExcelFooterTemplateFooter template that appears after the content on each Excel worksheet.

When a PDF and Excel export is generated, the tokens described in the table below are replaced in the template text.

TokenDescription
%reportCurrent report name.
%userUsername of session exporting the report.
%dateDate and time when report was exported.
%pageCurrentCurrent page number (PDF only).
%pageTotalTotal page number (PDF only).

Theming a pivot table

If you modify the system theme or add a new theme that changes the styles on the HTML pivot table, the PDF and Excel pivot tables can inherit the same background and font colors. To enable this feature, renderer.dataTable.inheritTheme must be set to true in analyzer.properties.

The following pivot table items can be themed in PDF and Excel exports.

Pivot table items

When generating the PDF or Excel export, the following CSS rule sets are used by the theme-specific CSS file to customize the pivot table.

NumberPivot table itemCSS selectorCSS declarations AExample
1Level headers

.pivotTable

.columnHeaders

.columnheading

background-color

color

Education Level

Name

Product Family

2Level member property headers

.pivotTable

.columnHeaders

.columnPropHeading

background-color

color

Yearly Income
3Measure headers

.pivotTable

.columnHeaders

.metric

background-color

color

Unit Sales
4Level members

.pivotTable

.columnHeaders

.member

background-color

Graduate Degree

Food

1997

5Total members and cells

.pivotTable

.cells

.colSubTotal

background-color

Cells under Graduate Degree Total and Food Total
6Grand total cells

.pivotTable

.cells

.colGrandTotal

background-color

Cells under Grand Total

A CSS declarations for background-color and color must be specified using hex notation, such as #FF0000 for the color red.

The example below shows a pivot table that was themed by modifying the ruby theme in the pentaho-solutions/system/analyzer/styles/themes/ruby/anaRuby.css file.

Pivot table Ruby theme

If you are customizing the HTML pivot table, use the following reference which lists the corresponding CSS selectors for each pivot table item. Many of these items also support :hover selection style changes. The CSS rule sets used to style the PDF and Excel pivot tables are a subset of the CSS selectors listed below.

NoteWhen styling the HTML pivot table, static resources such as CSS files are cached in the Pentaho Server. You can temporarily disable this caching by setting <cache> to false in the pentaho-solutions\system\analyzer\settings.xml file.

NumberPivot table itemCSS selectors
1Level headers

.pivotTable

.columnHeaders

.columnheading

.pivotTable

.rowLabelHeaders TD

2Level member property headers

.pivotTable

.columnHeaders

.columnPropHeading

.pivotTable

.rowLabelHeaders

.rowPropHeader

3Measure headers

.pivotTable

.columnHeaders

.metric

4Level members

.pivotTable

.columnHeaders

.member

.pivotTable

.rowHeaders

.inner

.pivotTable

.rowHeaders

.outer

5Total members and cells

.pivotTable

.columnHeaders

.memberSubTotal

.pivotTable

.rowHeaders

.subTotal

.pivotTable

.cells

.colSubTotal

.pivotTable

.cells

.rowSubTotal

6Grand total cells

.pivotTable

.cells

.colGrandTotal

Set pivot table minimum font size

When rendering the pivot table for PDF export, Analyzer attempts to fit the complete width of the table onto a single page using a size 9 font. If the table cannot fit the page using the size 9 font, then progressively smaller font sizes fonts are automatically attempted, down to a lower default limit of 3. To set a higher minimum font size and avoid the need to use PDF zoom functions to read the table, you can override this property.

Procedure

  1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/analyzer/ directory and open the analyzer.properties file with any text editor.

  2. Locate renderer.pdf.font.table.min.

    renderer.pdf.font.table.minSize=3
  3. Replace the value with the smallest font size that you want used for exported pivot tables.

  4. Save and close the file.

  5. Test your changes by refreshing the cache or by restarting the Pentaho Server.

Results

Your pivot tables exported to PDF from Analyzer will use text with the minimum font size.

Customize the logo on Excel exports

You can add a custom logo to your Excel exports from Analyzer. The image is placed after the content on each worksheet.

To add a custom logo, follow the steps below.

Procedure

  1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/analyzer/ directory and open the analyzer.properties file with any text editor.

  2. Locate renderer.excel.logo.image

    renderer.excel.logo.image=
  3. Specify the image file name and type, with no path.

    For example, my_favorite_logo.jpg
  4. Place the image file in the /pentaho-solutions/system/analyzer/resources/ directory.

  5. Save and close the file.

  6. Test your changes by refreshing the cache or by restarting the Pentaho Server.

Results

Your Excel exports from Analyzer will now contain the logo.

Customize the font on Excel exports

You can set the font used for your Excel exports from Analyzer.

To change the font, follow the steps below.

NoteThe font used with Excel reports must be accessible by the Java virtual machine or operating system.

Procedure

  1. Navigate to the pentaho/server/pentaho-server/pentaho-solutions/system/analyzer/ directory and open the analyzer.properties file with any text editor.

  2. Locate renderer.excel.font.name.

    renderer.excel.font.name=Verdana

  3. Specify the name of the font.

    (Default = Verdana

  4. Save and close the file.

  5. Test your changes by refreshing the cache or by restarting the Pentaho Server.

Results

Your Excel exports from Analyzer will now use the font.