Formatting Grids

This article refers to the latest XLCubed version. For other versions, see Formatting Grids/Versions.

Formatting in XLCubed is handled in 3 key areas:

Measure Formatting: Users can select a measure in the XLCubed member selector, and apply a numeric format to the selected measure using the standard Excel dialog.

Formatting via the right-click menu: Users can right click on any member in the row or column area of a grid, choose XLCubed - Formatting and apply a format to that cell through the standard Excel dialog.

Format sheet: This controls the basic formatting for a grid, in terms of the overall colour scheme and fonts. Any user applied formatting from the above 2 methods is also stored here.

Measure formatting

Formatting can also be achieved through the Member Selector by clicking the highlighted button below:

The standard Excel format window is displayed and allows the user to format the number as required.

This writes a line to the format sheet where any edits can be made if required.

Formatting via XLCubed’s right-click menu

Right-clicking on a row or column header allows you to format the selected member or level. The same can be done on a grid value by selecting the cell and right-clicking, XLCubed > Formatting > Format this Cell.

This technique allows you to quickly format a grid, such as highlighting a particular level:

Any formatting applied in this way will be written to the Format sheet and can also be edited from here using the Excel ribbon or right-click, format cell option.

Format This Member

To format a single member in grid, right click the relevant row/column and select XLCubed > Formatting > Format this Member. This will open the Excel format dialog from which you can apply any settings as desired.

After submitting, formatting will be applied to the member name and its values. If further editing is required, this can be applied on the format sheet (e.g. for just the member name to be formatted and not the values, or to make quick changes).

Format this level

Format this level allows the user to easily format all members at a particular hierarchy level and is available on XLCubed's right-click menu, XLCubed > Formatting > Format this Level. You will be presented with the standard Excel Format window from which you can choose any of the usual settings to change.

You can find more advanced level formatting instructions here.

Format sheet

This shows the default format settings for grids. These can be changed as required (see Default Formats) eg there may be corporate defaults for reports with particular preferred fonts and colours.

Rows 5 to 16 define the basic formatting for a grid. By directly formatting the relevant cells here using native Excel, you can control the font style and colour and cell background of the labels, the grid header and row and column headers and members.

Row 47 onwards is for formatting applied to individual slices of a grid, normally when a user wants to format a particular member. This area will be updated automatically when formatting is applied using XLCubed’s right-click menu.

If you have more than one grid in your workbook and you need to apply different formatting to each grid, you need to name the grid (through Grid Properties) and then refer to each specific grid in this area in column B. Please note: There is an empty row between each slice highlight, so that the borders do not conflict with each other.

Any updates you make directly on the format sheet will be applied the next time the grid is refreshed

To Edit the format sheet you directly apply the required formatting changes to the relevant cells in the FormatSheet using normal Excel formatting.

This is the grid after all the formatting above has been applied:

Cells Q2 to V3 in the format sheet hold XLCubed slicer settings. Here you can change the display format for the slicer title, slicer buttons and slicer items.

Upgrading pre-v9 workbooks

If you start a new workbook, you will automatically get the new format worksheet, workbooks built in older versions can be upgrading to the new formatting, by going to Workbook Options -> Reset Format sheet.

You will be given an option to keep any customisation as part of the upgrade - once the upgrade has completed the old format sheet is renamed and can be safely deleted once you are happy that all the formatting has been saved correctly.

Advanced Options

You can click in cell A16 to toggle the visibility of the advanced options, from here you can set alternate row formatting and other writeback/grid active cell formatting.

For example, to set the alternate row format you need to toggle the selector to "Alternate Rows - On". The formats for the members and cells will be applied for every other row.

Row 20 is for the formatting of the Active cell

Rows 22 to 28 are specific to writeback-enabled grids where you can write new values to the cube.

Formatting Rows and Columns

You can also set formatting on rows and/or columns for grid members.

Create formatting as for levels, so that the format sheet looks something like this:

Change the hierarchy to ROWS as below:

This format will now be applied for whichever hierarchy is on rows.

This could easily be included in the master format sheet so that all grids created would have the same row format. The same applies to columns.

To target a specific hierarchy you can specify the index you want, e.g. Columns:2

Format by Member Property

Format by property allows you to select the affected slice by a member property value. You need the full name of the property, as shown below, and the property must be selected in the grid (although you can set the property display style in Grid Properties to member only so it is not displayed).

(You can get the full name of the property from the grid MDX in the properties screen)

The format sheet is set as below:

The formatting is then applied to the grid based on the Color property.

Multiple Hierarchies

You can nest hierarchies in the format sheet by specifying the Hierarchy names and Member names in the respective columns ("Hierarchy 2", "Member 2", etc.) of the Slice Formatting section.

The example below uses the wildcard (*) to format all members in Product Subcategory under the "Bike" member.

The resulting grid has formatting across the whole row.

Format InCell Charts

From Version 9 you can set a highlight on an InCell Chart created in the member selector. To do this enter the hierarchy name as normal, and in the Member cell prefix the chart title name with "XLCubedInCellChart:", e.g. XLCubedInCellChart:InCell Chart.

Format Grid Calculations

Grid Calculations can be formatted by adding the placeholder XLCubedCalculation in the hierarchy column, and putting the calculation name as the member.

Format special levels

While the LEVEL:Number syntax will highlight members of a level it is also possible to use special identifiers to target specific members.

• LEVEL:LEAF - Highlights members of the hierarchy with no children.
• LEVEL:NOTLEAF - Highlights members of the hierarchy with children.
• LEVEL:SUBTOTAL - Highlights subtotals created by XLCubed.

For these types you specify the Hierarchy as usual and the LEVEL: part goes in the Member cell.

When highlighting subtotals the target hierarchy changes depending on the subtotal mode (Cube or Excel) as this affects the column where the subtotal appears.

Tag Formats

These only apply to Fluence API grids. In Fluence you can tag members and apply formatting to members with the applicable tag. To do this the syntax is.

• TAG:#MyTagName

Custom Formatting Areas for data driven highlights

It can be useful to have your highlights controlled by external data, e.g. using a SQL query to select the highlighted members.

You can do this by adding extra custom highlight areas in the Appearance tab of the Properties screen. These use the same rules as the format sheet and should have Dimension/Member pairs of cells and then a data cell.

In custom areas you do not have a grid name or spacer cells between each cell.

Workbook Formats

A selection of pre-made workbook level formats are available from the XLCubed ribbon. This will create a new basic grid format on the format sheet which can then be further edited as needed (selecting a new workbook format does not overwrite any custom slice highlighting already defined).

Conditional Formatting

Please see Conditional formatting.

Default Formats

If you want to change the default formatting for all new workbooks, you need to update the format sheet file. Its location can be seen in XLCubed Options. You can update the formatting as usual on the “StyleDefault” tab, then save and distribute this format file as necessary.

See Also

• Format Sheet for more formatting options