You can define specific styles, number formats, sizes, and rights for elements in a dimension so that they appear with the designated formatting in Jedox Views and custom reports. These styles and formats are defined in the Modeler and stored as attributes in the In-Memory DB. They can be applied in both Jedox Web and Excel Add-in.
There are four types of properties that can be defined and applied in spreadsheets:
- Styles: colors, borders, fonts, etc. (in CSS notation)
- Number formats: date, time, percentage, etc. (in Excel format notation)
- Size: height/width of rows and/or columns (in pixels)
These properties can be defined for each element in a dimension and can be defined in multiple dimensions.
*Note: rights properties are dynamically calculated based on user access rights on specific OLAP cells.
Defining Styles and Formats
Styles and formats are defined in the Modeler.
- In Modeler, select a dimension.
- Select the element on which you want to define a style.
- In the overview section of the element, click Set Format/Style to open the Format Element dialog.
- The last button alternates between Show Format/Style and Hide Format/Style.
The Format Element dialog allows you to set element attributes for style, format, row height and column width.
Style / Format
The Style / Format section allows you to define the style and format of the elements in a dimension. You can define a default format for an element in a view, and you can also specify formats and styles if the element is the first, last, odd, or even element in a view.
You can select a predefined style by clicking and selecting it from the drop down box.
You can also create new cell styles:
- In the drop down box, click New Cell Style. The Style dialog is displayed.
- Select the elements you want to include in your style definition.
- You can format your style further by clicking Format…This opens the Format Cells dialog.
- Click Ok. Your style is now available in the style list.
- You can access the Format Cells window for your already defined style.
- Click Ok. The style is now applied to the element.
The Format Element window allows you to see a preview of your dynamic style.
You can return to the initial settings by clicking Reset.
Applying styles and formats in Views
The settings you made in the Format Element dialog are visible in a Jedox View that uses the elements on which they are applied.
Note: By default, no style is applied when you paste a view. 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
You can set the size of the rows and columns containing the default, first and last values. By default, no size is set.
To see the dynamic style attributes for each dimension element, select a dimension in Modeler and click on the Element Overview page. The style settings are shown in columns, for each element.
Using of styles and formats in custom reports
You can also retrieve cell properties in custom reports (reports which are not using Jedox Views). To enable these properties, go to Query > Cell Properties.
The cell properties dialog opens. Check the boxes for the properties you want to retrieve, and apply. 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.
Note: 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, you have to enable retrieval of number formats, size, and style, on the Cells level in custom reports. If styles and formats are defined in multiple dimensions used in a given cube, they will be resolved in the order of the dimensions in the cube, and applied in reverse order. This means that styles and formats on the last dimension in the cube’s layout, for example, have precedence over styles and formats from previous dimensions. Non-conflicting styles from multiple dimensions (e.g. a background color from one dimension, and a font color from another dimension) will be merged, like they are in Views.
Removing styles and formats from a dimension element
You can remove any style and format from a dimension element by going to the Dimension Properties tab. Expand the Advanced section and click the button. A confirmation dialog dis displayed. Click Yes. The styles and formats are now removed from the dimension.