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 "subfunction 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 resolving
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. 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 September 27, 2022