The settings you made in the Format Element dialog can be used in a Jedox View that uses the elements on which they are applied. It is also possible to use these styles and formats in Jedox Excel add-in, and they are also partially supported in the Ad-hoc reports in Jedox mobile.
By default, when you paste a View the styles are not applied . To enable styles in your View, go to the Options tab of the Paste View dialog and select the options you want to apply.
You can define which type of property (number format, style, size, or rights) to retrieve from the dimensions used on headers, columns and/or rows. For example, you can decide to retrieve number formats from the dimension(s) used on columns, and styles (such as font color, or background color) from the dimension(s) used on rows.
Properties are merged whenever possible. If there is a conflict of multiple dimensions defining an identical property, the order of precedence is as follows:
- Row dimension
- Column dimensions
- Header dimensions
- View theme
Using Element-based Styles in Custom Reports
You can retrieve and use the cell properties of your previously defined styles in your custom reports (reports which are not using Jedox Views) via Cell properties. Cell Properties allow you to associate certain styles, formats, sizes, and rights with elements in the OLAP databases. Cell Properties facilitate the creation of customized, spreadsheet-based reports and increase the capabilities of Jedox Views by allowing more View formatting. The Cell Properties centralize some of the requirements for report formatting, therefore, you would not need to format each report separately. To open the Cell Properties, go to Query > Cell Properties.
You can enable the OLAP cell properties for custom formulas, Views, or both. You can also enable the Cell properties for just the current Worksheet, or for the entire Workbook via the Workbook option. Choosing to use the Cell properties for the entire workbook formats all worksheets in that workbook.
Four types of cell properties can be defined and applied in spreadsheets:
-
Number formats can be applied to cells, and they adhere to Excel number format notations.
-
Style can be defined for Header, Columns, Rows, and Cells. You can define colors, borders, font information, etc.
-
Column / row sizes can be defined for Header, Columns, Rows, and Cells. You can define the height/width of rows/columns.
-
Rights can only be defined for cells.
These properties can be defined per element in dimensions. An exception is the rights property, which is dynamically calculated based on user access rights on specific OLAP cells.
PALO.ESELECT() and PALO.DATA/DATAC() formulas
In custom reports, styles can only be calculated for and applied on cells containing either PALO.ESELECT() formulas, or PALO.DATA/DATAC() formulas. Styles on element names in a DynaRange cannot be retrieved and applied.
PALO.ESELECT()
Unlike in views, in custom reports, all cells are context-independent. They are not linked to a view (table) row / column / header. For this reason, to allow the styling and formatting of PALO.ESELECT cells, you need to specify a context in the "sub-function argument".
The subfunction arguments you can set are:
-
0=Default value
-
2=Header
-
4=Row dimension
-
6=Column dimension
PALO.DATA*()
For PALO.DATA*() cells, you have to enable the retrieval of number formats, Column/row sizes, and style, on the Cells level in custom reports. This can be done by choosing the different options of the Cell properties menu. It is also possible to enable it for the entire workbook or specific worksheet(s).
Conflict Resolution
When changing the "Cell properties" on a View, if a specific style (such as font family), or a specific number format is defined for multiple sections, a conflict might occur. In this case, the order of precedence is:
- Rows
- Columns
- Headers
Normally, when styles are from multiple dimensions (e.g., a background color from one dimension and a font color from another dimension), they will not have any conflicts and will be merged like in Views. For example, when a font color is from an element on the rows, and a background color is from an element on columns, the styles will be merged without any conflicts.
However, when there is a conflict, for example, when styles and formats are defined in multiple dimensions of a given cube, then styles and formats on the last dimension in the cube's layout have precedence over styles and formats from previous dimensions for resolving the conflict.
When spreadsheets contain various styles and formats, defined through several methods (style classes, cell formatting, and conditional formats), then styles and formats that are defined through cell properties are merged with the existing styles and formats if possible. If there is a conflict, properties that are retrieved from the OLAP database take precedence over style classes and cell formats. However, styles, number formats, and lock status defined through conditional formatting will take precedence over OLAP cell properties.
Updated March 27, 2024